ADO: Execute multiple TSQL using connection and command object

Question

For a particular installation of my application, I need to create the database and the schema on the SQL server from the installer itself. I have a custom installer through which I have been able to detect and install the pre-requisites and the software. The user is prompted to give the IP of the database server and the username and password. Behind the scene, I create a connection and a command object. I keep the queries in different files. I use a reader and read the content of the file and set the content of the file to the CommandText of the command object. The typical content of the file is like following:

create database mydatabase
Go

Use mydatabase
Go

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  

Now the issue is the first statements get executed but it gives error after that. The error that is shown is: "syntax error near 'GO'". I tried removing the GO statement and also tried ending the sql statements with semi-colon. The error in this case is "Database 'mydatabase'does not exist. Make sure that the name is entered correctly.".

However if I keep a single statement in the file, it works fine.

Can somebody help me?

Answer 1

As you can see at http://technet.microsoft.com/en-us/library/aa258908%28SQL.80%29.aspx

Remarks

GO is not a Transact-SQL statement; it is a command recognized by the osql and isql utilities and SQL Query Analyzer.

So this is the cause of your problems when you run it using the SqlCommand from .Net. In my opinion you have two options:

1) Execute the instructions one by one. Maybe use a separator in your files, then split the SQL statements and execute them sequentially using a for/foreach.

2) Use Server class from SQL Server Management Objects (SMO) that should allow you to execute the script containing "Go" statements.

Answer 2

You can execute more than one sql command statement by simply adding a ";" at the end of each command instead of a "GO" statement.

Example:

cmd.CommandText = @" Update TableA Set ColumnA = 'Test' Where ID = 1;
                Update TableB Set ColumnA = 'Second line' Where ID = 2;
          ";