Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Deploying Scripts with SQLCMD

By David Poole,

I have had to deploy database applications in a variety of scenarios and very early on in my career I reached the conclusion that any means of automating the deployment process was a "Good Thing".

Manual deployment introduces a point of failure for even the most diligent DBA, particularly when that deployment has to take place in hours when most sane people are soundly asleep. Where I work at present downtime costs big money (a years salary in lost revenue for every minute) hence the early start.

This means that you would be undertaking a manual process under the following conditions: -

  • Under stress due to cost considerations
  • On 3 or 4 hours disturbed sleep
  • Severely decaffeinated

The SQLCMD and its predecessors OSQL and ISQL offer a means of automating such deployments. However SQL Management Studio offers the facility to use SQLCMD from within SQL scripts by using SQLCMD Mode.

SQLCMD Mode

SQLCMD Mode can be activated/deactivated from a menu option within SQL Management Studio as shown below.

Obtaining SQLCMD Mode

When SQLCMD Mode is engaged then the specific SQLCMD commands are shown on a grey background as shown below

Basic SQL CMD Statement

As you can see from the script above I have asked to connect to a database server called DBDevelopmentServer and run a query in the Adventureworks database.

Straight away we can see that it is possible to specify the server that a script is supposed to run on as part of the script.

This may sound like small beer but for me a typical deployment may involve tens of scripts all to be deployed to servers with remarkably similar names. Being able to state explicitly what server the scripts are to be run on gives a major advantage

  • An incorrect server name will show up in any peer review
  • The server name is "in-your-face"

User variables in SQLCMD Mode

The :setvar command is a useful addition in the SQLCMD arsenal.

We can refine our first sample script slightly to demonstrate its usage

Basic use of the SQLCMD :setvar command

By itself this does not look like a big deal but it demonstrates the following

  • Connections can be made on user variables
  • Databases can be specified in user variables

Running many files from a single INSTALL.SQL script

Ultimately we want to reach the point where we only have to run a single Install.SQL script to deploy an entire solution.

Fortunately SQLCMD mode has a suitable facility to allow this to happen. To demonstrate it I created a simple script called TestSQLCMD.SQL as follows

DECLARE
	@UserName SYSNAME ,
	@DeploymentTime CHAR(18),
	@DeploymentDB sysname,
	@CRLF CHAR(2)
SET	@CRLF = CHAR(13)+CHAR(10)
SET @UserName = SUSER_SNAME()+@CRLF
SET @DeploymentTime = CONVERT(CHAR(16),CURRENT_TIMESTAMP),120)+@CRLF
SET @DeploymentDB = DB_NAME()+@CRLF
PRINT '***************************************'
RAISERROR('DEPLOYMENT SERVER: %s%sDEPLOYMENT DB: %sDEPLOYMENT TIME:%sDEPLOYER: %s',10,1,@@SERVERNAME,@CRLF,@DeploymentDB,@DeploymentTime,@UserName)
PRINT 'TestSQLCMD.SQL IN VSS UNDER Solutions\SQLServerCentral\SQLCMD'
PRINT '***************************************'
GO
SET NOCOUNT,XACT_ABORT ON
INSERT INTO  Person.ContactType(
	[Name],
	ModifiedDate
) VALUES ( 
	/* Name - Name */ N'Dave',
	CURRENT_TIMESTAMP
) 
RAISERROR('%i record(s) deployed to Person.ContactType',10,1,@@ROWCOUNT)

Ignore the code before the INSERT statement, it is purely a standard header I use in all my scripts to be able to verify that a script has been run on the correct server.

However, note that there is no database mentioned or connection specified. In fact I deliberately disconnected this query and saved it in my local "My Documents" folder as follows

C:\Documents and Settings\David\My Documents\SQLServerCentral\SQLCMD\TestSQLCMD.SQL

Our original SQLCMD mode script can then be modified as follows

A simple INSTALL.SQL file

This demonstrates something incredibly important

  • We can control all our connections and the databases we use from a single file
  • Even if a release engineer downloads our project scripts to a completely different directory they can specify that directory in a variable. Control is still from a single file.
  • We can mix and match strings and variables with SQLCMD mode commands.

There is another consideration I should like to draw your attention to. In some cases I may want to run a single script on several servers or on several databases for a single deployment. Two specific examples come to mind.

  • Replication subscriptions where the @sync_type=N'none' where you have to create objects manually.
  • Databases that are patterns where a fix applied to one should be applied to all.

This method of scripting allows me to run that same script many times automatically.

Output and errors

If you are going to implement a single INSTALL.SQL script then you have to make sure that the scripts that are called from INSTALL.SQL are pretty much bomb proof. They should not produce errors and should be safe even if they are rerun by mistake. In short we have to raise our game when it comes to our scripting skills.

We have to be much more rigorous with error trapping and pre-emptive checks when handing installation scripts over to a 3rd party. Of course our output and errors will still be shown on the screen but it would be safer to ensure that the output goes to specific text files so the installation can be reviewed.

With this in mind we alter our original script one more time.

STDERR and STDOUT

Here you can see I have asked to put the errors and output into text files in the installation directory. The install.out file is shown below

Connecting to DBDevelopmentServer...
***************************************
DEPLOYMENT SERVER: DBDevelopmentServer
DEPLOYMENT DB: Adventureworks
DEPLOYMENT TIME:2009-02-14 16:02
DEPLOYER: DBDevelopmentServer\David
TestSQLCMD.SQL IN VSS UNDER Solutions\SQLServerCentral\SQLCMD
***************************************
1 record(s) deployed to Person.ContactType
ContactTypeID Name                                               ModifiedDate
------------- -------------------------------------------------- -----------------------
1             Accounting Manager                                 1998-06-01 00:00:00.000
2             Assistant Sales Agent                              1998-06-01 00:00:00.000
3             Assistant Sales Representative                     1998-06-01 00:00:00.000
4             Coordinator Foreign Markets                        1998-06-01 00:00:00.000
5             Export Administrator                               1998-06-01 00:00:00.000
6             International Marketing Manager                    1998-06-01 00:00:00.000
7             Marketing Assistant                                1998-06-01 00:00:00.000
8             Marketing Manager                                  1998-06-01 00:00:00.000
9             Marketing Representative                           1998-06-01 00:00:00.000
10            Order Administrator                                1998-06-01 00:00:00.000
11            Owner                                              1998-06-01 00:00:00.000
12            Owner/Marketing Assistant                          1998-06-01 00:00:00.000
13            Product Manager                                    1998-06-01 00:00:00.000
14            Purchasing Agent                                   1998-06-01 00:00:00.000
15            Purchasing Manager                                 1998-06-01 00:00:00.000
16            Regional Account Representative                    1998-06-01 00:00:00.000
17            Sales Agent                                        1998-06-01 00:00:00.000
18            Sales Associate                                    1998-06-01 00:00:00.000
19            Sales Manager                                      1998-06-01 00:00:00.000
20            Sales Representative                               1998-06-01 00:00:00.000
25            Sales Gurus                                        2007-12-14 11:56:08.950
36            Dave                                               2009-02-14 15:13:39.967
37            James                                              2009-02-14 15:14:40.780

Disconnecting connection from DBDevelopmentServer...


Conclusion

I first came across SQLCMD mode when working with Visual Studio Team Edition for database professionals. I was trawling through the various files that Visual Studio creates in order to form a deployment script and came across a file which simply listed every database object file prefixed by :r.

Digging around revealed that Visual Studio was just calling SQLCMD mode using an install.SQL file.

SQLCMD mode is useful for simple deployments where the release engineer will have access to SQL Management Studio. If Microsoft ever consider extending SQLCMD mode I would suggest that they look at error handling and some if…then…else constructs to aid automated deployments.

For this reason and given the time I prefer to use SQLCMD.EXE from a windows command file as this already has the ability to do such things.

Total article views: 11627 | Views in the last 30 days: 10
 
Related Articles
FORUM

SSAS Deployment through Deployment Script

SSAS Deployment through Deployment Script

BLOG

Scripting SSIS Package Deployments

Before I delve into the subject of scripting SSIS package deployments, I’m going to take a slight de...

FORUM

SQLCMD not returning control

running scripts against remote server via sqlcmd control not returned to calling script

FORUM

Analysis Services deployment wizard

Analysis Services deployment wizard

ARTICLE

Multi-Script, Multi-DB Deployments

A way to handle application releases involving multiple scripts and/or multiple databases.

Tags
sqlcmd    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones