A key piece of Yukon, SQLCMD
Its been a while since I blogged anything on Yukon, Christa has just posted the Data Track slide decks from TechEd so I will use one of my demos from TechEd.
SQLCMD is a key new piece of technology in Yukon its designed to replace osql but also to add a range of new functionlality. As part of the customer research for Yukon we went out and studied a bunch of customers in their environment and got to see and get copies of their scripts for doing operational management of SQL Server. From a commandline perspective many customers used a combination of osql, batch files and VB Script. When we started the design for SQLCMD we wanted to add enough batch functionality to dramatically reduce the need for batch file editing. We did this by adding SQLCMD extensions to T-SQL, these only work on the client side so these won't work in procs or in scripts you call through ADO, for this release.
Lets walk through a simple example of doing a backup, but make it more flexible than today.
BACKUP DATABASE $(db) TO DISK = "$(path)\$(db).bak"
This script backs up a database, defined by variable db, to the path defined by variable path with a name defined by variable db. Now this is a simple example of a backup but it shows how it can be parameterised. Now we have our script, how do we define the variables? There are a couple of ways, option 1 is to pass them on the comandline:
SQLCMD -ic:\dev\src\yukon\scripts\backup.sql -vdb="pubs" path="c:\data"
This will run the above script and define the needed variables. Lets take a look at the second way to declare the variables and also add some more to the script above. Consider the following SQLCMD script:
:connect demosvr\inst1
:setvar db pubs
:setvar path C:\data
:r "c:\dev\src\yukon\scripts\backup.sql"
GO
:connect demosvr\inst2
:setvar db northwind
:setvar path C:\data
:r "c:\dev\src\yukon\scripts\backup.sql"
GO
This sample connects to inst1, sets the variables then calls the backup script from above, then it does the same thing again for inst2. So this sample introduces a couple of new things, the first of which is the ability to connect to different servers inside scripts. The next is the declaration of sqlcmd variables inside sql scripts and finally we can see that the scope of those variables can cross script boundaries.
Hopefully Michiel who is the PM for SQLCMD will start blogging and then we can get some more samples as there is a ton more including some very slick XML handling. If not I will dig out some more samples.