Welcome to SQLCMD

Last couple of weeks I was mainly working in the backend side.Its nothing but SQL server 2008.We had to develop a bunch of scripts which will help the production DBAs to accomplish their tasks with less effort.Since the production environment is unknown and will vary to country to country we cannot put dbo.<table name> in any of our scripts.So we decided to put some tokens in the scripts like #schema#,#DB# etc…the DBA needs to replace these tokens in his environment before running the scripts.
Things went fine till the scripts were reviewed by Microsoft consultant Dimitri Furman .He suggested to use SQLCMD variables.
What is this SQLCMD variables? Do we really need to use that? But after having a script file of 100MB we automatically preferred the sqlcmd mode because SQL Server Management Studio is not suitable for executing such a big sql file.It will definitely crash.

Reasons why we selected SQLCMD

• We have more files which needs to share the variables.Replacing in all the files is tedious.
• We have large files which cannot be executed in SSMS.

SQLCMD is the command prompt type interface to execute sql queries.You can start the shell using the executable located in the below location.

<drive>:\Program Files\Microsoft SQL Server\90\Tools\Binn\SQLCMD.EXE

In the first look there is no change.But when we come to authoring sql files there can be changes.Main advantage is you can have scripting variables which will replace their values at run time both in the command mode and in the sql files.You can compare this to classic ASP or PHP programming which replaces the server script before sending to the browser.

Ok Lets see what is this scripting variable by looking at an example.

:setvar PersonName Joy
:setvar email joymon@gmail.com
INSERT INTO Person ([Name] ,[Id] ,[EMail])
     VALUES('$(PersonName)', newId(), '$(email)')
GO

PersonName & email are variables and their current values are Joy & joymon@gmail.com respectively.When we refer them using the $() they will get replaced with the values.So here the values got replaced between the single quotes ‘’ and the query run smoothly.
Another advantage is we can call another script file from this using the :r keyword.See the example below.

r: "c:\queries\SQLQuery2.sql"

If we set a variable in one file and that file is calling another file the variables will be available in the second sql script also.If we took the above example,the variable $(PersonName) can be used in SQLQuery2.sql as well.There are lot many advantages, if you explore the possibilities of SQLCMD.Refer the below links for more details.

http://msdn.microsoft.com/en-us/library/ms188714.aspx

http://www.sqlbook.com/SQL-Server/SQLCMD-command-line-utility-13.aspx
Running the sql files with SQLCMD variables in SSMS
You can even run the sql files with SQLCMD variables in SSMS.For that change the query mode to SQLCMD.See the below screenshot.