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
