Share via

SQL Server Management Studio: Hacking SQLCMD Mode

  • Article

Notice: This article is geared toward people already familiar with SQLCMD mode in SQL Server Management Studio. Though not extremely advanced, readers should understand documented functionality of SQLCMD first. A basic understanding of DOS Command Shell will also provide the best results from trying these examples.

The steps outlined in this article deal with executing dynamically-generated files. Though we are creating and running our own files in the same script, the results are less-predictable as complexity increases. Whenever trying sample code (from anywhere), you should execute in an environment that can tolerate accidental loss.

Not SQLCMD I Already Tried It...

If you're like me, you've probably spent the last several years in and out of SQL Server Management Studio's SQLCMD mode. It's got some great functionality but activating it came with the cost of disabling IntelliSense. Personally, I've always ended up turning it off and forgetting it for several months... Until Now...

Over the next several posts, I'm going to cover all the issues that made you give up on SQLCMD mode in the past. My goal isn't to make you keep that button active in SSMS, but to make you consider it as the productivity enhancer you'll never forget again.

This post begins with workaround for minor annoyances like the inability to program variables. The second post will quickly move out of command shell basics and provide background for the third post, where the fun really begins. In the third post I'll show you how to write 20 lines of code to create a sample "people" table with 450 million unique names.

Oh yeah... I don't have a solution for using IntelliSense with SQLCMD mode but it'll still be worth it....

But I Can't Programmatically Set My Variables

SQLCMD mode allows you to set variables based on literal values. Unfortunately you can't set variables based on the output of SQL Server. It's also not possible through concatenation of other SQLCMD variables or with environment variables.

(This is not supported)

 :SETVAR FileName (SELECT @@Servername + '.txt')

:SETVAR NewVar $(OldVar1)$(OldVar2)

(This is not supported)

While we can't use the syntax above, we can still get the desired results by generating the script in a second file.

Programmatically Generating a "Variables File"

 The following script generates a file called "GetServerName.sql" in the system temp folder. The file contents, a SETVAR command, will then be read from the same script. 

After executing "GetServerName", we'll use our new variable $(Filepath) to create a test file of system users (server_principals).

Just for fun, we'll also open both files in to review them Notepad at the end. Remember to close Notepad because SQLCMD is patiently waiting and won't continue until you do.

 :OUT $(TEMP)\GetServerName.sql
PRINT ':SETVAR FilePath $(TEMP)\' + @@SERVERNAME + '.txt'
GO
:OUT stdout
:r $(TEMP)\GetServerName.sql
GO
:OUT $(FilePath)
SELECT name, create_date 
FROM master.sys.server_principals
GO
:OUT stdout
!!notepad $(TEMP)\GetServerName.sql
!!notepad $(FilePath)

 

Some important items to note:

  • Observe the file path when notepad opens the first file (GetServerName.sql). Notice that the system parameter, $(TEMP), has already resolved itself to the full path of the temp directory.
  • Be aware of the backslash character ( \ ) preceding a variable as it behaves like an escape character, negating the $ of the next variable.

There's no conditional (If/Then) logic in SQLCMD

True again! And how do we fix that? We're going to solve this one with another dynamically-generated file.

Suppose we wanted BULK IMPORT a local file from a drop folder, run BCP, or process a set of other SQLCMD functions. If our target files didn't exist, we'd normally have no way to exit the script. However, we can use DOS commands to perform similar actions to those in the previous example. We do this by generating a file that contains one of two scripts (based on a condition being met). We first create a "Fail/Exit" file. As we execute, we replace the file when we succeed.

 -- SQLCMD Error Handling
:setvar ErrorCheckFile "ErrorCheckFile.sql"
 
-- Set DOS commands that will display in the output window and run SQLCMD EXIT.
:setvar ErrorStatusCommand "(ECHO !!ECHO ValidationFailure. Terminating Script. && ECHO :Exit)"
 
-- The following line creates the file. Use this line for each situation that might require us to stop.
!! $(ErrorStatusCommand) > $(TEMP)$(ErrorCheckFile)
 
-- See if a file exists. If so, replace our EXIT command with a blank file.
!! If EXIST $(TEMP)\*.* @ECHO. > $(TEMP)$(ErrorCheckFile)
 
-- Now read/execute the result
:r $(TEMP)$(ErrorCheckFile)
!! ECHO Found files in the Temp folder (this is normal).
!! ECHO.
!! ECHO Checking Next File.
 
-- Reset the file back to Exit
!! $(ErrorStatusCommand) > $(TEMP)$(ErrorCheckFile)
-- Try again with a file that shouldn't exist
!! If EXIST $(TEMP)\ThisFileShouldNotBeHere.txt @ECHO. > $(TEMP)$(ErrorCheckFile)
 
-- Now read/execute the result
:r $(TEMP)$(ErrorCheckFile)
!! ECHO You should not see this.
PRINT 'You should not see this either'

If our script ran correctly, the output window will contain the following text:

Found files in the Temp folder (this is normal). Checking Next File. ValidationFailure. Terminating Script.

Okay... Fancy DOS tricks... Is that it?

Nope. This is where we start to have fun. We'll take it up a level in the next post but we needed to do the basics first. For now, I'll leave you with a way to perform loops.

This script executes itself in a loop. In order for it to work, you need to save it in the temp folder as "Loop.sql"  (the quickest way to do that is to put %TEMP%\Loop.sql in the Save-As box). Alternatively, you can update the script to reflect the file path of your choosing.

 

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#state') IS NULL

BEGIN

    CREATE TABLE #state(TheStart DATETIME2)

    INSERT #state VALUES(DATEADD(S, 1, SYSDATETIME()))

END

GO

PRINT 'Beginning Loop1'

GO

DECLARE @TimeRemaining INT

SELECT TOP 1 @TimeRemaining = DATEDIFF(MS, SYSDATETIME(), TheStart) FROM #state

PRINT CAST(@TimeRemaining AS VARCHAR) + ' milliseconds to go...'

GO

:out $(TEMP)\loop2.sql

IF SYSDATETIME() <=(SELECT TOP 1 TheStart FROM #state)

BEGIN

    PRINT 'PRINT ''Hello from loop2'''

    PRINT ':r $(TEMP)\loop.sql'

END

ELSE

BEGIN

    PRINT 'PRINT ''Last visit to Loop2!'''

    PRINT 'DROP TABLE #state'

END

GO

:out STDOUT

WAITFOR DELAY '0:00:00.1'

GO

PRINT 'Running loop-checker...'

GO

:r $(TEMP)\loop2.sql

 

 A final note about debugging

From my experience with SQLCMD Mode, the most-common causes of unexpected results were attributed to a missing GO statement or not inserting white space between lines.

  • SQLCMD loves white space. If your command isn't working correctly, try adding a space above it... and below it...
  • SQLCMD also loves to parse everything and it won't want for the SQL Engine to return a response. If you need SQLCMD to work with an output from SQL Server, throw in a GO statement.
  • Remember not to put a backslash in front of a variable name. This does NOT work: $(TEMP) \ $(FileName)
  • Add more vertical space. Add more GO statements.