en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP 


 


Microsoft® Community Contributor 


Microsoft® Community Contributor


Read first, before you you use the blog

Maximize
ספט25

Written by: ronen ariely
25/09/2018 08:24 RssIcon

What this post about?

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, which is where the name Invoke-Sqlcmd came from probably. It allows us to execute Transact-SQL or XQuery statements, or sqlcmd commands. Using the parameter "-Query" we can execute inline queries, and using the parameter "-inputfile" we can specifies 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 paramatres 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, but how can we control the output of PRINT for example or the output of ERROR massage?!? 

Today I want to discuss some undocumented options of using Invoke-Sqlcmd. In this post I 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 file, to the shell screen or to any other target.

Background

Several days ago a user named Mike111111111, raise a question at the MSDN forum, which led to a nice discussion. 

The original first requirement was to use PowerShell commands in order to execute T-SQL queries from a file, and send the result of the queries to a new file.

This is a simple requirement, which can be achieved by using the "Out-File" Cmdlet command. For example:

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

With that being said the original question had a second requirement: the OP wanted to add to result file information regarding the number of rows affected.

In order to return the number of rows affected by the last statement, we can use @@ROWCOUNT which returns the number of rows affected by the last statement.

The first response the user got was to use the statement PRINT in order to add this information to the result file and redirect the output from PowerShell to the file using "Streams Redirection" (I will explain what this mean in a second...).

This idea of return the result of PRINT statement and the discussion about different options led me to write this post

So... followup the discussion in the forum let me rephrase a new question: 
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?

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

Streams in Windows PowerShell and in SQL Server

When we execute a powershell command the Powershell Engine provides different type 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 massages. (6) Information massages. 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 type of messages to specific client, by using the number of the type which we want to redirect to the client (the numbers of the types as I mentioned above).

Redirect PowerShell output

PowerShell uses a redirection operators in order to control the output streaming. In the table bellow 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 orwe 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 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 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 I 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 I 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 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 I am 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 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 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 I forgot to add it before and sicne I am lazy to start re-number all the Demos I 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 si 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.

I think that we can stop here for now, and move to summarize what I shewed in the post...

In conclusion

In this post I explain in short what are Streams in Windows PowerShell and in SQL Server, and I 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, i 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.

I hope that this post was clear, fun to read, and most of all that it was useful :-)

If you liked the post, if you have any comment or feedback, or if you just want to say "hi" I invite you to follow me on Facebook, and feel free to add your comments there.

   

Credits

Well.... I publish the post, but it is based on the long discussion and all the participates in the discussion, directly contributed for this post by taking part in the discussion. With that being said I need to mentioned one in particular which contributed insights and  answer to the original question - Thanks Will_Kong.

Resources and More to read