PowerShell features many one-line commands for working with SQL Server, one of which is Invoke-SqlCmd. This tool can be useful in many development contexts where we need to quickly execute scripts or test code and it helps to know some of the parameters we’ll often use. In addition, because we may want a custom script using some of the underlying .NET objects, we’ll look at an alternative where we will be able to create a custom PowerShell script that connects to SQL Server in order to run commands. The latter can be useful because one-line scripts have a tendency to change in future versions of PowerShell and working with the library directly can sometimes avoid this challenge.
In all these examples where we call Invoke-Sqlcmd, we are not executing saved scripts, but executing these scripts through PowerShell ISE without saving the script. PowerShell ISE is included in most versions of Windows along with the PowerShell command line. For the examples in this article, we are using PowerShell version 5.
When to Use Invoke-SqlCmd
When we consider one-line scripts, we want to consider where these tend to be the most helpful for us:
- When we need to run a test quickly, such as testing code execution outside SSMS (like executing code on TestServerOne that runs against TestServerTwo)
- When we’re running a standard script without custom execution, like a stored procedure that executes a transform of our ETL layer, and the execution matches our flow
- When we have no alternative that is as quick or effective for our needs
In some cases, we may want a custom timeout that exceeds the limit, or we may not want to allow user input for the connection string (or other details). These are examples where we may want a custom script. We also want to be careful about using more tools than we need to avoid development complexity. Using ETL as an example, if most of our ETL is T-SQL, we should be careful about introducing extra tools that add more work in troubleshooting. Invoke-SqlCmd can be a useful tool in some contexts where we’re already using PowerShell, or if we’re running steps through SQL Server Job Agent, where we can run PowerShell scripts.
Covering the Basics
When we connect to SQL Server, we will generally either use a trusted connection or a SQL Server authenticated user. If we try to log onto SQL Server, we see these as the first two options when connecting.
Authentication options with SQL Server
For running our first tests with Invoke-SqlCmd, we’ll connect with three one-line calls each labelled with Query and the number. The reason for this is to compare how we can run connections to our database. Our two queries connect with integrated security (note how credentials are not specified), which means the account running the script has access to the SQL Server and database. We also see that our first query doesn’t have verbose enabled, which means when the script runs, we get no output – and our T-SQL script solely prints out This is output. Our second call to Invoke-SqlCmd prints out the output. Our third call, prints the output and uses SQL Server Authentication (these are placeholder credentials – do not use).
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$SQLServer = "TestServerOne" $db3 = "TestDB3" $qcd = "PRINT 'This is output'" Write-Host "Query 1:" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd Write-Host "Query 2:" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Verbose Write-Host "Query 3:" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $qcd -Username "User" -Password "Password" -Verbose |
The output we get from running the above 3 one-liners in PowerShell ISE
From these three side-by-side examples we see two important basics of calling this function – how we specify our credentials and whether we want output. Regardless of whether we use integrated security or SQL Server authentication, our users must have permission to the objects they call, such as tables, views, stored procedures, etc. Likewise, verbose may help us see output of what’s happening as we’re calling Invoke-SqlCmd and without it, we may not get the confirmation we need.
Running CRUD Operations
From creating objects to running CRUD operations, we can use this one-line function to run many SQL commands. In the below code, we run five statements where we complete the following in each all: create a table, insert data, update data, delete data, and select some data. Our final output (shown in the image below this) reflects all our CRUD operations where we add data, update one value, remove another value and finally select the entire data set.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
$SQLServer = "TestServerOne" $db3 = "TestDB3" $droptable = "DROP TABLE invokeTable" $createtable = "CREATE TABLE invokeTable (Id TINYINT, IdData VARCHAR(5))" $insertdata = "INSERT INTO invokeTable VALUES (1,'A'), (2,'B'), (3,'C'), (4,'E'),(5,'F')" $updatedata = "UPDATE invokeTable SET IdData = 'D' WHERE Id = 4" $deletedata = "DELETE FROM invokeTable WHERE Id = 5" $selectdata = "SELECT Id, IdData FROM invokeTable" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $createtable Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $insertdata Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $updatedata Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $deletedata Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata |
From creation to all CRUD operations, we see our final output in the PowerShell script pane from PowerShell ISE
While we can see that we are able to run T-SQL directly in the script, we will more than likely use the inputfile parameter instead, as most T-SQL files involve multiple lines of code rather than being one-line or a few line calls. In the below two calls to Invoke-SqlCmd, we pass in a file using the inputfile parameter with a file that runs both an insert and an update together. After that call, we call our function again to return our data.
File: C:\Files\TSQL\run.sql
|
1 2 3 4 5 6 7 8 |
INSERT INTO invokeTable VALUES (5,'E') , (6,'F') , (7,'G') , (8,'H') UPDATE invokeTable SET IdData = IdData + '-' + CAST(Id AS VARCHAR(1)) |
Script run from PowerShell ISE:
|
1 2 3 4 5 6 |
$SQLServer = "TestServerOne" $db3 = "TestDB3" $selectdata = "SELECT Id, IdData FROM invokeTable" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -InputFile "C:\Files\TSQL\run.sql" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata |
The results from running our input file in PowerShell ISE’s script pane
In most cases with T-SQL from creating objects to running CRUD operations, we’ll use the input file parameter since most of our T-SQL will involve multiple lines of code and multiple operations. For an example, we may use files to run data operations on a regular basis, but change the files from time to time and in these cases, our input file would stay the same on each call.
Returning Data in Files
Sometimes we’ll use Invoke-SqlCmd to return a data set in a file that will be read by an application, such as Excel or Tableau reading a CSV file returned from a call to this function. Using our same table and data set that we’ve created, we’ll use our function to create a CSV file that we can read in applications that can work with CSV files, such as Excel. When we call our function, we can pipe the output – which is the result set to Out-File or Export-Csv to save a file of these data. Since we want a CSV file, we’ll use the Export-Csv function. We specify the delimiter as a comma in this case – though we could specify any custom delimiter here and pass in the command to avoid outputting type information, which would normally add type information to our header (we don’t want in our case). As we see in the below image, we have our CSV file saved to our reporting location.
|
1 2 3 4 5 |
$SQLServer = "TestServerOne" $db3 = "TestDB3" $selectdata = "SELECT Id, IdData FROM invokeTable" Invoke-Sqlcmd -ServerInstance $SQLServer -Database $db3 -Query $selectdata | Export-Csv "C:\files\reports\run.csv" -Delimiter "," -NoTypeInformation |
Our CSV file returns with the Export-CSV function that catches the output from the select
Alternative Script
Using Invoke-SqlCmd comes with a few drawbacks and we may prefer to use a custom script. One example of this is the parameter -QueryTimeout, which is the length of time a query must run before timing out. To avoid an error from returning, we must specify an integer between 1 and 65535 according to Microsoft. There may be situations in which we want the command timeout to be specified at 0. In a similar manner, we may want the connection string to be hardcoded with only commands allowed, which we can achieve through a custom function or wrapping a custom function around Invoke-SqlCmd.
In the below code, we create a custom function that will execute write-based or construct based commands, such as creating objects, inserting, deleting or updating data. This custom function restricts input to only accept a command and we also see that it uses integration security and does not specify a timeout for either the connection or command. We use this script to clean up our test objects by first removing the data (DML) and then dropping the object (DDL).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
Function Execute-SqlWriteConstruct { Param( [Parameter(Mandatory=$true)][string]$command ) Process { $gscn = New-Object System.Data.SqlClient.SqlConnection $gscn.ConnectionString = "Data source= TestServerOne;Initial Catalog= TestDB3;Integrated Security=true;Connection Timeout=0" $wrCmd = New-Object System.Data.SqlClient.SqlCommand $wrCmd.Connection = $gscn $wrCmd.CommandTimeout = 0 $wrCmd.CommandText = $command try { $gscn.Open() $wrCmd.ExecuteNonQuery() | Out-Null } catch [Exception] { Write-Warning $_.Exception.Message Write-Warning $command } finally { $gscn.Dispose() $wrCmd.Dispose() } } } $deletethendrop = "DELETE FROM invokeTable DROP TABLE invokeTable" Execute-SqlWriteConstruct -command $deletethendrop |
Should we create custom functions over using a built-in function? In addition to having full control over parameters including restricting some parameters, custom functions that use underlying .NET libraries seldom experience as much changes as built-in functions. If we choose to use built-in functions like Invoke-SqlCmd, we must be aware that these may change and any call to them must be reworked. With underlying .NET libraries, some of these may change if bugs or security holes are discovered, but we don’t see as many changes to these as we see with built-in PowerShell functions. The SqlClient namespace is a great example of this – I’ve been using this for years and while Microsoft has made it stronger over the years, the underlying structure matches. This isn’t to say that it won’t ever be deprecated, but functions built on top of it have changed, whereas it has experienced less change.
Conclusion
As we’ve seen, PowerShell’s Invoke-SqlCmd is a convenient tool in which we can create objects, read and write data to and from SQL Server (with direct or file input), and save queries to files without significant development work. While we’ve looked at a few examples that we’ll use frequently, this function has more capability than what’s covered. In the rare situations where we may need to write or use a custom function, we can use the .NET library to read and write to our database as well.
Table of contents
| Working with PowerShell’s Invoke-SqlCmd |
| Securely Working with Invoke-SqlCmd |
He has spent a decade working in FinTech, along with a few years in BioTech and Energy Tech.He hosts the West Texas SQL Server Users' Group, as well as teaches courses and writes articles on SQL Server, ETL, and PowerShell.
In his free time, he is a contributor to the decentralized financial industry.
View all posts by Timothy Smith
- Data Masking or Altering Behavioral Information - June 26, 2020
- Security Testing with extreme data volume ranges - June 19, 2020
- SQL Server performance tuning – RESOURCE_SEMAPHORE waits - June 16, 2020