Run SQL comand with String from SQL Script

Using SQL 2005 I generated a SQL Script to create a DB Table and columns. in
VB.NET i use the StreamReader to read this file to a string, and I want to
execute the script in my application, so the app can create an identical
table at runtime.

Obvously if i simply run the SQL Script from the file, it works, and if I
copy the StreamReader String while debugging into a SQL query, it also works
(creates the table) fine. However, when the program steps to the
SqlCmd.ExecuteNonQuery( ), I get a SQL Exception, "Syntax error near....".
The sql script (and command string) are very long, (Many tables, many
properties... many "Syntax error near"). I've tried to Replace single
(apostrophe) ' with '' and even single (quote) " with "", but the same
result. How come if I paste the string directly from the app to the query
analizer it works, but errors out in the app? Any suggestions?

Thanks
MATT

MATT
Sun May 07 02:41:02 CDT 2006

In the SQL Script, after each EXEC line, it is followed by a 'GO' command.
When i remove the go, it seems to work much better. Can Anyone explain?

"MATT" wrote:

> Using SQL 2005 I generated a SQL Script to create a DB Table and columns. in
> VB.NET i use the StreamReader to read this file to a string, and I want to
> execute the script in my application, so the app can create an identical
> table at runtime.
>
> Obvously if i simply run the SQL Script from the file, it works, and if I
> copy the StreamReader String while debugging into a SQL query, it also works
> (creates the table) fine. However, when the program steps to the
> SqlCmd.ExecuteNonQuery( ), I get a SQL Exception, "Syntax error near....".
> The sql script (and command string) are very long, (Many tables, many
> properties... many "Syntax error near"). I've tried to Replace single
> (apostrophe) ' with '' and even single (quote) " with "", but the same
> result. How come if I paste the string directly from the app to the query
> analizer it works, but errors out in the app? Any suggestions?
>
> Thanks
> MATT

Jim
Sun May 07 11:59:34 CDT 2006

GO is not a TRANSACT SQL keyword

It is a batch separator used by Query Analyzer (and its siblings).

Split your script on the GO keyword and execute each segment by itself.

"MATT" <MATT@discussions.microsoft.com> wrote in message
news:E25AB483-02BA-4818-BA09-99120A64A34C@microsoft.com...
> In the SQL Script, after each EXEC line, it is followed by a 'GO' command.
> When i remove the go, it seems to work much better. Can Anyone explain?
>
> "MATT" wrote:
>
>> Using SQL 2005 I generated a SQL Script to create a DB Table and columns.
>> in
>> VB.NET i use the StreamReader to read this file to a string, and I want
>> to
>> execute the script in my application, so the app can create an identical
>> table at runtime.
>>
>> Obvously if i simply run the SQL Script from the file, it works, and if I
>> copy the StreamReader String while debugging into a SQL query, it also
>> works
>> (creates the table) fine. However, when the program steps to the
>> SqlCmd.ExecuteNonQuery( ), I get a SQL Exception, "Syntax error
>> near....".
>> The sql script (and command string) are very long, (Many tables, many
>> properties... many "Syntax error near"). I've tried to Replace single
>> (apostrophe) ' with '' and even single (quote) " with "", but the same
>> result. How come if I paste the string directly from the app to the
>> query
>> analizer it works, but errors out in the app? Any suggestions?
>>
>> Thanks
>> MATT

Jeff
Mon May 08 11:32:36 CDT 2006

May I assume you have a simple test script that IS working?

Jeff

"MATT" <MATT@discussions.microsoft.com> wrote in message
news:4274740D-FDC8-44DD-8368-F0794F8124C9@microsoft.com...
> Using SQL 2005 I generated a SQL Script to create a DB Table and columns.
> in
> VB.NET i use the StreamReader to read this file to a string, and I want to
> execute the script in my application, so the app can create an identical
> table at runtime.
>
> Obvously if i simply run the SQL Script from the file, it works, and if I
> copy the StreamReader String while debugging into a SQL query, it also
> works
> (creates the table) fine. However, when the program steps to the
> SqlCmd.ExecuteNonQuery( ), I get a SQL Exception, "Syntax error near....".
> The sql script (and command string) are very long, (Many tables, many
> properties... many "Syntax error near"). I've tried to Replace single
> (apostrophe) ' with '' and even single (quote) " with "", but the same
> result. How come if I paste the string directly from the app to the query
> analizer it works, but errors out in the app? Any suggestions?
>
> Thanks
> MATT