How it Works: SQL Command Mode Parsing

  • Article

I ran across a teaser this week that makes for a interesting blog post. SQL Server uses a common batch parser for the SQLCMD, OSQL, SSMS Query Window in Command mode and various other locations. The primary job of this parser is to identify GO delimited batches. However, it can also identify the extended commands as outlined in SQL Server Books Online.

Let's use the following .SQL file as our example. The !! (bang bang) executes the specified command. Other command such as ':Help' are also available.

=============================================

select @@VERSION
go
!! notepad.exe
go  

-- Runs notepad.exe before the select @@VERSION
select @@VERSION
!! notepad.exe
go

=============================================

When reading the .SQL file you read from top to bottom and have an expectation that the commands are executed in order. This is true but you need to understand the order of execution to make sense of the pattern.

From the example the execution will be the following.

 

                1. @@VERSION
                2. Notepad
3.             Notepad
4. @@VERSION  

Instead of  

                1. @@VERSION
                2. Notepad
                3. @@VERSION
                4. Notepad

Note: Using the -X parameter for SQLCMD shows the error messages in the order I just described as well.

The parser is designed to fire an event when a rule has been reduced. So a batch is considered reduced when the GO is located. The SQL Commands are considered reduced when the end of line is reached. So as the parser is searching for the GO delimiter it may encounter the SQL Commands. When this occurs the SQL Command is carried out.

BEST PRACTICE: Place the SQL Command(s) in separate go delimited batches as the example shows to get the behavior you are looking for.

EXAMPLE - Running .SQL from .SQL file

-----------------------------------------------------------------------
-- This example shows how to run a second T-SQL script from the first T-SQL script
--
-- *** Be careful because .TSQL using !! allows execution of any program so keep the script
-- *** safe for users.
--
-- Note this script would be easier if written like InstPubs.sql but for example purposes
-- here is how it could be done.
-----------------------------------------------------------------------

-- Reference: https://blogs.msdn.com/psssql/archive/2008/09/18/how-it-works-sql-command-mode-parsing.aspx
-- This has to be the first in the script. All : and !! commands in the same
-- go delimited block are executed before the T-SQL commands
:setvar MYNEWDB newdbname
go

print 'ADD/REMOVE started for database "$(MYNEWDB)"'
go

-- UNINSTALL - REMOVE the database
if exists (select name from sys.databases where name = '$(MYNEWDB)')
BEGIN
drop database $(MYNEWDB)

-- Use -b parameter with SQLCMD with a sev of 11 and the script SQLCMD will
-- abort further execution. This way UNINSTALL stops and does not
-- take the install path. This is how the InstPubs.sql is handled.
raiserror('UNINSTALL: Completed successfully', 11, 1)
END
ELSE
BEGIN -- INSTALL
create database $(MYNEWDB)
print 'INSTALL: Database "$(MYNEWDB)" has been created'
END
go

print 'INSTALL: Creating database objects'
go

!!OSQL -S .sql2005 -E -d $(MYNEWDB) -i "c:tempCreateEverything2.sql"
go

print 'INSTALL: Completed successfully'
go

Why not use :exit or :quit? Remember the directive is carried out as soon as it is reduced. So using :exit instead of the raiserror and -b will exit SQLCMD before the batch is even carried out.

EXAMPLE 2 - Running .SQL from .SQL file using (:r)

:r is another way to execute .SQL from .SQL.

:r "c:tempCreateEverything2.sql"
go

As described as soon as the batch parser locates a directive like !! or :exit or :r the directive is carried out. In the case of :r that file is opened and the data within the file treated as part of the original .SQL file stream. This helps avoid some security concerns like placing the password for a SQL Server authentication user on the !! SQLCMD line. But it also brings up some behavior you need to think about. Conditional logic is not the best in

The following will fail if the CreateEverything2.sql has a go in it because you can't have a go inside an if statement.

if(1=1)
begin
:r "c:tempCreateEverything2.sql"
end
go

If you just inline it like the following it can work

select 1
:r "c:tempCreateEverything2.sql"
go

However, this can fail as some DDL statements are not allowed to execute in a transaction.

Begin tran
:r "c:tempCreateEverything2.sql"
go

!! Execution

The !! execution uses CreateProcess and than does a WaitForSingleObject(hProcess, INFINITE) to wait for the execution of the process to complete. In some cases the behavior will not wait as you might expect. For example, some utilities are shell wrappers and end up calling other versions of the executable. When this occurs the original application can returns and the script continues and you may not expect that. launching an MSI is a great example of this. For MSIs you need to use the start with the /wait parameter.

POWERSHELL

Looks at the SQL Server PowerShell cmdlets and providers shipped with SQL Server 2008 for a better solution. PowerShell allow full functionality for structured logic, control of flow and integration to execute SQL Server queries.

Bob Dorr
SQL Server Principal Escalation Engineer