T-SQL ‘GO’ and SqlCommand

Try to execute a T-SQL script which contains several SQL batches separated by the GO keyword using .Net’s SqlCommand, and you will receive an error message

Incorrect syntax near ‘GO’.

What’s going on here? Why does it work from SSMS or sqlcmd, but not in SqlCommand?

The reason for this behavior is that ‘GO’ is not a statement of T-SQL, but rather

a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor

(MSDN). You think you might simply remove all occurrences of GO inside your statement, but that will not always work: Some statements require to be the first statement in a batch

‘CREATE/ALTER PROCEDURE’ must be the first statement in a query batch.

So how do you execute a set of SQL statements containing ‘GO’ in C#?

The “lazy” way is to simply split the SQL string at each occurrence of ‘GO’ using string.Split(), but this is really lazy.

Why? Because of the complexity of the language that the T-SQL shells are capable of processing:

• string literals ” and N”
• single line comments –
• multi-line comments /* */
• nested multi-line comments (!)

The characters ‘GO’ must not be recognized if they occur inside any of these constructs, and they need not be the first characters in a line – the line may also contain spaces or whitespace or comments (just give it a try in SSMS, it’s truly amazing).

The more practical way (mentioned here) to execute an arbitrary SQL string is to use SMO’s Server.ConnectionContext.ExecuteNonQuery().

Or, you write a parser using ANTLR, define the correct grammar to split at the relevant GO keywords, and execute the list of resulting SQL batches separately.

automssqlbackup 0.31 supports MS SQL Server 2012

automssqlbackup has been idle for 3 years now, not because I discontinued the script, but simply because it worked! (Thanks everybody for their feedback!)

With the arrival of a new version of SQL Server it was time to adjust the assembly references in the code.

What you will now find is a set of paths to default SQL Server SMO assembly locations, of which you need to activate the one that applies to your system.

automssqlbackup is available for download here.

SMO failing on SQL Azure

I was notified that SMOscript fails to generate scripts for SQL Azure databases, see SO here and here.

The reported error message is

Objects in database [DatabaseName] on server [ServerAddress]:

Error:

Index was outside the bounds of the array.

I found reports about SMO access failing on SQL Azure, but I do not have confirmed information whether SP1 of 2008R2 or this Azure SR fix the problem.

If you have information on how to solve this problem, please leave a comment here or answer on SO. Thanks.

Searching for String Literals in T-SQL Code

If you have ever worked in a multi-language (i.e. localization-aware) project, you know that you should avoid string literals embedded in code.

To make sure that no untranslatable error messages, warnings or other text that might end up in the user interface is embedded in T-SQL code (MS SQL Server stored procedures, functions, and triggers), you’d need to search each of the code blocks for the string delimiter ‘ (single quote).

I’ll sketch a solution called tsqlfindstrings implemented in PowerShell using SMO to access the code stored in a MS SQL Server database, and .Net regular expressions to find string literals. The script tsqlfindstrings.ps1 needs to be configured similarly to my previous PowerShell solution automssqlbackup.

First, the SQL Server-specific SMO libraries are loaded

$mspath = "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\"
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.ConnectionInfo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.Smo.dll")
$dummy = [System.Reflection.Assembly]::LoadFrom($mspath + "Microsoft.SqlServer.SmoExtended.dll")

and connection parameters $dbhost, $dbname, $username, and $password are set.

Next, we define string literals to be excluded from the search results, either complete strings or string beginnings, as string arrays:

$excludedStrings = @(" (", ")", "%", "*", " - ", ", ", "(", "true", "false", " ", """", "=""",
  "uniqueidentifier", "datetime", "int", "decimal", "bit", "float", "Z",  "string", "boolean", "guid")
$excludedBegins = @("'")

The example shows T-SQL keywords as excluded strings and the single quote as first character in a string literal (both used for dynamic SQL, so we want to ignore these).

We connect using the standard SMO objects ServerConnection and Server:

$conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server($conn)
[void] $srv.Initialize($false) 
[void] $srv.Refresh() 
[void] $srv.SetDefaultInitFields($true)

to find our database $db in $srv.Databases.

In its current version, the script checks these SMO properties for string literals:

• Tables.Checks.Text
• Tables.Columns.Default
• Views.TextBody
• StoredProcedures.TextBody
• UserDefinedFunctions.TextBody
• Triggers.TextBody

If a single quote is found in the property values, the Extract subroutine is called to extract the string literal and match it with the defined excluded strings:

foreach($m in [RegEx]::Matches($s, "'(.+?)'[^']") |
  Where-Object { ($excludedStrings -notcontains $_.Groups[1]) })
{
  $g = $m.Groups[1].Value
  $do = $true
 
  foreach($b in $excludedBegins)
  {
    if ($g.StartsWith( $b ))
    {
      $do = $false
    }
  }

  if ($do)
  {
    Write-Host ($type + " " + $name + ": '" + $g + "'")
  }
}

The first released version of tsqlfindstrings is available for download here.

Strange SMO Errors

I came across some strange SMO errors today running my SMOscript utility on a new SQL Server 2008 database (10.0.2531.0):

Error: An exception occurred while executing a Transact-SQL statement or batch.

and

Error: Could not find stored procedure ‘msdb.sys.sp_getProcessorUsage’.

A web search did not yield any results.

It turned out that the code was calling Server.Initialize(false) twice, and removing one of the calls caused the program to run without raising exceptions.

