Share via

PowerShell: Control target output of Invoke-Sqlcmd including success Queries, Error, Warning, Debug , and PRINT statements

  • Article

Background

PowerShell gives us a powerfull tool to invoke queries using the cmdlet command "Invoke-Sqlcmd".

Invoke-Sqlcmd execute statements, which are supported by the SQL Server SQLCMD utility. This is where the name Invoke-Sqlcmd came from probably. This command allows us to execute Transact-SQL queries, XQuery statements, or sqlcmd commands. Using the parameter "-Query" we can execute inline queries, and using the parameter "-inputfile" we can specify a path to a file, which is used as the query input to this cmdlet.

There are a lot of tutorials online on how to use Invoke-Sqlcmd command, which usually focus on the built-in parameters and how to execute the command. One of these parameters is "Out-File", which allows us to send the output of the queries to a file. For example we can use the bellow command:

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database  "MyDatabaseName" | out-File -filepath "E:\MyResultFile.txt"

The command above will send the output of the success Queries to the file, but how can we control the output of Error messages, Warning messages, PRINT statements, and Debug messages?!?

What this post about?

In this article we will discuss some undocumented options of using Invoke-Sqlcmd. We will show how we can control the target of the output, not only of the queries but also errors and user-defined messages (PRINT statements), and how we can send these to a new or existing file, to the shell screen or to any other target.

From the PowerShell's users point-of-view, we will see how to control different types of returns informationm like: success Queries, Error messages, Warning messages, PRINT statements (Verbose), and Debug messages?

Let's first understand what different type of stream messages a command can return...

Credit! This article is based on a post Written by Ronen Ariely on his blog.

Streams in Windows PowerShell and in SQL Server

When we execute a PowerShell command the PowerShell Engine provides different types of messages, streaming in parallel to the client. PowerShell numbers the types of stream messages according to their type from 1 to 6: (1) success message, which is the output of the success command execution. (2) Error messages. (3) Warning messages. (4) Verbose messages. (5) Debug messages. (6) Information messages. We can use these numbers in order to control the returned output.

This is not so different from other applications, like SQL Server for example, which also provides these type of messages. The equivalent of success message is the result of the query which returned. The Error, Warning, Debug, and Information messages returned as errors in different levels. And the equivalent of Verbose messages is the output of PRINT statement.

Most of the client applications like the "SQL Server Management studio" or "SQL Operations studio", present these messages according to their type. For example the success result returns by default in a GRID structure in the "result window", the different levels of errors and the output of the PRINT statement return in a simple text format in the "message window".

PowerShell gives us the power to redirect each of these different types of messages to specific clients, by using the number of the type which we want to redirect to the client (the numbers of the types as mentioned above).

Redirect PowerShell output

PowerShell uses a redirection operator in order to control the output streaming. In the table below, you can see these operators and a short explanation:

> Send specified stream to a file.
>> Append specified stream to a file.
>&1 Redirects the specified stream to the Success stream

By default, the PowerShell redirect only the success stream, but we can explicitly control which type of message we want to redirect by adding the number before the operator. We can use the sign * in order to redirect all the types together.

For example:

  • "1>" will redirect the success message, which is what we will use to redirect the result of success QUERY in SQL Server
  • "4>" will redirect the Verbose message, which is exactly what we will use in order to control the output of the PRINT statement in SQL Server.

When we execute the script using "invoke-sqlcmd" command, we can control these different types of messages, and send them to different clients or to the same client.

let's go over some samples, and see how we can redirect the different types of messages to the external file, using the PowerShell redirection's operators...

Demo

Preparations - Before we start:

1. For the sake of the Demo please create a new table in the tempdb database by executing the following code (you can use SSMS or SOS for this task)

DROP TABLE  IF EXISTS T;
GO
CREATE TABLE  T(
ID INT,
ColName NVARCHAR(100),
TableName NVARCHAR(100),
LookUpTableName NVARCHAR(100),
LookUpColName NVARCHAR(100)
)
GO

INSERT T(ID,ColName,TableName,LookUpTableName,LookUpColName)
VALUES
(1,'Col1','Stage','student','RollNo'),
(1,'Col2','Stage','student','uid'),
(1,'Col3','Stage','Class','Cid'),
(1,'Col4','Stage','Class','Cuid'),
(1,'Col5','Stage','Hobby','Hid'),
(2,'Col1','Stage','student','RollNo'),-----extra rows
(2,'Col1','Hobby','student','RollNo')-----extra rows
GO

SELECT * FROM T
GO

2. create a new text file and named it: MyScriptFile.sql

* In this demo we store the file in at the path "E:\MyScriptFile.sql". You can replace this path according to your need in all the samples code.

3. Add the following code to the file and save it:

SELECT * FROM [tempdb].[dbo].[T];

PRINT cast(@@ROWCOUNT as  varchar)+' Rows are affected';

SELECT 1/0;

As you can notice my file includes three statements. The first one is a simple QUERY which returns SET of rows, The second statement is a PRINT, and the third Statement raise an ERROR.

