Best way to execute cmd from sql script
I want to generate some excel reports using vba scripts called from stored procedure. My team got almost the same BI solution, and previously they were using VBA script that opened excel file with macros inside.
Report generation process follows this path:
There’s SQL Stored procedure executed from weekly scheduled job, there we call another procedure which is preparing and calculating data, and then we open
script presented below to run excel.
Here’s how this script looks like:
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("E:PathToExcel.xlsm")
We call this script in such way:
EXEC master..xp_cmdshell 'C:WINDOWSSysWOW64WSCRIPT.EXE Z:PathToScript.vbs'
I got some problems with WSCRIPT.EXE, it’s hanging when opening reports, and when I was looking for solution I’ve read much about not using XP_CMDSHELL
. And the question is: Is there any way to avoid using XP_CMDSHELL
and to not to create report generation path from scratch? I’m a bit in hurry so I don’t have time to write new procedures etc.
Working with the sql server command line (sqlcmd)
You can run sqlcmd as commands. You can run scripts in command mode.
In the next example, we will show how to run a script using sqlcmd and show the results in another file.
We will first create a script file named columns.sql with the following sentences:
select * from adventureworks2021.information_schema.columns
In the cmd, run the following command to invoke sqlcmd:
sqlcmd -S DESKTOP-5K4TURFSQLEXPRESS -E -i c:sqlcolumns.sql -o c:sqlexit.txt
-i is used to specify the input. You specify the script file with the queries.
-o is used to show the results of the input in a file.
The exit.txt file will be created:
If we open the file, we will see the output results:
We will first create a script to back up the database named backup.sql:
In the cmd run the following command:
sqlcmd -S DESKTOP-5K4TURFSQLEXPRESS -E -i c:sqlbackup.sql -o
c:sqloutput.txt
The output will be similar to this one:
The commands will create a backup in a file named backup.sql in the c:sql folder:
You can work with variables in sqlcmd. The following example will set the variable DATABASENAME with the value adventureworks2021 and then we change the context to the database specified:
The result displayed is the following:
As you can see, SETVAR is used to specify the value of the variable. Then you need to use $() for the variable.
Another example is to set the variable CONTACTTYPEID to 3 and use it in the where clause to find a contact type ID according to the value of the variable:
The result displayed is the following:
You can list the tables of the database using the information_schema.tables view. We will first create a script named tables.sql. This script contains the tables and views:
Next, we will invoke sqlcmd to execute the script.
sqlcmd -E -i c:sqltables.sql -o c:sqloutput.txt -S DESKTOP-
5K4TURFSQLEXPRESS
The result displayed are the following in the output.txt file:
The following sentences will list the table names and the column names of a database in a script named columns.sql:
In the cmd run this command:
sqlcmd -E -i c:sqlcolumns.sql -o c:sqloutput.txt -S DESKTOP-
5K4TURFSQLEXPRESS
The result of the output.txt is the following:
You can check all the sqlcmd commands using this command:
Sqlcmd -?
This command will list all the commands available:
The following command will exit if it fails using the –b parameter:
sqlcmd -E -q “create table adventureworks” –b -S DESKTOP-
5K4TURFSQLEXPRESS
The command will exit if there is an error:
If there is an error, the error is displayed. However, according to the error level, you can stop this behavior by default using the -m option.
Here it is an example about this:
The following command shows an error message:
However, if you add the –m 16, the error will no longer be displayed because the error has the level of 15:
sqlcmd -E -q “create table adventureworks” -m 16 -S
DESKTOP-5K4TURFSQLEXPRESS
-m 16 will show only the errors higher than 16. As you can see the error message is no longer displayed
The following example will run a SQL script with one variable. The example will create a database specified by the user.
We will first create a script named createdb.sql with the following content:
Next, in the cmd we will run the database specifying the database name:
sqlcmd -E -v DATABASENAME=”Userinput” -i
c:sqlcreatedb.sql
The command will create a database named Userinput.
In sqlcmd you can run the sp_databases stored procedure:
And you will be able to see the database created:
Ввод запроса
Начните вводить оператор SQL в приглашении 1>. Вы можете использовать столько строк, сколько хотите для своего запроса, нажимая клавишу Enter после каждой строки. SQL Server не выполняет ваш запрос, пока не получит явную инструкцию.
SELECT *
FROM HumanResources.shift
Выполнение запроса
Когда вы будете готовы выполнить ваш запрос, введите команду GO в новой командной строке в SQLCMD и нажмите Enter . SQLCMD выполнит ваш запрос и отобразит результаты на экране.
Выход из sqlcmd
Когда вы будете готовы выйти из SQLCMD, введите команду EXIT в пустой командной строке, чтобы вернуться в командную строку Windows.
Открытие командной строки
Чтобы запустить SQLCMD, вы должны сначала открыть утилиту командной строки Windows. В Windows XP нажмите Пуск > Запустить , а затем введите CMD в текстовом поле, а затем нажмите ОК . В Windows Vista нажмите кнопку Windows , введите CMD в поле Поиск и нажмите Enter .
Вы должны увидеть командную строку Windows.
Подключение к базе данных
Когда у вас откроется командная строка, используйте утилиту SQLCMD для подключения к базе данных. В этом примере мы подключаемся к базе данных AdventureWorks2021, поэтому используем команду:
sqlcmd -d AdventureWorks2021
При этом используются учетные данные Windows по умолчанию для подключения к вашей базе данных. Вы также можете указать имя пользователя, используя флаг -U, и пароль, используя флаг -P. Например, вы можете подключиться к базе данных, используя имя пользователя «mike» и пароль «goirish», используя следующую командную строку:
sqlcmd -U майк -P goirish -d AdventureWorks2021