May 27, 2008 at 11:10 am
Hi all,
in order to speed up the process of detaching a db (SQL2000), zipping it and attaching it again, I wanted to develop a batch-file doing all the work.
Here is what I came up with:
COPYNDFI-810.CMD
[font="Courier New"] isql -S d810mb\sql2000 -U sa -P ___ -i c:\batch\copy_ndfi.sql[/font]
COPY_NDFI.SQL
[font="Courier New"]use master
go
sp_detach_db 'konfig'
sp_detach_db 'ndfi'[/font]
Executing the CMD gives this result:
[font="Courier New"]1> 2> 1> 2> 3> Msg 170, Level 15, State 1, Server D810MB\SQL2000, Line 2
Line 2: Incorrect syntax near 'sp_detach_db'.[/font]
Any ideas? I'm at the end of my ropes here 🙁
Thanks
Michael
May 27, 2008 at 12:03 pm
You need to put the verb "EXEC " in front of the ""sp_detach_db"'s in order to execute them.
You can get away without it only for the first command in a batch (I think).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 27, 2008 at 12:26 pm
correct, if it's the first proc, you can ignore exec, but as a matter of practice: Use it.
This will work as well:
use master
go
sp_detach_db 'konfig'
go
sp_detach_db 'ndfi'
But do this:
use master
go
exec sp_detach_db 'konfig'
go
exec sp_detach_db 'ndfi'
May 27, 2008 at 10:53 pm
Yeah, it works 🙂
Thanks guy, as hard as I tried, I didn't figure that one out and doubt I would have found it. Hope I learned this now 🙂
Many thanks!
May 27, 2008 at 10:57 pm
glad we could help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply