Setting sqlcmd environment variables


In this post, I talked about how you can set your default editor for sqlcmd.

At the end of that post, I asked how to make that variable persist. To do that, you can create an environment variable. For example, you can create an environment variable called sqlcmdeditor, and give it the value of your favorite editor.

This works, but if you define a lot of sqlcmd variables, you will have quite a few to manage. That is not an elegant solution. Below is a list of sqlcmd predefined variables, most of which you can set to a value you like. As you can see, creating and maintaining them can be messy:

SQLCMDCOLSEP = ” ”
SQLCMDCOLWIDTH = “0”
SQLCMDDBNAME = “”
SQLCMDEDITOR = “gvim”
SQLCMDERRORLEVEL = “0”
SQLCMDHEADERS = “0”
SQLCMDINI = “c:\initial.sql”
SQLCMDLOGINTIMEOUT = “8”
SQLCMDMAXFIXEDTYPEWIDTH = “0”
SQLCMDMAXVARTYPEWIDTH = “256”
SQLCMDPACKETSIZE = “4096”
SQLCMDSERVER = “MyServer”
SQLCMDSTATTIMEOUT = “0”
SQLCMDUSER = “”
SQLCMDWORKSTATION = “MyWorkstation”

Instead, you can simply create one environment variable for sqlcmdini. I have discussed sqlcmdini in this post. Within sqlcmd initialization file, you can set values for the rest of sqlcmd predefined variables. As sqlcmd is invoked, the initialization will be executed, and the rest of variable values can be set there accordingly. I think this is a much simpler and elegant solution. Below is an example of my initialization file. I set sqlcmdeditor and sqlcmdmaxfixedtypewidth variables in this sample, but you can set others to suit your needs:

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
:setvar sqlcmdeditor “gvim”


3 responses to “Setting sqlcmd environment variables”

  1. Thanks Haidong,

    Thought to leave comment as your post is still no1 ranked all these years later! ie google search “sqlcmd gvim”

    Dealing with spaces in m$ land as follows:
    From dos:
    set SQLCMDEDITOR=C:\”Documents and Settings”\garyt\”Program Files”\Vim\vim73\gvim.exe
    From sqlcmd:
    :setvar SQLCMDEDITOR “C:\””Documents and Settings””\garyt\””Program Files””\Vim\vim73\gvim.exe”

  2. Hilarious!

    set SQLCMDINI=C:\Documents and Settings\garyt\work\sql\sqlcmdInit.sql
    No quotes required for SQLCMDINI as with quotes it errors as follows:
    C:\Documents and Settings\garyt\work\sql>sqlcmd -E -S server
    Sqlcmd: Error: The environment variable: ‘SQLCMDINI’ has invalid value: ‘C:\”Documents and Settings”\garyt\work\sql\sqlcmdInit.sql’.

    From within sqlcmd we see these scripting variables assigned as follows:
    1> :listvar
    SQLCMDEDITOR = “C:\”Documents and Settings”\garyt\”Program Files”\Vim\vim73\gvim.exe”
    SQLCMDINI = “C:\Documents and Settings\garyt\work\sql\sqlcmdInit.sql”

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.