By now you probably can tell that I am a big fan of sqlcmd;) Here is one more nugget on sqlcmd that I can throw at you:

You can set a variable so that sqlcmd would run a query whenever you launch it from the command line interactively. For example, you can let it run a query and return some useful information like version number, edition level, and patch level. All these are great to know for a DBA. Here are steps to set it up:

1. Creat the script file. Let’s call it c:\work\scripts\sql\Initialization.sql. For example, you can put statements below, customize it to suit your needs:
–Begin script
set nocount on
go

print ‘You are connected to ‘ + rtrim(CONVERT(char(20), SERVERPROPERTY(‘servername’))) + ‘ (‘ + rtrim(CONVERT(char(20), SERVERPROPERTY(‘productversion’))) + ‘)’ + ‘ ‘ + rtrim(CONVERT(char(30), SERVERPROPERTY(‘Edition’))) + ‘ ‘ + rtrim(CONVERT(char(20), SERVERPROPERTY(‘ProductLevel’))) + char(10)

:setvar SQLCMDMAXFIXEDTYPEWIDTH 20

set nocount off
go

:setvar SQLCMDMAXFIXEDTYPEWIDTH
–End script

2. In DOS prompt, type:
set sqlcmdini=c:\work\scripts\sql\Initialization.sql

3. You are all set. Here are some sample results:

C:\Documents and Settings\Haidong>sqlcmd
You are connected to MARIALAPTOP (8.00.194) Enterprise Evaluation Edition RTM

1> exit

C:\Documents and Settings\Haidong>sqlcmd -S.\ssistest
You are connected to MARIALAPTOP\SSISTEST (9.00.1399.06) Standard Edition RTM

1> exit

Please share with me if you have clever script that can display additional information that is useful by commenting or email. Thanks and enjoy:)

PS. I noticed that when you restart your computer, sqlcmdini info is lost. You need to reset it again. Microsoft, is it possible to fix this in SP1?