Since the last version of SMOscript is dated May 2010, and I have some files created by SMOscript dated July 2010, some magic event must have caused a different behavior in the SMO libraries. I have no idea.

Scripting all MS SQL Databases using SMOscript

SMOscript user Eric reported that the tool fails to script all remaining objects of a database once an error occurs trying to generate a CREATE PROCEDURE statement for an encrypted stored procedure.

This problem has been fixed in the latest version 0.14 which is now available for download.

Based on his feedback I want to demonstrate more capabilities of SMOscript:

Logging

While SMOscript does not implement log file functionality, you can still redirect its output to files from the command line using the standard shell redirectors >, >> and 2>.

smoscript ...parameters... >filename.log 2>filename.err

Scripting all databases on a server

To script all databases of a server into separate files in distinct directories, you can use SMOscript to first list all databases, then invoke SMOscript to script each database in the original list:

set basepath=c:\path\to\output\
set first=

for /f in "usebackq" %%i in (`smoscript -s localhost`) do ↵
                                         (set db=%%i& call :smo)
goto :end

:smo

if "%first%"=="." (
    echo database %db%
    mkdir %basepath%%db%
    smoscript -s localhost -d %db% -f %basepath%%db%\create.sql ↵
                                         > %basepath%%db%.log
)
set first=.

goto :eof
:end

The first line of “smoscript -s” is not a database name, so we include it from the list by using the variable named “first”.

The simple combination of SMOscript with a bit of shell magic can be quite efficient.

Schema-Qualified Foreign Keys in SMOscript

The MSSQL SMO library has some funny functionality and options (depending on your sense of humor).

If you tell an Smo.Table to script itself and its dependent objects (indexes, constraints, etc.) using its Script(ScriptingOptions) method, it will correctly generate the table name in Schema.Table notation (schema qualified), but the foreign key constraints to other tables will not contain the schema of the referenced table.

That is, unless you set the ScriptingOptions.SchemaQualifyForeignKeyReferences member to true. Thanks to user Oliver for pointing that out.

SMOscript has now been fixed and is available for download here.

automssqlbackup Update 0.30

A user of automssqlbackup notified me of two problems with the program:

• If you try to backup a database running on a named SQL Server instance (server\instance), creation of the log file caused an error as the “\” is handled like a directory separator, and the server directory could not be found.
• If there is an exception during backup (executed by SMO’s SqlBackup method), the exception message is not displayed.

Both problems are fixed in the latest version 0.30 of automssqlbackup.

Note: By default, automssqlbackup performs a full backup on Sundays, and incremental backups on all other days. So if you run automssqlbackup during the week, and you never (fully) backed up your databases before, you will get an exception for the databases without full backups.

automssqlbackup is available for download here.

Standalone SMO 2008 download

The Microsoft SQL Server 2008 Feature Pack (link to April 2009 version) contains tools and libraries for SQL Server 2008, among them the latest version of SQL Server Management Objects (SMO) which support 2000, 2005, and 2008.

SMO is the technology used by my applications SMOscript and automssqlbackup to query and control SQL Server databases.

Listing database properties using SMO

Recently I came across a couple of questions asking how to retrieve the physical file names of databases via SMO on Stackoverflow.

These questions prompted me to add a “List Database Properties” function to SMOscript.

This new function lists all database properties accessible via SMO, the DatabaseOptions object, and the database’s FileGroups and Files.

Sample output:

C:\Projects\smoscript>smoscript -s localhost -d dbscript2dev db
Database dbscript2dev on server localhost:

Current user dbo is DBO DbOwner

Owner DOMAIN\User Created 26.03.2008 21:23
Accessible True Updatable True SystemObject False
Status Normal CompatibilityLevel Version80
Size 386 MB SpaceAvailable 148.694 kB
DataSpaceUsage 133.016 kB IndexSpaceUsage 15.984 kB
PrimaryFilePath C:\Programme\Microsoft SQL Server\MSSQL\data
Default FileGroup PRIMARY Schema dbo
Collation Latin1_General_CI_AS CaseSensitive False
FullTextEnabled False
LastBackup 20.06.2009 03:01
LastLogBackup 11.09.2008 23:50
ActiveConnections 0

Options AnsiNullDefault False AnsiNullsEnabled False AnsiPaddingEnabled False An
siWarningsEnabled False ArithmeticAbortEnabled False AutoClose False AutoCreateS
tatistics True AutoShrink False AutoUpdateStatistics True CloseCursorsOnCommitEn
abled False ConcatenateNullYieldsNull False DatabaseOwnershipChaining True Local
CursorsDefault False NumericRoundAbortEnabled False PageVerify TornPageDetection
 QuotedIdentifiersEnabled False RecoveryModel Full RecursiveTriggersEnabled True
 UserAccess Multiple

Filegroup PRIMARY
 Default True ReadOnly False Size 318.208 MB
 File dbscriptdev_Data
 Path C:\Programme\Microsoft SQL Server\MSSQL\data\dbscript2dev.mdf
 Primary True
 Size 318.208 MB MaxSize unlimited
 Used 176.768 kB Available 141.440 kB
 Growth 10 %

Logfile dbscriptdev_Log
 Path C:\Programme\Microsoft SQL Server\MSSQL\data\dbscript2dev_log.ldf
 Size 76.736 MB MaxSize unlimited
 Used 70.090 kB
 Growth 10 %

The latest version 0.12 of SMOscript is available for download here.