How to synchronize between sql client and sqlserver

Question

How to synchronize between sql client and sqlserver

Viewing 11 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Bill Geake Ten Centuries Points: 1352 More actions · Answer 1

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.

David Jackson SSCertifiable Points: 6414 More actions · Answer 2

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

http://glossopian.co.uk/
"I don't know what I don't know."

Jennifer-242405 SSC Veteran Points: 284 More actions · Answer 3

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"

David Jackson SSCertifiable Points: 6414 More actions · Answer 4

Is it still firing up? Try adding a small waitfor delay '00:00:30' after

http://glossopian.co.uk/
"I don't know what I don't know."

Jennifer-242405 SSC Veteran Points: 284 More actions · Answer 5

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.

David Jackson SSCertifiable Points: 6414 More actions · Answer 6

what command is EM using? Capture the command in Profiler.

Then put that in your script if it errors?

http://glossopian.co.uk/
"I don't know what I don't know."

noeld SSC Guru Points: 96590 More actions · Answer 7

Did you performed what I adviced you to do here?

* Noel

Jennifer-242405 SSC Veteran Points: 284 More actions · Answer 8

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.

noeld SSC Guru Points: 96590 More actions · Answer 9

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

Jennifer-242405 SSC Veteran Points: 284 More actions · Answer 10

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)

noeld SSC Guru Points: 96590 More actions · Answer 11

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