Reading SQL Server data from Powershell using sqlcmd

Friday, May 8, 2020

I'm always looking for ways to read SQL Server data from Powershell scripts. Sometimes I start creating SqlConnections and SqlCommands the '.NET' way, sometimes I use a module I wrote for that purpose, but nothing is really one-line-like simple.

Today, I revisited an old script that uses sqlcmd to read a single value from SQL Server, like so:

$query = "SET NOCOUNT ON; SELECT TOP 1 SomeValue FROM SomeTable WHERE SomeCondition ORDER BY SomeTimeStamp"
[int]$SomeInt = sqlcmd -X -h-1 -S server -U username -P password -d database -Q $query

The options are:

-X: no startup scripts
-h-1: no headers
-S: server name
-U: user name
-P: password
-d: database
-Q: the query to execute

And to prevent the "(1 rows affected)" at the end we prefix the query with SET NOCOUNT ON;

For a single value this works great: sqlcmd will just spit out the desired value, no frills. But in this case I needed some structured data, as in:

$query = "SET NOCOUNT ON; SELECT * FROM SomeTable WHERE SomeCondition ORDER BY SomeTimeStamp"

Firstly, in this case we query all fields from the table and secondly, we query more than one row, so the single value trick doesn't work.

Lately, I have been using ConvertFrom-CSV a lot, so it occurred to me I might be able to parse the sqlcmd output as CSV. Since I want column headers, I omitted the -h-1 argument and got:

Computer        Count       Avg         Min         Max
--------------- ----------- ----------- ----------- -----------
231                      17          34          32          38
232                      17          27          22          30
233                      17          36          30          42

This is not CSV, but fixed length, so I added a -s ',' argument to sqlcmd. This sets a delimiter on the output (in this case a comma) which results in:

Computer       ,Count      ,Avg        ,Min        ,Max
---------------,-----------,-----------,-----------,-----------
231            ,         17,         34,         32,         38
232            ,         17,         27,         22,         30
233            ,         17,         36,         30,         42

This is parseable using ConvertFrom-CSV and it seems to work. The result is an Object[] where each element of the array is a PSCustomObject with properties Computer, Count, Avg, Min and Max. But there are two problems. On closer inspection it turns out that the Min property is not just called Min: its name has a trailing space! So we need to get rid of the spaces, too. The solution: the -W option. This produces:

Computer,Count,Avg,Min,Max
--------,-----,---,---,---
231,18,34,32,38
232,17,27,22,30
233,18,36,30,42

Almost there! The second problem is that we get not three but four elements in our array. The first element comes from the second line (the first line is parsed as column names), the one with all the dashes. Fortunately, we can skip the first element using Select-Object -Skip 1. So now we have:

$data = sqlcmd -X -S server -U username -P password -d database -Q $query -s ',' -W |
   ConvertFrom-CSV |
   Select-Object -Skip 1

The result is three objects:

Computer : 231
Count    : 18
Avg      : 34
Min      : 32
Max      : 38

Computer : 232
Count    : 18
Avg      : 27
Min      : 22
Max      : 31

Computer : 233
Count    : 18
Avg      : 36
Min      : 30
Max      : 42

Just one more niggle: one of the nasty things about the CSV format is its sensitivity to commas in data. So I'd rather change the delimiter to something rarer, e.g. the pipe symbol. For sqlcmd that's simple: we just change the -s option. Fortunately, ConvertFrom-CSV has a similar option: -Delimiter. So when we use

$data = sqlcmd -X -S server -U username -P password -d database -Q $query -s '|' -W |
   ConvertFrom-CSV -Delimiter '|' |
   Select-Object -Skip 1

we get the same result, only "comma proof". (I tried to be smart and use "`t" as the delimiter: the tab character. Alas, sqlcmd isn't fooled and complains the -s option should be specified - obviously, the tab character is stripped away.)

Bottom line? Use the following fragment to read SQL Server row data into nice Powershell custom objects. Bring your own server name, user name, password and database:

$data = sqlcmd -X -S server -U username -P password -d database -Q "SET NOCOUNT ON; $query" -s '|' -W |
   ConvertFrom-CSV -Delimiter '|' |
   Select-Object -Skip 1

Specify -s and -W to sqlcmd, set a delimiter on ConverFrom-CSV and skip the first object.

(OK, OK, it's not a one-liner, but it could be)