Generated create scripts don't work in SqlCommand.ExecuteNonQuery()

  • Thread starter Thread starter James Cadd
  • Start date Start date
J

James Cadd

What changes need to be made for a create script that I
generated for a database to be used to create the DB?
Here are the steps I took:

1. Right click a data connection in VS.NET, select
Generate Create Script.
2. Select the options to script all objects; don't script
the database and write all data in one file.
3. In the create file, replace all occurrances of 'GO'
with a semicolon
4. In code, create the database; then switch to the new
database on my sqlConnection
5. Load up create file as a text stream and pass it to
SqlCommand.ExecuteNonQuery.
6. This bombs out near the end of my create file while
trying to create a stored procedure. I get an error that
stored procedures must be at the top of a batch file, and
that I also must define @UserName (used in my SP).

Thanks in advance!
 
One of the "requirements" of the TSQL language and due to the way that the
batch is compiled and executed, you can't simply replace the "GO" separators
and expect the batch to work. Instead, each batch in the script (the blocks
of TSQL code separated by GO) must be executed sequentially one-at-a-time.
This is what QA or OSQL do.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top