3

I'm trying to query a SQL database and return only the messages that are produced, table results aren't important. I've looked at several answers online and came up with the following code, but every time I output the $message it is empty.

Can someone tell me what I'm doing wrong here or if there's some hidden problem?

$server = 'SampleServer'
$sqlQuery = "print 'sample message';"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection("Server=$server; Trusted_Connection=True;") 
$sqlConnection.FireInfoMessageEventOnUserErrors = $true

    #Listener for messages
$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler]{$message = "$($_)"}
$sqlConnection.add_InfoMessage($handler)

     #tried using events, but it also didn't return anything. Assumed $Event was some system variable?
     #  Register-ObjectEvent -InputObject $sqlConnection -EventName InfoMessage -Action {$message = "$($Event.SourceEventArgs)"} -SupportEvent

     #run Query
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = $SQLQuery
$adapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlcommand
$dataset = New-Object System.Data.DataSet
$adapter.Fill($dataSet) | out-null

$sqlConnection.Close()
    #output $message results
write-host $message

Also, I can't use Invoke-SqlCommand.

EDIT So, after tinkering with the code some more, there's not really a problem with it, but there's a problem with how the query message is being returned from the $handler block. If I put a write-host in the $handler block it works just fine, but I need the information later in my script. I've tried these solutions to no avail (also added return $message to $handler):

$message = $sqlConnection.add_InfoMessage($handler) #nothing returned
$sqlConnection.add_InfoMessage($message = $handler) #just plain wrong

Is there a correct location to put an assignment in this situation?

CuriousOne
  • 922
  • 1
  • 10
  • 26
  • 2
    You are creating a sqlcommand but never doing anything with it. – Ryan Wilson May 11 '18 at 13:50
  • Even when I create an adapter and fill a dataset, there's nothing produced. Updated solution to show that part – CuriousOne May 11 '18 at 14:01
  • 1
    @RyanWilson I think you misread the question, he does not want to return query results. – Jacob Colvin May 11 '18 at 14:02
  • @JacobColvin If you don't acctually execute a command on SQL, what message would you expect to get? – Ryan Wilson May 11 '18 at 14:17
  • @RyanWilson Just creating a connection should be enough to return messages. Such as "Hello client you are connected". In pl/sql dbms at least will print something similar. – Jacob Colvin May 11 '18 at 14:19
  • 2
    Hi, have you seen [this question](https://stackoverflow.com/questions/7769839/how-to-retrieve-output-statement-when-calling-stored-proc-through-powershell?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa)? – sodawillow May 11 '18 at 15:09
  • @sodawillow , I looked at that before and couldn't get it to work, but I just tried again and realized why it didn't work for me. In the `$handler` scriptblock, I was setting `$message`, but I forgot that I can't access that outside the block. Trouble is, I need to gather that information outside the block – CuriousOne May 11 '18 at 17:11
  • I guess the messages are in the warning stream, maybe this article helps you further (in the end there are a number of commands that pipe specific streams to a file): https://blogs.technet.microsoft.com/heyscriptingguy/2014/03/30/understanding-streams-redirection-and-write-host-in-powershell/ – iRon May 12 '18 at 15:12

0 Answers0