{"id":509,"date":"2011-10-04T22:42:57","date_gmt":"2011-10-04T22:42:57","guid":{"rendered":"\/blogs\/jonathan\/post\/Capturing-InfoMessage-Output-(PRINT-RAISERROR)-from-SQL-Server-using-PowerShell.aspx"},"modified":"2017-04-13T12:11:56","modified_gmt":"2017-04-13T16:11:56","slug":"capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/","title":{"rendered":"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell"},"content":{"rendered":"

Tonight, a question was asked on the #sqlhelp<\/a> tag on Twitter about how to capture all of the output from a stored procedures execution, to include the informational and error message outputs that may be returned by using PRINT or RAISERROR in the code.  This was a problem I ran into in the past with C# and the way you do it in .NET is to handle the InfoMessage Event for the SqlConnection object using a SqlInfoMessageEventHandler in your code.  The same thing can be done in PowerShell to have these messages written out to the output of a scripts execution.<\/p>\n

\n

$conn = New-Object System.Data.SqlClient.SqlConnection "Server=.\\LAB1;Database=tempdb;Integrated Security=SSPI;";
$conn.Open();<\/p>\n

## Standard default connection with no EventHandler
$cmd = $conn.CreateCommand();
$cmd.CommandText = "PRINT ‘This is the message from the PRINT statement’";
$res = $cmd.ExecuteNonQuery();
$cmd.CommandText = "RAISERROR(‘This is the message from the RAISERROR statement’, 10, 1)"; 
$res = $cmd.ExecuteNonQuery();
$conn.Close();<\/p>\n

## Attach the InfoMessage Event Handler to the connection to write out the messages
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {param($sender, $event) Write-Host $event.Message };
$conn.add_InfoMessage($handler);
$conn.FireInfoMessageEventOnUserErrors = $true;<\/strong><\/p>\n

$conn.Open();<\/p>\n

$cmd = $conn.CreateCommand();
$cmd.CommandText = "PRINT ‘This is the message from the PRINT statement’";
$res = $cmd.ExecuteNonQuery();
$cmd.CommandText = "RAISERROR(‘This is the message from the RAISERROR statement’, 10, 1)"; 
$res = $cmd.ExecuteNonQuery();
$conn.Close();<\/p>\n<\/blockquote>\n

The bold code region above creates the EventHandler so that it writes the Message out to the host and then adds the handler to the InfoMessage Event for the connection.  The last line of code specifies for the event to fire on lower priority user errors as well.<\/p>\n","protected":false},"excerpt":{"rendered":"

Tonight, a question was asked on the #sqlhelp tag on Twitter about how to capture all of the output from a stored procedures execution, to include the informational and error message outputs that may be returned by using PRINT or RAISERROR in the code.  This was a problem I ran into in the past with […]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,38,40],"tags":[],"class_list":["post-509","post","type-post","status-publish","format-standard","hentry","category-powershell","category-sql-server-2008","category-sql-server-denali"],"yoast_head":"\nCapturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell - Jonathan Kehayias<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell - Jonathan Kehayias\" \/>\n<meta property=\"og:description\" content=\"Tonight, a question was asked on the #sqlhelp tag on Twitter about how to capture all of the output from a stored procedures execution, to include the informational and error message outputs that may be returned by using PRINT or RAISERROR in the code.  This was a problem I ran into in the past with […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/\" \/>\n<meta property=\"og:site_name\" content=\"Jonathan Kehayias\" \/>\n<meta property=\"article:published_time\" content=\"2011-10-04T22:42:57+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:11:56+00:00\" \/>\n<meta name=\"author\" content=\"Jonathan Kehayias\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jonathan Kehayias\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/\"},\"author\":{\"name\":\"Jonathan Kehayias\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"headline\":\"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell\",\"datePublished\":\"2011-10-04T22:42:57+00:00\",\"dateModified\":\"2017-04-13T16:11:56+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/\"},\"wordCount\":289,\"commentCount\":12,\"articleSection\":[\"Powershell\",\"SQL Server 2008\",\"SQL Server Denali\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/\",\"name\":\"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell - Jonathan Kehayias\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\"},\"datePublished\":\"2011-10-04T22:42:57+00:00\",\"dateModified\":\"2017-04-13T16:11:56+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Powershell\",\"item\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/category\\\/powershell\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#website\",\"url\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/\",\"name\":\"Jonathan Kehayias - The Rambling DBA\",\"description\":\"The Rambling DBA\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.sqlskills.com\\\/blogs\\\/jonathan\\\/#\\\/schema\\\/person\\\/01c10d94f3648654ef706d5e6305f69c\",\"name\":\"Jonathan Kehayias\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g\",\"caption\":\"Jonathan Kehayias\"},\"sameAs\":[\"http:\\\/\\\/3.209.169.194\\\/blogs\\\/jonathan\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell - Jonathan Kehayias","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/","og_locale":"en_US","og_type":"article","og_title":"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell - Jonathan Kehayias","og_description":"Tonight, a question was asked on the #sqlhelp tag on Twitter about how to capture all of the output from a stored procedures execution, to include the informational and error message outputs that may be returned by using PRINT or RAISERROR in the code.  This was a problem I ran into in the past with […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/","og_site_name":"Jonathan Kehayias","article_published_time":"2011-10-04T22:42:57+00:00","article_modified_time":"2017-04-13T16:11:56+00:00","author":"Jonathan Kehayias","twitter_misc":{"Written by":"Jonathan Kehayias","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/#article","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/"},"author":{"name":"Jonathan Kehayias","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"headline":"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell","datePublished":"2011-10-04T22:42:57+00:00","dateModified":"2017-04-13T16:11:56+00:00","mainEntityOfPage":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/"},"wordCount":289,"commentCount":12,"articleSection":["Powershell","SQL Server 2008","SQL Server Denali"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/","name":"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell - Jonathan Kehayias","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website"},"datePublished":"2011-10-04T22:42:57+00:00","dateModified":"2017-04-13T16:11:56+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/capturing-infomessage-output-print-raiserror-from-sql-server-using-powershell\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/"},{"@type":"ListItem","position":2,"name":"Powershell","item":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/category\/powershell\/"},{"@type":"ListItem","position":3,"name":"Capturing InfoMessage Output (PRINT, RAISERROR) from SQL Server using PowerShell"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/","name":"Jonathan Kehayias - The Rambling DBA","description":"The Rambling DBA","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/#\/schema\/person\/01c10d94f3648654ef706d5e6305f69c","name":"Jonathan Kehayias","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/86630e27f5deecc5c393ea57fc7c3b6a068949f4fd6b5309f81de5a276f12855?s=96&d=mm&r=g","caption":"Jonathan Kehayias"},"sameAs":["http:\/\/3.209.169.194\/blogs\/jonathan"]}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/comments?post=509"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/posts\/509\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/media?parent=509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/categories?post=509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/jonathan\/wp-json\/wp\/v2\/tags?post=509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}