Skip to content

SQL Server and the Command Line

1 December 2008

To execute a SQL script from the command line

From the command line, use SQLCMD. SQLCMD can be used with all kinds of parameters: -S servername to specify a server to connect to, -U username -P password to supply login in case Windows Authentication is not used, -i inputfile to specify a SQL script file to be executed etc. -? shows the entire list of parameters.

So for executing a script on the default server using Windows Authentication, the following command can be used:

sqlcmd -i myscript.sql

In case SQLCMD fails to connect to SQL Server, make sure that the SQL Server instance accepts Named Pipes and TCP/IP connections (in SQL Server Configuration Manager, under Network Configuration – Protocols, check that Named Pipes and TCP/IP are enabled) and also that Windows Firewall allows SQL Server through.

To execute a command line from a SQL script

From the script, use xp_cmdshell. For example, to list all files in the current folder (dir command), the syntax is

EXEC xp_cmdshell 'dir'

Standard output is copied into a temporary table containing a single nvarchar(255) column, each row corresponding to a stdout line. To store the result for further processing, a table can be used:

CREATE TABLE temp (Line NVARCHAR(255))
INSERT INTO temp EXEC xp_cmdshell 'dir'

Also, an execution result code from applications is returned by the command. To check the code, the value can be stored in an INT variable.

DECLARE @ResultCode INT
EXEC @ResultCode = xp_cmdshell 'dir'

The xp_cmdshell stored procedure is disabled by default. It can be enabled either by using the SQL Server Surface Area Configuration tool, or with the following commands:

EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

From → code complete

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: