SQL Script: Passing Parameters to SQL script with Batch File and PowerShell

Article
1/17/2024

Abstract

The multiple ways of passing parameters to SQL file or Query using sqlcmd/Invoke-sqlcmd(PoSH) is explained in this article. The various ways of passing parameters to batch file, looping construct are explained with an example. This article also talks about the power of PoSH and how easy to derive the solution using PoSH.

Problem Statement

Pass input parameters such as SQL Server, Database name and Where Clause parameters to SQL Script or to a file and generate an output CSV file on a given output path using sqlcmd with bat scripting and invoke-sqlcmd(PoSH) cmdlet.

Solution

Dynamically passing parameters to SQL Script during run time, over a command prompt using command line utility SQLCMD or using PowerShell is described in this article.

Let's take an example of the below SQL query. The following SQL to be executed on Remote Server

USE CM_PS1
GO
SELECT B.* from  v_CITargetedMachines ctm a 
inner join v_r_system b  on  a.resourceid=b.resourceid 
where  b.CollectionID in ('PS1000FC','PS100131','PS1000FD')

Looking at the above SQL, One can easily identify the numbers of input parameters. The three parameters are mandatory and the fourth is going to be an optional one.

The four parameters are listed below

%1=Server - Remote Server
%2=Database - Database context under the SQL has to be executed
%3=Where clause - filter for the resultset
%4=Output Path - the path in which CSV file to be generated

The SQL File

The below SQL script has two variables $database and $ Input variable of Where clause. The values will be passed during runtime. The sqlcmd utility accepts inputs from a batch file. The batch files simply accept a list of arguments from cmd prompt and allow the program to function based on the order of input parameter

USE $(database)
GO
SELECT B.* from  v_CITargetedMachines ctm a 
inner join v_r_system b  on  a.resourceid=b.resourceid 
where  b.CollectionID in ($(input))

The batch sccm.bat file looks like below

 

      sqlcmd -S %1 -i \\papers.com\hq\pub\DOCUMENTATION\SCCM\SCCM.sql -v dbname=%2 input=%3 -o %4 -E

-S ->This option can be used to specify to which SQL Server Database Engine instance SQLCMD should connect. If a server instance name is not specified, SQLCMD checks the value of the SQLCMDSERVER environment variable. If the environment variable is not defined, it tries to connect to the default instance of SQL Server running on the same computer.

-E ->This option instructs the tool to connect to SQL Server Database Engine using Windows Integrated Security

-o ->This option enables you to specify an output file for SQLCMD session execution.

-v ->"<variable value>" ->This option enables you to define one or more variables right on the command line before running a script.

-i ->nput_file[,file2...] ->This option enables you to supply one or several script files for immediate execution

Call sccm.bat

The bat file accepts four input parameters. The position of the input is important. The order in which the parameters are fed will have an impact on the functioning of the script

 

      C:\>sccm.cmd  HQSCCMP    10 CM_PS1  "'PS1000FC','PS100131','PS1000FD'" \\apers\hq\OUTPUT.CSV

%1-> HQSCCMP10%2->CM_PS1%3->"'PS1000FC','PS100131','PS1000FD'"%4->\\appletonpapers\hq\OUTPUT.CSV

Flow Diagram

Passing parameter(s) to Batch file

Example 1 -Demonstration the use of the position locator of Input parameter

For example, consider a batch file named "sql.BAT" that requires three input parameters

MD %1

MD %2

MD %3

If we invoke sql.BAT as:

C:\>SQL.BAT FILE``1 FILE``2 FILE``3

The internal call is going to be like below

MD FILE1

MD FILE2

MD FILE3

Example 2 - Looping through Input file parameter

The "FOR" construct offers looping capabilities for batch files. Specifically, it allows sequencing through a list of parameters.

For example, consider a SQL file named "sqlcmd.sql" has few sets of SQL to identify the recovery model of the databases.

uSE $(dbname)
Go
SET NOCOUNT ON
GO
select name,recovery_model_desc,compatibility_level,create_date from  sys.databases where  recovery_model_desc in  ($(input))

The above script has to execute across multiple servers and the result is stored in the shared path

The callscript.cmd file looks like below

for /f %%j in (f:\PowerSQL\server.txt) do sqlcmd -S %%j -E -h -1 -W  -i f:\PowerSQL\sqlcmd.sql -v dbname=%1 input=%2  >> %3

Command line call

F:\PowerSQL>callscript``1``.cmd MASTER ``"'simple','full'" \\appers.com\hq\pub\documentation\OUTPUT``1``.CSV

The above example loops through all servers, execute the sqlcmd.sql SQL file and appends the output to the shared path.

Example 3 - xp_cmdhshell and Looping construct

