Many sqlcmd options can be controlled in a script by using the setvar command. In the following example, the script test.sql is created in which the SQLCMDLOGINTIMEOUT variable is set to 60 seconds and another scripting variable, server, is set to testserver. The following code is in test.sql.

:setvar SQLCMDLOGINTIMEOUT 60

:setvar server "testserver"

:connect $(server) -l $(SQLCMDLOGINTIMEOUT)

USE AdventureWorks;

SELECT FirstName, LastName

FROM Person.Contact;

The script is then called by using sqlcmd:

sqlcmd -i c:\test.sql

The following example shows how to set a scripting variable interactively by using the setvar command.

sqlcmd

:setvar MYDATABASE AdventureWorks

USE $(MYDATABASE);

GO

Here is the result set.

Changed database context to 'AdventureWorks'

1>

In the following example, four environment variables are set and then called from sqlcmd.

C:\>SET tablename=Person.Contact

C:\>SET col1=FirstName

C:\>SET col2=LastName

C:\>SET title=Ms.

C:\>sqlcmd -d AdventureWorks

1> SELECT TOP 5 $(col1) + ' ' + $(col2) AS Name

2> FROM $(tablename)

3> WHERE Title ='$(title)'

4> GO

In the following example the user-level environmental variable %Temp% is set at the command prompt and passed to the sqlcmd input file. To obtain the user-level environment variable, in Control Panel, double-click System. Click the Advance tab, and then click Environment Variables.

The following code is in the input file c:\testscript.txt:

:OUT $(MyTempDirectory)

USE AdventureWorks;

SELECT FirstName

FROM AdventureWorks.Person.Contact

WHERE ContactID < 5;

This following code is entered at the command prompt:

C:\ >SET MyTempDirectory=%Temp%\output.txt

C:\ >sqlcmd -i C:\testscript.txt

The following result is sent to the output file C:\Documents and Settings\<user>\Local Settings\Temp\output.txt.

Changed database context to 'AdventureWorks'.

FirstName

--------------------------------------------------

Gustavo

Catherine

Kim

Humberto

(4 rows affected)

A sqlcmd startup script is executed when sqlcmd is started. The following example sets the environment variable SQLCMDINI. This is the contents of init.sql.

SET NOCOUNT ON

GO

DECLARE @nt_username nvarchar(128)

SET @nt_username = (SELECT rtrim(convert(nvarchar(128), nt_username))

FROM sys.dm_exec_sessions WHERE spid = @@SPID)

SELECT @nt_username + ' is connected to ' +

rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('servername'))) +

' (' +

rtrim(CONVERT(nvarchar(20), SERVERPROPERTY('productversion'))) +

')'

:setvar SQLCMDMAXFIXEDTYPEWIDTH 100

SET NOCOUNT OFF

GO

:setvar SQLCMDMAXFIXEDTYPEWIDTH

This calls the init.sql file when sqlcmd is started.

C:\> SET sqlcmdini=c:\init.sql

>1 Sqlcmd

This is the output.

>1 < user > is connected to < server > (9.00.2047.00)

Note   The -X option disables the startup script feature.

The following example shows working with data in the form of a sqlcmd variable.

USE AdventureWorks;

CREATE TABLE AdventureWorks.dbo.VariableTest

(

Col1 nvarchar(50)

);

GO

Insert one row into Col1 of dbo.VariableTest that contains the value $(tablename).

INSERT INTO AdventureWorks.dbo.VariableTest(Col1)

VALUES('$(tablename)');

GO

At the sqlcmd prompt, when no variable is set equal to $(tablename), the following statements return the row.

C:\> sqlcmd

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';

>2 GO

>3 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';

>4 GO

Here is the result set.

>1 Col1

>2 ------------------

>3 $(tablename)

>4

>5 (1 rows affected)

Given the variable MyVar is set to $(tablename).

>6 :setvar MyVar $(tablename)

These statements return the row and also return the message "'tablename' scripting variable not defined."

>6 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(tablename)';

>7 GO

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(tablename)';

>2 GO

These statements return the row.

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = '$(MyVar)';

>2 GO

>1 SELECT Col1 FROM dbo.VariableTest WHERE Col1 = N'$(MyVar)';

>2 GO