views:

40

answers:

3

It is possible to run multiple commands or sql script using OLE DB? For example to run sql script to create database and its structure (tables, indexes, stored procedures...).

When I use CCommand class, I can run only one command. How can I run SQL script with multiple commands?

Thanks, Martin

+1  A: 

I've sent multiple statements like INSERT INTO;INSERT INTO (semicolons are not required in SQL Server.

Some statements (like CREATE FUNCTION) have to be the first statement in a "Batch". In SSMS and sqlcmd, you have a GO separator, which is a client-side tool for separating batches. This is not an OLEDB feature, so you would have to send them separately - in a separate invocation in series or parallel (depending on whether your operations can run simultaneously).

If you can get away from CCommand, I guess you could launch sqlcmd, since it supports GO batch separators.

Cade Roux
Yup; I usually just separate with semicolons. You can use it to queue up groups of 'harmless' queries in order to reduce roundtrips if it's not a local server
Kieren Johnstone
+1  A: 

The GO command which separates TSQL statements is NOT a SQL command, but a statement recognized by the front end only.

Therefore you need to explicitly split your script into several commands at "\nGO" before passing the result to the command object.

devio
+1  A: 

Martin - I have had this same issue. I assume that when you load a script with one or more "go" in it you get an error?

I have used SQL DMO in the past to run scripts with GO in them. That does require that SQLDMO be installed on your target computer.

Another option is to use the .NET "String.Split("GO")" function, and loop the resulting array of strings, exexuting them one by one.

Like this:

StreamReader file = File.OpenText("c:\\MyScript.sql");
SqlConnection conn = new SqlConnection("My connection string here...");
string fullCommand = file.ReadToEnd();
string[] splitOptions = new string[] {"go", "GO", "gO", "Go" };
foreach (string individualCommand in fullCommand.Split(splitOptions, StringSplitOptions.RemoveEmptyEntries))
{
    SqlCommand comm = new SqlCommand(individualCommand, conn);
    comm.ExecuteNonQuery();
}

Disclaimer: I have not tested the above code, but it should give you an idea of what is required :-)

Joon
Yes, thats my problem. I am using C++ and OLE DB, not ADO .NET but thats it. I will try it. Thanks
martin pilch
@martin - You might want to check that the Go is at the beginning of the line as well to reduce the risk that it splits into batches inappropriately (e.g. if you have a table name containing the string `go`) - Or just use some other less likely delimiter.
Martin Smith