Executing SQL code

Question

I have script to create database and tables(around 50 tables). When I copy paste the script to SQL Management studio it runs fine, but when I try to run from my app I am getting an SQL Exception.

Here is my code:

string enterRecordsScript = "my sql script";
SqlCommand enterRecords = new SqlCommand(enterRecordsScript, connection);
enterRecords.ExecuteNonQuery();

And here is the exception:

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Incorrect syntax near 'GO'.
'CREATE VIEW' must be the first statement in a query batch.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near 'GO'.

Here is the Script:

CREATE DATABASE TEST3
GO
USE Test3;

CREATE TABLE [SAShoppingInvoice]
(
    [Guid] uniqueidentifier PRIMARY KEY NOT NULL ROWGUIDCOL,
    [Number] nvarchar(50),
    [DateOfShopping] datetime,
    [SubTotal] decimal(18,2) DEFAULT 0,
    [PSubTotal] decimal(18,2) DEFAULT 0,
    [Freight] decimal(18,2) DEFAULT 0,
    [Total] decimal(18,2) DEFAULT 0,
    [ATotal] decimal(18,2) DEFAULT 0,
    [PriceLevel] nvarchar(20),
    [SummaryTotal] decimal(18,2) DEFAULT 0,
);


CREATE TABLE [SAShoppingInvoiceDetail]
(
    [Guid] uniqueidentifier PRIMARY KEY NOT NULL ROWGUIDCOL,
    [Number] nvarchar(50),
    [UnitPrice] decimal(18,2) DEFAULT 0,
    [Quantity] decimal(18,2) DEFAULT 0,
    [Description] nvarchar(100),
);



ALTER TABLE [SAShoppingInvoiceDetail]
ADD ShoppingInvoiceGuid uniqueidentifier
CONSTRAINT FK_SAShoppingInvoice_ShoppingInvoice_To_SAShoppingInvoiceDetail_ShoppingInvoice
FOREIGN KEY (ShoppingInvoiceGuid) 
REFERENCES [SAShoppingInvoice](Guid);

GO
CREATE VIEW SAShoppingInvoice_SummaryTotal WITH SCHEMABINDING AS SELECT ShoppingInvoiceGuid,  SUM(ISNULL(Quantity,0)*ISNULL(UnitPrice,0)) as SummaryTotal, COUNT_BIG(*) as CountBig
FROM dbo.SAShoppingInvoiceDetail Group By ShoppingInvoiceGuid;
GO
CREATE UNIQUE CLUSTERED INDEX idx_SAShoppingInvoiceSummaryTotal ON SAShoppingInvoice_SummaryTotal(ShoppingInvoiceGuid);

Why running the script from my app is making a problem?

like is says `CREATE VIEW` must be the first statement in a query batch. means that you should use a `Go` before `CREATE VIEW` statement — farid bekran, Jul 11 '15 at 09:20
@faridbekran No, the fact that `GO` is included is the problem. Putting more `GO`s in it won't solve anything. — , Jul 11 '15 at 09:23

score 4 · Accepted Answer · answered Jul 11 '15 at 09:22

GO is not valid SQL. It is used by some specific tools (such as SSMS) to split a large text file into multiple separate commands. Within your own programs, that becomes your responsibility.

If you have a text file

a
GO
b
GO
c
GO

then if you want to execute this using SqlCommand objects, you need three commands, one containing a, one containing b, and one containing c. None of those three includes GO.

Executing SQL code

1 Answers1