How to synchronize between sql client and sqlserver
-
In the client , a sql file is parsed into pieces of sql command delimitted by "GO". Each command is executed with sqlCommand object. Some of the commands have dependency. So if the previous command has not been executed throughly,the next command wlll fail. I think the sql function call is synchronized to some extent. Because my code works fine on most machine in most time . But sometimes it will fail.
For example, I try to execute "sp_attach_single_file_db" before alter table in this database. Most time it works fine and sometimes there is exception that "database is offline" and can not be opened.
Any help is appreciated!
-
I learned the little I know about this from looking at the scripts created by Redgate SQL Compare. I think this is one of the quicker ways to learn - the scripts are not perfect but they are better than I could write myself, the concepts are portable and you can edit anything you like before running.
It's a good tool, will help you rollback or exit if things fail, and I think they do a free trial. I wouldn't be without it now, and it was not expensive.
HTH
Bill.
-
The only way I know to do this is to create a temporay table (#temp) at the start of the batch, and after every DML/DDL statment check the @@Error system variable. If > 0, delete the #temp table. Then after each GO, check if #temp still exists. (if Object_id('tempdb..#temp') > 0) If it doesn't, return.
here's a little test script
--create table #temp(id int)
--drop table #temp
if Object_id('tempdb..#temp') > 0
print 'there'
else
print 'not there'
HTH
Dave J
-
Thanks a lot !
I am still confused that why sometimes after executing sp_attath_single_file_db,the database status could be "Offline".In my understanding,the database should be "Online"
-
Is it still firing up? Try adding a small waitfor delay '00:00:30' after
-
The problem occurs at times. Last time when it occured,I checked the log message and found after waiting for 10 minutes, the database is still offline .But I can manually set it online via EM in no time.
-
what command is EM using? Capture the command in Profiler.
Then put that in your script if it errors?
-
-
Hi Noel, many thanks for your help!
Yes. I have made the change to alter database online after attaching it.
And until now I have not reproduced this problem. Since this issue was not always reproducable before ,it's hard to say this is the final resolution. I hope it is !
This issue is so strange and inconsistent ,I really want to figure out the root cause. But I get lost.
-
I am pretty sure with that is not going to happen again but just for curiosity how were you doing the detaching of it ?
* Noel -
Three steps.
1.Set database offline (Clear connections)
2.Detach database
3.Delete mdf and ldf files.
I used to use drop command .Later on I found it can not fit all situations.For example, if database doesn't exist in system tables while database file exist in dest directory(for example,after uninstalling the sqlserver)
-
some how the OFFLINE status is definetly being kept in master I can't figure out a possible scenario but you should look at the steps you are taking very carefully. I know this does not helps but just to give you some feed back
* Noel
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply