I read an interesting question today. Someone wanted to be able to run the same stored procedure multiple times, at the same time, but with different parameters each time. One way to do it would be to open multiple query windows, and run them all as close to at the same time as possible. But with more than a few queries running at a time that gets to be a pain really fast. The easiest way to do this is to use code of some type. T-SQL isn’t going to work because we need these to run asynchronusly and not synchronously (all at the same time not one after the other) and T-SQL doesn’t have that option. You could create a bunch of agent jobs but again, huge pain when you get past a small number of queries at once.

Powershell is the code of choice these days so I figured I would give that a shot. I did a post a little while back about creating a bunch of SQL connections at the same time. That time it was the same command running over and over again. This time we want a different command each time. Close though. With just a few modifications I had what was needed.

T-SQL to set up a test stored procedure

CREATE TABLE LogMe (String varchar(50));
GO
CREATE PROCEDURE sp_LogMe (@String varchar(50)) AS
BEGIN
INSERT INTO LogMe VALUES (@String);
END;

Powershell to run the stored procedure multiple times at the same time

#This script creates a number of connections (one per entry in 
#the $Commands array) to a SQL Server instance ($Server) and database ($DBName)
#Set Initial collections and objects    
$Server= "(local)\sql2016cs" ; #Server to connect to
$DBName = "Test" ; #Database to connect to
#The individual commands you want to run. In this case the same SP run
#with a different parameter each time.
$Commands = @()
$Commands += "EXEC sp_LogMe 'a'"
$Commands += "EXEC sp_LogMe 'b'"
 
#Loop through commands array, create script block for establishing SMO connection/query
#Start-Job for each script block
foreach ($sql in $Commands ) {
 
#All of that extra information after "Smo" tells it to load just v13 
#(for when you have multiple versions of SQL installed.)  Note: V13 is 2016.
 $cmdstr =@"
`Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=$(13).0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
`[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
`$SqlConn = New-Object Microsoft.SqlServer.Management.Smo.Server ("$Server")
`$SqlConn.Databases["$DBName"].ExecuteNonQuery("$sql")
"@
 
#Uncomment the next like to print the command string for debugging
# $cmdstr
#Execute script block in jobs to run the command asyncronously
$cmd = [ScriptBlock]::Create($cmdstr)
Start-Job -ScriptBlock $cmd
}

Filed under: Microsoft SQL Server, Powershell, SQLServerPedia Syndication Tagged: microsoft sql server, Powershell