sqlcmd not returning error

Question

I am running the following sqlcmd via power shell

$dump = sqlcmd -S $server -Q $sqlCommand -t $queryTimeout -b -h -1 -W

I trying the write the output to screen

$message = "Error while executing sql {0}, Error details {1}" -f "$sqlCommand","$dump" 
Write-Warning $message

but $dump is empty

• Matt: Are you expecting $dump to contain an error? $dump would only contain data send to the output stream. If its from the error stream you need to redirect that $dump = sqlcmd -S $server -Q $sqlCommand -t $queryTimeout -b -h -1 -W 2>&1... maybe
• Deadly-Bagel: SQLCMD is a weird creature. I would confirm by running the query in SQL and checking you do actually get a result, then mess with the switches. I tend to run full scripts so I use -i and give a filename, but I could only get it working with & (SQLCMD) -s localhost -x -i $file. Try removing all your switches, and if you get a result start adding them back.
• Shachaf.Gortler: I expect dump to contain the output of the sql file , as if i ran it in SQL management studio
• Shachaf.Gortler: @Matt the example you gave worked , thanks please make it an answer . What is 2>&1 used for ?

Answer 1

I am not familiar with sqlcmd.exe but from the description of your expectations I think that sqlcmd is sending information down the error stream. This is a seemingly odd but common practice and therefore occurrence.

Problem here is that the variable $dump will only collect information send to the output stream. What you can do is redirect the error stream to output stream with a redirector. For more information you can look at about_redirection

So using the following will accomplish that:

$dump = sqlcmd -S $server -Q $sqlCommand -t $queryTimeout -b -h -1 -W 2>&1

The linked document describes 2>&1 as

Sends errors (2) and success output (1) to the success output stream.

Now $dump should contain what you are looking for. Be careful though as it might contain more information than you expect.