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”
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”
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”
Oops when I said “dos” I meant “cmd”.