SQLCMD and the power of the little r

  • Article

I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been the SQLCMD utility.  I am going to demonstrate the use of include files. The following is the complete call syntax for SQLCMD.

 sqlcmd 

 [{ { -U login_id [ -P password ] } | –E trusted connection }] 

 [ -z new password ] [ -Z new password and exit]

 [ -S server_name [ \ instance_name ] ] [ -H wksta_name ] [ -d db_name ]

 [ -l login time_out ] [ -A dedicated admin connection ] 

 [ -i input_file ] [ -o output_file ]

 [ -f < codepage > | i: < codepage > [ < , o: < codepage > ] ]

 [ -u unicode output ] [ -r [ 0 | 1 ] msgs to stderr ] 

 [ -R use client regional settings ]

 [ -q "cmdline query" ] [ -Q "cmdline query" and exit ] 

 [ -e echo input ] [ -t query time_out ] 

 [ -I enable Quoted Identifiers ] 

 [ -v var = "value"...] [ -x disable variable substitution ]

 [ -h headers ][ -s col_separator ] [ -w column_width ] 

 [ -W remove trailing spaces ]

 [ -k [ 1 | 2 ] remove[replace] control characters ] 

 [ -y display_width ] [-Y display_width ]

 [ -b on error batch abort ] [ -V severitylevel ] [ -m error_level ] 

 [ -a packet_size ][ -c cmd_end ] 

 [ -L [ c ] list servers[clean output] ] 

 [ -p [ 1 ] print statistics[colon format] ] 

 [ -X [ 1 ] ] disable commands, startup script, enviroment variables [and exit] 

 [ -? show syntax summary ]

:r is a SQLCMD command that parses additional Transact-SQL statements and sqlcmd commands from the file specified by <filename> into the statement cache.

A simple example:

In SQLQuery1.sql I have the following:

SELECT TOP 5 ProductID,Name,ProductNumber,MakeFlag FROM AdventureWorks.Production.Product

In SQLQuery2.sql I can then reference SQL file 1 as follows:

:r "c:\SQLQuery1.sql"

My results are:

ProductID    Name    ProductNumber    MakeFlag
1    Adjustable Race    AR-5381    0
2    Bearing Ball    BA-8327    0
3    BB Ball Bearing    BE-2349    1
4    Headset Ball Bearings    BE-2908    0
316    Blade    BL-2036    1

 

This may not seem like a big deal, but consider the following scenario. I have a large bat file of SQL that I process in jobs or in SSIS packages. This bat file is doing and setting the same variables over and over again, so I can create include files that declare my variables, and another include file that sets them.

My first file "c:\DeclareVariables.sql" will always change me to the correct DB, set no count on and declare my variables. This becomes  good anchor file for any pre-processing that I may want to do.

 USE [AdventureWorks]

 GO

 SET NOCOUNT ON

 GO

 DECLARE @AccountingStartDate smalldatetime;

 DECLARE @AccountingEndDate smalldatetime;

 DECLARE @DocumentStatusText varchar(400);

 DECLARE @Status tinyint;

"c:\SetVariables.sql" is where is set my variables. You notice that the SQL variable @Status is set to a SQLCMD variable of the same name "Status". This allows me to control that from my batch sql file. I am trying to eliminate any hard coded references in my global files.

 SET @Status = $(Status);

 SET @AccountingStartDate = (SELECT AdventureWorks.dbo.ufnGetAccountingStartDate());

 SET @AccountingEndDate = (SELECT AdventureWorks.dbo.ufnGetAccountingEndDate());

 SET @DocumentStatusText = (SELECT AdventureWorks.dbo.ufnGetDocumentStatusText(@Status));

And finally the batch file "c:\SQLBatch.sql". The first two lines set my include files to variables. This allows for a consistent naming through all my files and allows me to change the files without having to change the batch code. This is good for testing and portability. The next line is where I set the SQLCMD "Status" variable. This variable is then set in the SetVariables.sql file to the TSQL variable @Status that is passed to the function ufnGetDocumentStatusText.

 :setvar DeclareVariablesScript "c:\DeclareVariables.sql"

 :setvar SetVariablesScript "c:\SetVariables.sql"

  

 -- Set Local variable Status (Used in SetVariablesScript)

 :setvar Status 1

  

 -- Declare Global Script Variables

 :r $(DeclareVariablesScript)

 -- Set Global Script Variables

 :r $(SetVariablesScript)

  

 SELECT * FROM AdventureWorks.Sales.SalesOrderHeader soh

 WHERE ((soh.OrderDate > = @AccountingStartDate) AND (soh.OrderDate <= @AccountingEndDate));

 GO

Now all I have to do it execute the file "c:\SQLBatch.sql". If you are familiar with ASP include files this should be easy to understand.

 

The more you use this utility the more you will enjoy it. I will be posting more topics on SQLCMD in the future. Stay Tuned!

 

Technorati Tags: SQLCMD, SQL, SQL Server 2005, SQL Server, TSQL

Comments

  • Anonymous
    September 03, 2007
    I would have to say that one of my favorite new utilities that shipped with SQL Server 2005 has been

  • Anonymous
    September 03, 2007
    Wow - that's way cool! I didn't know about that - love it :) Thanks for sharing -

  • Anonymous
    May 23, 2008
    Thanks Patrick, It's very useful.

  • Anonymous
    July 09, 2011
    I just wish you could pass parameters with :r e.g. :r CheckMyJob.sql job#

  • Anonymous
    September 03, 2011
    There is a free tool "SQLSPlus" (on http://www.memfix.com ) which is like SQLPlus for SQL Server. Very flexible with data formatting (set lines size, pagesize, etc), variables (&, &&), spool, etc

  • Anonymous
    June 19, 2012
    The comment has been removed

  • Anonymous
    June 19, 2012
    The comment has been removed