SqlCommand() ExecuteNonQuery() truncates command text

Question

I'm building a custom db deployment utility, I need to read text files containing sql scripts and execute them against the database.

Pretty easy stuff, so far so good.

However I've encountered a snag, the contents of the file are read successfully and entirely, but once passed into the SqlCommand and then executed with SqlCommand.ExecuteNonQuery only part of the script is executed.

I fired up Profiler and confirmed that my code is not passing all of the script.

    private void ExecuteScript(string cmd, SqlConnection sqlConn, SqlTransaction trans)
    {

        SqlCommand sqlCmd = new SqlCommand(cmd, sqlConn, trans);
        sqlCmd.CommandType = CommandType.Text;
        sqlCmd.CommandTimeout = 9000000; // for testing
        sqlCmd.ExecuteNonQuery();

    }

    // I call it like this, readDMLScript contains 543 lines of T-SQL
    string readDMLScript = ReadFile(dmlFile);
    ExecuteScript(readDMLScript, sqlConn, trans);

Bonni

What character is the script truncated at? – Celerity

how does the ReadFile method work? Are you 200% sure it's not skipping a few characters, maybe?? Why not just use System.IO.File.ReadAllText(filename) ?? – Dowitcher

how much text are you reading from the file, in bytes? – Dowitcher

Thanks for the help Marc, I was using a Stream reader. I'll switch to System.IO.File.ReadAllText(filename) for the sake of clarity. Now onto the issue, I suspected that the script was messed up because when executing it using the SqlCommand exceptions relating to the syntax of the script were raise while it ran fine in Sql Management Studio. It looks like I had some head trauma this morning it turns out that the script is not truncated. – Bonni

The exceptions are still holding me back though. Do you know why something like this : System.Data.SqlClient.SqlException: Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Incorrect syntax near 'GO'. Would be visible under the SqlCommand but not under Sql Management Studio when executing a script? – Bonni

Answer 1

Yep, everyone hits this snag the first time they start sending the contents of SQL script files to the database.

GO is not a T-SQL command. It's the end-of-batch marker recognised by all the Microsoft interactive SQL tools (Management Studio, isql, osql). In order to handle it, you will have to write your own parser to break out every block of text in the file between GO statements and feed them to the database as separate commands.

How you implement your parser is up to you. It could be simple (read in each line at a time, detect lines that consist of nothing but GO and whitespace) or complex (tokenising all the statements and working out whether a GO is a genuine statement or a bit of text inside a string or multi-line comment).

Personally I went with the first option. It handles 99% of all SQL files you are ever likely to encounter with no fuss. If you want to go the whole hog and write a tokeniser, I'm sure lots of people have done one already, just Google for it.

Example:

using(var reader = new SqlBatchReader(new StreamReader(dmlFile))) {
    string batch;
    while((batch = reader.ReadBatch()) != null) {
        var cmd = new SqlCommand(batch, conn, trans) { CommandType = CommandType.Text };
        cmd.ExecuteNonQuery();
    }
}

class SqlBatchReader : IDisposable {
    private TextReader _reader;
    public SqlBatchReader(TextReader reader) {
        _reader = reader;
    }
    /// <summary>
    /// Return the next command batch in the file, or null if end-of-file reached.
    /// </summary>
    public string ReadBatch() {
        // TODO: Implement your parsing logic here.
    }
}

Finis

Unless your script file starts with 'SET IDENTITY_INSERT <table_name> ON'. Is there no good solution to export data to a file for .NET to then import? – Whencesoever

@MStodd: SET IDENTITY_INSERT is intended for use in data import scripts, so it is a good solution. Personally, though, I would use SSIS to import data. – Finis

The problem I have now is if I export a script from SSMS starting with 'SET IDENTITY_INSERT <table_name> ON', it won't be enough to use your code will it? Won't I need to execute that command before every insert? – Whencesoever

@MStodd: SET IDENTITY_INSERT stays set until you turn it off, you only have to set it once. What problem are you having with your script exactly? – Finis

