none
Executing T-SQL scripts via ADO.NET RRS feed

  • Question


  • I wanted to execute a T-SQL script that contains multiple intervening GO statements programmatically via .NET/C#.  Couple of years ago when I did that I ended up using SMO to get it done because the ADO.NET framework could not properly deal with multiple GO statements.

    I read this blog post of 2009: http://smehrozalam.wordpress.com/2009/05/12/c-executing-batch-t-sql-scripts-with-go-statements/ 

    Is this still the state of play with .NET/C# 4.0?
    Thursday, June 17, 2010 6:41 PM

Answers

  • There has been no change to the SQL Server ADO.NET provider to add support for executing scripts with GO statements.  As you said, still the same state of play, but here's why.

    The SQL Server toolset (including SMO) explicitly parses queries for GO statements, and splits the queries into smaller batches when it finds a GO statement.  In fact, GO is not actually part of the T-SQL language.

        http://msdn.microsoft.com/en-us/library/ms188037.aspx

    I hope this information proves helpful.


    David Sceppa
    Thursday, June 17, 2010 7:46 PM
    Moderator

All replies

  • There has been no change to the SQL Server ADO.NET provider to add support for executing scripts with GO statements.  As you said, still the same state of play, but here's why.

    The SQL Server toolset (including SMO) explicitly parses queries for GO statements, and splits the queries into smaller batches when it finds a GO statement.  In fact, GO is not actually part of the T-SQL language.

        http://msdn.microsoft.com/en-us/library/ms188037.aspx

    I hope this information proves helpful.


    David Sceppa
    Thursday, June 17, 2010 7:46 PM
    Moderator
  • There has been no change to the SQL Server ADO.NET provider to add support for executing scripts with GO statements.  As you said, still the same state of play, but here's why.

    The SQL Server toolset (including SMO) explicitly parses queries for GO statements, and splits the queries into smaller batches when it finds a GO statement.  In fact, GO is not actually part of the T-SQL language.

        http://msdn.microsoft.com/en-us/library/ms188037.aspx

    I hope this information proves helpful.


    David Sceppa

    David

    Thanks for the reply.  I tried doing the alternative method of reading in the whole script, splitting it into batches separated by GO and execute each batch individually (using SqlCommand and friends).  The problem is if I do it that way, I will have to wrap this stuff inside a transaction so that if one batch fails for whatever reason, I can rollback everything I did thus far.  However my script sometimes calls into some system sprocs (sp_dbcmptlevel for example) that *cannot* be called from within a transaction.

     

    That doesn't leave me with any other way than SMO, right?

     

    Thursday, June 17, 2010 9:36 PM
  • I wouldn't recommend trying to split the script into batches programmatically.  Technically, sure, it's possible.  But splitting the script yourself the "right" way involves understanding enough of the SQL language to make sure you don't accidentally pick up on the use of the word go inside of string literals or comments.

    Personally, I've banged my head against the monitor enough times in the past around this exact scenario.  Each time I've felt like "There has to be an easier way other than SMO", though I'll admit that this is the first time I actually looked in SQL Books OnLine for GO to confirm that it's not actually valid T-SQL.

    Having to go the SMO route felt like admitting defeat on some level or that I was running into a silly limitation of the SQL connectivity component (ADO, System.Data.SqlClient, etc.).  But now that I've found that GO isn't part of the T-SQL language, I'm gradually coming to terms with the fact that expecting to execute a query that does not actually contain valid T-SQL was probably an unreasonable expectation on my part.

    SMO isn't some last ditch option.  For executing SQL scripts like the ones you're working with, SMO is the right tool for the job.


    David Sceppa
    Thursday, June 17, 2010 11:49 PM
    Moderator