One of the DBAs at our company recently wanted to execute a stored procedure to get data from some of our databases. We have a number of databases that are used by different customers, all of which are the same in terms of the schema and objects. The data is different, so when we call a stored procedure in each database, we get the results specific to that customer.

This article will showcase a short solution I put together in PowerShell to allow the DBA to quickly execute this stored procedure against multiple databases without having to connect to each in SSMS. I do this in PowerShell since saving the results is a little easier and more flexible than just using SQLCMD or SSMS. We can run this with one click and get updated results, or we can schedule this with any tool that can run a command line program.

The Setup

I can't show our company system, so I'll mock up a database and show how to connect to SQL Server, call a stored procedure, and then examine the results returned.

For this scenario, I'll create a procedure called CustomerSales in the database that I want to call from PowerShell. The details of the stored procedure do not matter, but we do want to get some of the data back from the procedure, but not all of it. This is where PowerShell makes this easy.

For the sake of this demonstration, let's create a table, insert some data, and build the stored procedure. I have the code below that I executed on my machine at home.

create table SalesOrder
( SaleID int,
  LineNumber int,
  SaleDate datetime,
  CustomerID int,
  ProductID int,
  Qty int,
  Price numeric(10,4),
  LineTotal numeric(10,4)
)
go
insert SalesOrder ( SaleID, LineNumber, SaleDate, CustomerID, ProductID, Qty, Price, LineTotal)
 values 
  ( 10, 1, '6/11/2020', 1, 50, 2, 100, 200),
  ( 10, 2, '6/11/2020', 1, 51, 20, 50, 1000),
  ( 20, 1, '6/15/2020', 4, 52, 50, 10, 500),
  ( 30, 1, '6/15/2020', 6, 53, 10, 60, 600),
  ( 30, 2, '6/15/2020', 6, 54, 2, 60, 12),
  ( 30, 3, '6/15/2020', 6, 55, 100, 50, 5000),
  ( 40, 1, '6/19/2020', 7, 50, 20, 100, 2000),
  ( 50, 1, '6/25/2020', 1, 50, 40, 100, 4000),
  ( 50, 2, '6/25/2020', 1, 57, 40, 25, 1000),
  ( 50, 3, '6/25/2020', 1, 58, 80, 50, 4000)
go
create procedure CustomerSales
as
begin
   select CustomerID,
          sum(qty) as totalunits,
  avg(price) as averageprice,
          sum(Linetotal) as totalsale
    from SalesOrder
group by CustomerID
end

This isn't a useful proc, but it will allow me to get some data back from PowerShell. If I execute the proc, I see this:

CustomerIDtotalunits   averagepricetotalsale
1        182     65.000000        10200.0000
4        50     10.000000        500.0000
6        112     56.666666        5720.0000
7        20     100.000000        2000.0000

I'll also execute the same script with some data changes in another database, just to have a second copy of things.

Now that I have a proc, how can I call this from PowerShell?

Invoke-SqlCmd

When PowerShell first came out, connecting to SQL Server was a hassle. In fact, it remained a pain until the SqlServer module was released in the PowerShell Gallery. It can be a pain to set up, so I'll do another article on that, but for now, I'll assume you have this module installed.

One of the cmdlets in his module is the Invoke-SqlCmd, which allows you to execute a batch against a SQL Server, just as if you were connected to SQL Server.

I can do this directly by typing in a few parameter values. I'll describe them here:

• ServerInstance - The SQL Server instance  to connect to
• Database - The database in which to run the command. If you don't include this, the query will run in your default database.
• Query - The batch commands to run.

For my simple procedure, I can run a powershell cmdlet and get results on the screen, as you see here.

This uses my Windows account to connect, but I could have included the -UserName and -Password parameters if I wanted.

As you can see, the results are formatted on the screen as though they are in a table. However, an object is really returned, and I can use that to capture results and display them selectively, or even write them to a file.

As an example, this result set has 4 fields. What if I just want the CustomerID and the total sale? In that case, I can assign the results to a variable, and then I can output data from that variable. For example, here is a short script:

$results = Invoke-Sqlcmd -ServerInstance localhost -Database db2 -Query "CustomerSales"
foreach ($sale in $results) {Write-Host("Customer: " + $sale.CustomerID + ", TotalSale:$" +$sale.totalsale)}

This gives me the results on the screen like this:

Customer: 1, TotalSale:$10200.0000
Customer: 4, TotalSale:$500.0000
Customer: 6, TotalSale:$5720.0000
Customer: 7, TotalSale:$2000.0000

If I wanted to output these to a file, I could use Select-Object to pick certain fields, and then the Export-Csv cmdlet to write this to a file. I string this together, passing in my $results variable. like this:

 $results | Select-Object CustomerID, totalsale | Export-Csv -Path "sales.csv" -NoTypeInformation

If I open up the file, sales.csv, I'll see this:

If I skipped the entire Select-Object part of the script, I'd run this:

$results | Export-Csv -Path "sales2.csv" -NoTypeInformation

and see this in the CSV.

I can also change my first Invoke-SqlCmd call to execute against the other database, and I'll see these results on the screen:

Summary

This is a short article that shows how you can use PowerShell to connect to SQL Server, run a query and get results, and write them to a file. This is a very basic look at this process, and if you decide to do something similar, be sure that you use variables for most of the values I passed in as parameters so that you can script this to work in loops and against multiple systems.