I'm having this issue: #7715312 – Whencesoever

@MStodd: I see. However, that will only be an issue if the connection is reset between commands. If you keep it open, as you would with my code above or with SSIS, then it should work fine. – Finis

Answer 2

I found the code below while searching for an answer to this issue:

http://blogs.msdn.com/b/onoj/archive/2008/02/26/incorrect-syntax-near-go-sqlcommand-executenonquery.aspx

Pros: It's short and simple to understand and worked perfectly for my needs.

Cons: It is less efficient than Stream based solutions and is case sensitive (i.e "GO" not "go").

string[] commands = sql.Split(new string[]{"GO\r\n", "GO ", "GO\t"}, StringSplitOptions.RemoveEmptyEntries );
foreach (string c in commands)
{
    var command = new SqlCommand(c, masterConnection);
    command.ExecuteNonQuery();
}

Meddlesome

Answer 3

ExecuteNonQuery in SMO works with batches:

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.executenonquery.aspx

Alla

Having now used the SMO library I would advise against it and recommend one of the pure .net solutions. – Alla

Answer 4

Answer based on comments under the original post:

GO is a marker for Management Studio / osql / isql. It tells to send a batch of commands to SQL Server. In your utility, you should split the input data using GO as a delimiter and send each element individually (without the GO command)

Unsuitable

Answer 5

This is what we use :)

public static class ExtensionMethodsSqlCommand
{
    #region Public

    private static bool IsGo(string psCommandLine)
    {
        if (psCommandLine == null)
            return false;
        psCommandLine = psCommandLine.Trim();
        if (string.Compare(psCommandLine, "GO", StringComparison.OrdinalIgnoreCase) == 0)
            return true;
        if (psCommandLine.StartsWith("GO", StringComparison.OrdinalIgnoreCase))
        {
            psCommandLine = (psCommandLine + "--").Substring(2).Trim();
            if (psCommandLine.StartsWith("--"))
                return true;
        }
        return false;
    }

    [System.Diagnostics.DebuggerHidden]
    public static void ExecuteNonQueryWithGos(this SqlCommand poSqlCommand)
    {
        string sCommandLong = poSqlCommand.CommandText;
        using (StringReader oStringReader = new StringReader(sCommandLong))
        {
            string sCommandLine;
            string sCommandShort = string.Empty;
            while ((sCommandLine = oStringReader.ReadLine()) != null)
                if (ExtensionMethodsSqlCommand.IsGo(sCommandLine))
                {
                    if (sCommandShort.IsNullOrWhiteSpace() == false)
                    {
                        if ((poSqlCommand.Connection.State & ConnectionState.Open) == 0)
                            poSqlCommand.Connection.Open();
                        using (SqlCommand oSqlCommand = new SqlCommand(sCommandShort, poSqlCommand.Connection))
                            oSqlCommand.ExecuteNonQuery();
                    }
                    sCommandShort = string.Empty;
                }
                else
                    sCommandShort += sCommandLine + "\r\n";
        }
    }

    #endregion Public
}

Ovolo

Why don't you add some description of the code example you have posted? It's easier to read the description and understand that solution work or doesn't work for you than spend some time to analyze what the code is doing. – Volkman

Answer 6

I ended up writing an implementation of a StringReader to do this.

It handles:

1. Skipping past GO contained in dash dash comments
2. Skipping past GO contained in slash star comments
3. Skipping past GO contained in literals (i.e single quotes)
4. Skipping past GO contained in column names etc.

Therefore it will only detect the keyword GO when used as a batch seperator. This means it splits the SQL text correctly.

It also handles if you have appended a sql terminator (semicolon) to the word GO

You can find the code for it here:

You use it like so:

using (var reader = new SqlCommandReader(scriptContents))
       {
            var commands = new List<string>();
            reader.ReadAllCommands(c => commands.Add(c));
            // commands now contains each seperated sql batch.
        }

Enamor