Create multiple Databases via ExecuteNonQuery command

Question

As part of automating an installation, there's a need to create multiple databases and automate a bunch of things (add users, assign roles and schema, etc);

This following command is executed through ado.net SqlCommand.ExecuteNonQuery which generates an error saying the databases that are just created don't exist.... is there a work around?

Create Database foo ...  Go
Alter Database foo ... Go

Create Database foo2 ...  Go
Alter Database foo2 ... Go

Use Foo
....

Exception: FOO doesn't exist

any help is much appreciated

You can't use GO in an T-SQL command. This is used as command separator by Sql Server Management Studio. It is an error if you put it in a query passed via ExecuteNonQuery. — Steve, Mar 24 '15 at 23:26
You're absolutely correct, unfortunately :( I'm gonna have to do some other funky stuff I guess; executing them one by one, or split the commands by "GO" or something; thanks anyways — DotNet98, Mar 24 '15 at 23:36
I don't know, but this could help http://stackoverflow.com/questions/40814/how-do-i-execute-a-large-sql-script-with-go-commands-from-c — Steve, Mar 24 '15 at 23:52
Thanks for the post Steve; I actually saw that link; and it finally took me to a funny place where I had to process "batches" of sql statements (breaking them by "go"); it was too much; I found a work around.... so all's good... thanks again for your help on this though :) — DotNet98, Mar 25 '15 at 16:44

score 0 · Accepted Answer · answered Mar 25 '15 at 16:42

In case anyone else is wondering, THERE'S DEFINITELY A WORK AROUND!

Save your sql-command to a file
Launch your Powershell or dos command
do a "osql ?" (if this doesn't work you don't have sql client tools). you'll need to install it
run this command

osql -S serverName -E -i PATH_TO_SAVED_SQL_FILE

-E: (case sensitive) is for trusted connections; you may have to use -U and -P for user name and passwords

Create multiple Databases via ExecuteNonQuery command

1 Answers1