|
I haven't done it in your kind of environment, but the following approach has worked for me elsewhere:
connect_to_server();
if (error_db_not_exist == open_database(my_db))
{
read_from_file(canned_sql);
run_query(canned_sql);
if (success != open_database(my_db))
}
where canned_sql is the appropriate set of creates, etc. Hopefully you can get this by querying system tables on your test server. An alternative is to export your test db, then delete all the *data* from that, leaving just the "structure".
HTH
Peter
Software rusts. Simon Stephenson, ca 1994.
|
|
|
|
|
It sounds to me that it's something similar to this[^] you need.
It creates a script for recreating a database on another server.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
That's worth a look, Jorgen - Thanks!
It just seems silly to me that I can't export my database to a XML file, then regenerate the database on the target server using that file. I've used db-based product before that create the database on installation, but I assumed there was a CREATE DATABASE function, in addition to the CREATE TABLE function used regularly. It never occurred to me that they were scripting a copy and import process in the installer.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I agree with Mycroft. I detach the database, copy the files, and reattach.
|
|
|
|
|
It sounds like scripting is the best way as you do not want everything to go. However I do not know if Express has scripting.
Another way would be to 1) backup the database 2) truncate tables 3) backup to second file then restore the second backup to new server.
|
|
|
|
|
Actually, I found this[^] to be intriguing. I wonder if I can code this into a SqlCommand? 
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I found the solution at Microsoft Support, not MSDN.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
I am using this code on SQL Server Expess as stored procedure. The icd_disease has 12,000 records but with LEFT(icd_code, 1) = 'R' it’s just 400 records. Then why it takes more than 40seconds to bind into grid control although I ave already indexed the icd_code and icd_description field
SELECT icd_diseases.icd_id, icd_diseases.icd_code, icd_diseases.icd_description
FROM icd_diseases
WHERE LEFT(icd_code, 1) = 'R'
ORDER BY icd_description, icd_code
This is my binding code
data_table = new DataTable();
sql_connection = new SqlConnection((string)public_var._system_parameters_hash["SQL_SERVER_CONNECTION"]);
sql_connection.Open();
sql_command = new SqlCommand("sp_get_all_symptoms", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_adapter = new SqlDataAdapter(sql_command);
sql_adapter.Fill(data_table);
dataSymptoms.DataSource = null;
dataSymptoms.ResetBindings();
dataSymptoms.DataSource = data_table;
gridSymptoms.Columns["icd_id"].Caption = "icd_id#";
gridSymptoms.Columns["icd_id"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_id"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_id"].Visible = false;
gridSymptoms.Columns["icd_code"].Caption = "icd_code#";
gridSymptoms.Columns["icd_code"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_code"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Center;
gridSymptoms.Columns["icd_code"].Visible = false;
gridSymptoms.Columns["icd_description"].Caption = "Symptom";
gridSymptoms.Columns["icd_description"].AppearanceHeader.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Default;
gridSymptoms.Columns["icd_description"].AppearanceCell.TextOptions.HAlignment = DevExpress.Utils.HorzAlignment.Default;
gridSymptoms.Columns["icd_description"].Width = 420;
|
|
|
|
|
Figure out what takes more time, the query or the code. If it is the query, take a look at execution plan. Might help.
|
|
|
|
|
WHERE LEFT(icd_code, 1) = 'R'
Doesn't look very efficient. I'd add an extra (redundant) column that holds the first character that you're filtering on. And put an index on that field 
I are Troll 
|
|
|
|
|
I am sure there is something wrong because running a query to get all the 12000 on the same PC but using SSMS takes 1sec only! The issue is when I run it on winform either to get all or to get starting with R so no creating another col holding the first character won't help when I want to get all
|
|
|
|
|
jrahma wrote: when I want to get all
sp_get_all_symptoms
jrahma wrote: I am sure there is something wrong because running a query to get all the 12000 on the same PC but using SSMS takes 1sec only!
I don't think that SSMS is using a datatable to cache the entire table locally. Might be using a reader, filling your grids with results as they arrive over TCP.
If all the ICD-codes need to be on the client, why not put them on the clientmachine as a SqlCe database?
I are Troll
|
|
|
|
|
Why not just do this?
WHERE icd_code LIKE 'R%'
Isn't that the same thing as LEFT(icd_code, 1)? I don't know whether the query optimiser is smart enough to reduce the two queries down to the same thing or not. Using LIKE would definitely result in the optimiser considering using an index on icd_code if there is one. I don't know whether the query optimiser considers using an index for LEFT or not.
|
|
|
|
|
David Skelly wrote: Why not just do this?
Would be an option too, even cleaner to read. Another (a bit more obfuscating perhaps) alternative would be a computed column, including an index;
BEGIN TRANSACTION
CREATE TABLE Test
(
ICDKEY VARCHAR(8)
,ICDVAL VARCHAR(5000)
,KEYPFX AS LEFT(ICDKEY, 1)
)
CREATE INDEX Test_KeyPfx_Idx ON Test (KEYPFX)
ROLLBACK
Then again, if I have the option of doing the processing ahead, I'd surely choose to add a readonly-column and do a single update-statement.
Mean part of that query is that it's dragging along a large text-field.
I are Troll
|
|
|
|
|
0) Start with using LIKE (as mentioned)
1) Don't prefix your stored procedure names with SP_.
2) Don't use a DataAdapter, use a DataReader
3) Be sure to close your Connection
|
|
|
|
|
Hello Friends,
I've a dynamic sql and it's returning Max(TRN_AMOUNT) now i've to store this Max(TRN_AMOUNT) in a variable i.e. @NUM.
Here is my code
DECLARE @STRSQL VARCHAR(MAX),@NUM VARCHAR(MAX)
SET @STRSQL='SELECT MAX(TRN_AMOUNT) FROM TRN'
EXEC(@STRSQL)
PRINT @NUM
|
|
|
|
|
You can use following:
SET @STRSQL='SELECT @someVariable = MAX(TRN_AMOUNT) FROM TRN
You can declare @someVariable in your SP. BTW there is no need to dynamic query in the scenario you have mentioned.
|
|
|
|
|
Hi!
I've to select the most recently added field in a column. i.e the last value in a column. How to select it?
|
|
|
|
|
Do you mean to get the last row of data added to the table? Your question is not clear.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I'll describe it.
I'm inserting TeamIds to the table from one class(TeamIds are selected ids of a list in a Menu page). I've to display a color of the selected team(TeamID)over the remaining pages. On Clicking a button, I've stored the selected index of the list control(TeamID) to the Team table. From other pages(other classes),
I've to query the recently added TeamID(last one in the table). How to do this?
|
|
|
|
|
Quick thought: why not add the id to the session and use that? Save a trip back to the db to fetch what you already have.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
You could use a trigger to include a timestamp on each row that is inserted or updated. Then your select would be on the most recently timestamped row. However this may not work if your inserts and updates can occur faster than the smallest time span for your timestamp.
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
If you're using an autonumber field as a primary key, select on the MAX value of that field. Alternatively, get the number of rows in the main table and fetch the (N-1)th row.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hi all.
I would like to make from three only two rows.
I hope someone can helps me to put values which are not "null" to a new row.
This is the table which exisits:
Table "MACandDNS":
NAME | ETH0 | ETH1 |
hugo | 000011223333 | 000011223334 |
sassy | 000013323333 | 000211223334 |
jan | 985555111113 | <empty> |
kevin |
|
|
|
|
|
Maybe something like?
INSERT INTO MACandDNSnew
SELECT NAME, ETH0 AS ETH
FROM firsttable
WHERE ETH0 IS NOT NULL
UNION
SELECT NAME, ETH1 AS ETH
FROM firsttable
WHERE ETH2 IS NOT NULL
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