The xp_cmdshell option is a server configuration option that enables system administrators to control whether the xp_cmdshell extended stored procedure can be executed on a system.Nowadays there is not much need for this particular command. This is the reason have given a lot of PoSH examples below. We would like to demonstrate the difference and Power of PoSH. However, there was a time when PowerShell did not exist and we had to do lots of tasks with the help of the command shell.

Master..xp_cmdshell 'for /f %j in (f:\PowerSQL\server.txt) do sqlcmd -S %j -i f:\PowerSQL\sqlcmd3.sql -E >>f:\PowerSQL\Output.txt'

How to pass parameters to SQL file using PoSH

Invoke-Sqlcmd is a SQL Server cmdlet that runs SQL script file or query and commands which are supported by the sqlcmd utility. The PowerShell allows importing .NET namespaces and libraries and, in effect, you can write C# code to solve any problem you may have. The use invoke-sqlcmd cmdlet libraries to access SQL data is explained below. The SQLPS module provides the Invoke-Sqlcmd cmdlet, which makes accessing data fairly easy. When you work with large data set, we would recommend or prefer loading/adding snap-ins and invoke-sqlcmd rather working to ADO.NET libraries. We feel significant performance improvement when you use invoke cmdlet directly with the database.

 #Passing Parameters to SQL Query
$database="'tempdb','model'"
$strSQL= "select name,state,state_desc from sys.databases where name in($database)"
$ServerList1 = Invoke-Sqlcmd -ServerInstance hqdbsp18 -Query $strSQL
$ServerList1|Format-Table -AutoSize

Passing Parameter(s) to SQL File Using Invoke-sqlcmd

#Passing single parameter to SQL file
  
$DBServer = "hqdbsp18"
$DBScriptFile = "e:\Output\sqlcmd1.sql"  $sqlParameters = @(  "database='tempdb','model'"  )

Invoke-Sqlcmd -InputFile $DBScriptFile -ServerInstance $DBServer -Variable $sqlParameters -Database ``'master'

#Passing parameters to SQL file

$DBServer = ``"hqdbsp18"

$DBScriptFile = ``"e:\Output\sqlcmd2.sql"

$sqlParameters =@(``"database='tempdb','msdb'"``, ``"statedesc='online'"``)

Invoke-Sqlcmd -InputFile $DBScriptFile -ServerInstance $DBServer -Variable $sqlParameters -Database ``'master'

Error: Error while executing invoke-sqlcmd with parameters

"Invoke-Sqlcmd : Object reference not set to an instance of an object."

WorkAround:

The issue is with the input parameters and eventually found out this was a bug. Workaround to the solution is to remove the space around '='

$sqlParameters = @("database='tempdb','model'")

Around the parameter "database", space as to be removed.

Error Message:

Invoke-Sqlcmd : Object reference not set to an instance of an object.

At line:40 char:1

+ Invoke-Sqlcmd -InputFile $DBScriptFile -ServerInstance $DBServer -Variable $sqlP ...

+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

+ CategoryInfo : InvalidResult: (hqdbsp18:PSObject) [Invoke-Sqlcmd], NullReferenceException

+ FullyQualifiedErrorId : ExecutionFailed,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

Comparing Invoke-Sqlcmd and the sqlcmd Utility

Invoke-Sqlcmd can be used to run many of the scripts that can be run using the sqlcmd utility. However, Invoke-Sqlcmd runs in a Windows PowerShell environment which is different than the command prompt environment that sqlcmd is running. The behavior of Invoke-Sqlcmd has been modified to work in a Windows PowerShell environment.

Not all of the sqlcmd commands are implemented in Invoke-Sqlcmd. Commands that are not implemented include the following: :!!, :connect, :error, :out, :ed, :list, :listvar, :reset, :perftrace, and :serverlist.

Invoke-Sqlcmd does not initialize the sqlcmd environment or scripting variables such as SQLCMDDBNAME or SQLCMDWORKSTATION.

Invoke-Sqlcmd does not display messages, such as the output of PRINT statements unless you specify the Windows PowerShell -Verbose common parameter. For example:

Invoke-Sqlcmd -Query "PRINT N'abc';" -Verbose

Not all of the sqlcmd parameters are needed in a PowerShell environment. For example, Windows PowerShell formats all output from cmdlets, so the sqlcmd parameters specifying formatting options are not implemented in Invoke-Sqlcmd.

Conclusion

The use of sqlcmd and invoke-sqlcmd is mainly focused on outputs
The invoke-sqlcmd provides a new perspective for various DB automation as in PoSH everything is an object
Invoke-sqlcmd - easy to integrate output with other script but whereas sqlcmd requires a lot of T-SQL expertise to generate an output and integrate with other scripts

References

Share via