Jon Galloway - Handling "GO" Separators in SQL Scripts - the easy way
"If you've ever had to execute one or more SQL scripts from ADO.NET, you've likely run into the GO batch terminator issue....
Until recently, there have been two ways to handle this problem - execute SQL scripts by shelling to OSQL, or splitting the script on GO separators and running them in sequence. Both solutions kind of worked, but SQL Server Management Objects (SMO) has a better solution for us: Server.ConnectionContext.ExecuteNonQuery(), which parses T-SQL statements and "gets" the GO statement as a batch separator. And the crowd goes wild!!!
..."
This is a very cool tip from Jon. And it smells much better than OSQL or GO parsing based solutions...
And for those of us in SQL2k land, it looks like SQL DMO has similar functionality in its SQLServer/SQLDatabase .ExecuteImmediate, .ExecuteWithResults, .ExecuteWithResultsAndMessages methods.
I tested ExecuteImmediate with a test T-SQL script with a number of GO's (creating a table, SP, dropping the SP, creating it again, all in one script with GO's separating the batches) and it seemed to work just as expected...
Related Past Post XRef:
Call SQLCMD from .Net with Output Capture
No comments:
Post a Comment
NOTE: Anonymous Commenting has been turned off for a while... The comment spammers are just killing me...
ALL comments are moderated. I will review every comment before it will appear on the blog.
Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...
I reserve, and will use, the right to not approve ANY comment for ANY reason. I will not usually, but if it's off topic, spam (or even close to spam-like), inflammatory, mean, etc, etc, well... then...
Please see my comment policy for more information if you are interested.
Thanks,
Greg
PS. I am proactively moderating comments. Your comment WILL NOT APPEAR UNTIL I approve it. This may take some hours...