Running T-SQL and returning Print information


One of the most questions that I saw in foruns is how to return the print messages from SQL Server using some PowerShell Function.

I created a function called Invoke-ExecuteTSQL. It is for T-SQL that does not return any data. Just to execute and return if it was OK or not, the message error (it it had) and the messages from print´s or any message with a severity of 10 or less from SQL Server.

It is using the SqlConnection.InfoMessage Event and it is part of SqlConnection Class,so it works on SQL Server 2000 and Windows Server 2003 SP2 after you install PowerShell 2.0 on it.

to illustrate I created a Table called test(id int)

The the Posh script with a simple T-SQL with prints

   1:  

   2: $Query = "    SET NOCOUNT on

   3:             DECLARE @val INT

   4:             SET @val = 1

   5:             print 'Step 1'

   6:             WHILE @val <= 100

   7:             begin

   8:                 INSERT INTO test VALUES (@val)

   9:                 PRINT 'Step2 '+ cast(@val as char)

  10:                 SET @val= @val +1

  11:             end"

  12: $a =Invoke-ExecuteTSQL -SQLInstanceName . -DatabaseName Test -verbose -Query  $Query

  13: $a

and the output properties are :

Exitcode = True or False if it runs successfully

ErrorMessage = If not runs  successfully,the error message

Message = All the print information in the T-SQL

image

It is on PoshCode Alegre

#PowerShellLifeStyle

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Powershell, SQL SERVER EM GERAL, Virtual Pass BR. Bookmark the permalink.

2 Responses to Running T-SQL and returning Print information

  1. Laerte,

    Thats a really succcient example of of how to capture the SqlConnection.InfoMessage event. However, there is a really polished script Originally by your fellow MVP Chad Miller, with some additions by myself and a few others for running a SQL cmd from powershell that solves this any many other problems.

    • Sure. Invoke-SQLCMD2 is one of my favorites functions and I used since Chad did and sent to me. It Was just a different approach to example how to use Register-Object event. You can use it for other SMO/NET Events as well. Also I needed the print/raiserror information wr returned as object,not in the -verbose parameter , as invoke-sqlcmd2 does.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s