11

I can't seem to execute SQL that creates a database using a DbCommand object. What am I doing wrong? Here's my code:

DbConnection connection; // initialized and opened elsewhere
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

Here's the error:

The query syntax is not valid., near term '/', line 1, column 2. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.EntitySqlException: The query syntax is not valid., near term '/', line 1, column 2.

Here's the first part of the file. The exception is thrown regarding just the comments on the first line:

/****** Object:  Table [dbo].[User]    Script Date: 10/08/2009 12:14:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [EmailAddress] [nvarchar](100) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This same SQL script executes just fine from SQL Management Studio Express (in fact that app generated this script!). It's just Visual Studio's own Server Explorer query view and from my own code that seems to fail.

Improve this question
2
  • 1
    Note: GO is not a SQL command, it is a command to the client tool (such as OSQL, Query Analyzer, or in this case SMSS.) to break the script into seperate "batches". Each batch is an seperate call to the server. Commented Oct 8, 2009 at 22:24
  • Possible duplicate of Execute a large SQL script (with GO commands) Commented Jun 9, 2016 at 2:15

4 Answers 4

Reset to default
12

You need to use the SQL management classes instead of the normal SqlCommand. This page shows you how to do it. If you try to parse the SQL yourself then there will always be edge cases that you miss. For example, what if a string within the code contains the word "GO" with leading and trailing carriage returns?

Add these references:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.ConnectionInfo
  • Microsoft.SqlServer.Management.Sdk.Sfc (Edit: This reference isn't needed)

Then you can use this code:

string connectionString, scriptText;
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Awesome. I couldn't find the Microsoft.SqlServer.Management.Sdk.Sfc assembly to reference, but it turned out it wasn't necessary anyway.
8

Here is a code snippet that I posted on my blog some time ago that may solve this problem:

private static void RunScript(SqlConnection connection, string script)
{
    Regex regex = new Regex(@"\r{0,1}\nGO\r{0,1}\n");
    string[] commands = regex.Split(script);

    for (int i = 0; i < commands.Length; i++)
    {
        if (commands[i] != string.Empty)
        {
            using(SqlCommand command = new SqlCommand(commands[i], connection))
            {
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }
    }
}

It splits the SQL script into separate commands and executes each of them. I regularly use this to set up test databases with generated SQL scripts.

Improve this answer

5 Comments

+1, but I don't think this is the only problem here. The error is complaining about the comment at the very top of the file.
@Joel: I think that it may be that it actually complains about the full statement, which happens to start with the comment. Can't be sure though, so I have edited the text to be less certain in whether it provides a solution or not.
the GO batch seperator is valid surronded by white space, at least in SMSS for SQL Server 2005.
@Mork: command.Dispose() doesn't need anymore because it is inside using statement
Works like a charm even with complex stuff
1

I find it strange that you're getting an EntitySqlException ...

Another solution that you can choose, is to execute this script via the osql command-line tool. You could create a System.Diagnostics.Process instance, and use this process to call osql which in turn executes the script.

System.Diagnostics.Process p = new System.Diagnostics.Process ();

p.StartInfo.FileName = Environment.GetEnvironmentVariable ("COMSPEC");
p.StartInfo.UseShellExecute = false;
p.StartInfo.ErrorDialog = false;

p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.RedirectStandardOutput = true;

p.Start ();

p.StandardInput.WriteLine ("echo off");
string command = @"osql -U -b -e -S " + servername + " -d " + databasename + " -i \'" + filename + "\'";
p.StandardInput.WriteLine (command);
p.StandardInput.WriteLine ("exit");

p.WaitForExit ();
Improve this answer

1 Comment

Thanks for the tip. This may prove useful in another scenario of mine.
0

in SQL Server you can concatenate as many queries as you want with a simple space separator but for that you need to remove the "GO"s.
example :

BEGIN TRANSACTION   SET QUOTED_IDENTIFIER ON  SET ARITHABORT ON  SET NUMERIC_ROUNDABORT OFF   SET CONCAT_NULL_YIELDS_NULL ON   SET ANSI_NULLS ON   SET ANSI_PADDING ON   SET ANSI_WARNINGS ON   COMMIT   BEGIN TRANSACTION   $remove this GO here$   CREATE TABLE dbo.Tmp_Tralala(   ERRID numeric (18,0) NOT NULL) ) ON [PRIMARY]   $remove this GO here$   IF EXISTS(SELECT * FROM dbo.Tralala)   EXEC('INSERT INTO ..etc
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.