DEMO 1: Send the output of QUERIES to external file (output of the PRINT statement is ignored)

In this demo, we will use the redirection operator ">" without specify the type of message which we want to redirect to the file. As explained above the default type is 1 which mean that insteadof using ">" we could also use "1>".

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database"MyDatabaseName" > "E:\MyResultFile.txt"

Result: This is the default behavior.

  • The output of the QUERY which executed without errors send to the file
  • The output of the ERROR massage send to the shell which executed the command
  • The output of the PRINT statement is ignored

Note! by default, the Verbose messages are not returned by the command invoke-sqlcmd

Demo 2: Using the parameter "-Verbose" in order to get the output of the PRINT statement

In this demo, we are using the same redirection as in the previous demo, but we add the parameter -Verbose which led the command invoke-sqlcmd to return the Verbose messages as well.

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database"MyDatabaseName" -Verbose > "E:\MyResultFile.txt"

Result:

  • The output of the QUERY which executed without errors send to the file
  • The output of the ERROR massage send to the shell which executed the command
  • The output of the PRINT statement send to the shell which executed the command

Demo 3: Send the output of the PRINT statement to the file

In this demo, we specify that we want to redirect type 4, which is the Verbose messages

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database"MyDatabaseName" -Verbose 4> "E:\MyResultFile.txt"

Result:

  • The output of the QUERY which executed without errors send to the shell which executed the command
  • The output of the ERROR massage send to the shell which executed the command
  • The output of the PRINT statement send to file

Demo 4: Send all types of messages to the file

In this demo we are using the operator "*>" which redirect all the messages to our file

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database"MyDatabaseName" -Verbose *> "E:\MyResultFile.txt"

Note! This is highly not recommended in production for most cases, since we do not get information that there was an issue in the execution and we might find the error too late!

Result:

  • The output of the QUERY which executed without errors send to the file
  • The output of the ERROR massage send to the file
  • The output of the PRINT statement send to the file

Demo 5: Sending only the output of the success QUERIES and the PRINT statements to the file

This is a bit more complex since PowerShell redirection does not have a built-in value which we can add to the redirect parameter in order to send several types. Using the parameter ">" we can only redirect a single type of message or all the messages.

The solution is to use the operator ">&1", which redirects the specified stream to the Success stream, meaning that we can redirect the verbose message to the Success stream, and next we can use the simple operator ">" in order to redirect the Success stream (which includes the verbose message) to the file.

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database  "MyDatabaseName" -Verbose 4>&1> "E:\MyResultFile.txt"

Result:

  • The output of the QUERY which executed without errors send to the file
  • The output of the ERROR massage send to the shell which executed the command
  • The output of the PRINT statement send to the file

Note! This is my recommendation for the original question and for most of your cases in production.

Demo 6: Redirect output of PRINT and the output of the success QUERIES to different files

Time to take it to the next level, and only the the sky's the limit for what we can do more

Ops... today when we all work in the Azure and the clouds are in our hands this phrase "the sky's the limit" lost his meaning :-)

(invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database  "MyDatabaseName" -verbose > "E:\MyResultFile01.txt") 4> "E:\MyResultFile02.txt"

Result:

  • The output of the QUERY which executed without errors send to the file MyResultFile01.txt
  • The output of the ERROR massage send to the shell which executed the command
  • The output of the PRINT statement send to the file MyResultFile02.txt

Demo 7: Append information to existing file

This is a simple use of the redirect operator ">>" instead of the operator ">" which create a new file (delete existing file). In fact this is so simple and there is nothing to discuss here, that is needed to add this demo at the beginning, but since we forgot to add it before we will add this demo here for now :-)

invoke-sqlcmd -inputfile "E:\MyScriptFile.sql"  -serverinstance ".\MyServerName"  -database"MyDatabaseName" >> "E:\MyResultFile.txt"

Note! The ability to append information to existing file is VERY POWERFUL and useful in production. For example we can use this option in order to split a long script file to multiple files and execute each one separately, but append all the data into the same result file.

Note! you can use the append operator ">>" instead of the operator ">" in all the samples above as needed.

This is a good point to move to summarize what we had in the post...

In conclusion

In this post, we explain in short what are Streams in Windows PowerShell and in SQL Server, and we showed the relation between output from SQL Server to the PowerShell when we use the PowerShell command Invoke-Sqlcmd in order to execute Transact-SQL statements. After a short description of PowerShell redirection parameters and explanation on how we redirect PowerShell output, we gave several samples on how we can redirect the output of Transact-SQL statement to external file, and how can we control the output of Invoke-Sqlcmd PowerShell command including the success Queries, Error messages, Warning messages, PRINT statements (Verbose), and Debug massages.

If you liked the article, if you have any comment or feedback, if you want to ask any followup questions, or if you just want to say "hi", then you can add your comment here bellow the article, you you can join us at the TechNet Wiki group on Facebook.

Credits

The original post was written by Ronen Ariely (aka PITUACH) and it is available in his blog, but the idea for this article was a very long discussion in the MSDN forum.

Resources and More to read