cmb-web.co.uk

SQLCMD mode in SQL Server Management Studio

In Microsoft SQL Server 2005, Microsoft introduced the command line tool sqlcmd to replace osql.

The SQL Server Management Studio allows you to run your scripts in ’sqlcmd mode’ in the query window.  This allows you to take advantage of the syntax colour coding and variables, while developing and testing sqlcmd scripts.  By default it is turned off, but you can enable it from the ‘Query’ menu for a specific query window.

One important thing to remember about using sqlcmd, is that it uses OLEDB, while Management Studio users the .NET SqlClient.  It is possible, therefore, to obtain different results when the scripts are actually run with sqlcmd at the command line.

Executing multiple SQL statements with SqlCommand

If you try and execute a number of sql statements in one go using the SqlCommand object, you will receive a SqlException: “Incorrect syntax near…” when you call the ExecuteNonQuery().

This is because the “GO” statements cannot be executed within the script. The simple way around this is to break the script into parts by splitting the string on the “GO” statements. Then you can execute the individual commands.

Some sample code:

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