0

Currently i am trying to retrieve a query hash from a database. The plan is to query slow queries from the query store and write them to a csv file. To retrieve the data fro the database I am using the following command:

$queryResult = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -InputFile $SQLQueryPath -Username $SQLUser -Password $SQLPassword -ConnectionTimeout 60  | Export-Csv $ResultPath -Delimiter "," -NoTypeInformation

The query used retrieves queries that are running slow with their query hash (stored in the query_hash field). This query is tested on the same database using SMSS and then it returns the hash field shown as a hash. This hash is stored in the sql database, it is not generated by the query or script.

But whenever I try to retrieve the query hash using Invoke-Sqlcmd it returns the query_hash field as a byte array (System.Byte[]). All other fields are shown as in SMSS. Tried printing the result in the powershell terminal but there it also shows me an array of bytes.

How can I get the script to return the hash?

Tried to use [System.Text.Encoding]::ASCII.GetString($.query_hash) and [System.Text.Encoding]::UTF8.GetString($.query_hash) but they expect a single byte where this script gives an array.

Rick
  • 1
  • 2
    The query hash *is* a collection of bytes. SSMS helpfully formats this as a hexstring, but that's strictly a display issue. If you want the same in PowerShell, try something like `[BitConverter]::ToString($query_hash).Replace('-', '')` (add lower-casing and/or `0x` prefix to taste). – Jeroen Mostert Jan 18 '23 at 11:18
  • Thank you so much for your response, didn't know that it was formatted by SSMS, that explains a lot. This code works! The only thing is i get a lot of zero's at the end of the hash, any idea why that is the case? – Rick Jan 18 '23 at 11:56
  • Apparently, because `Invoke-SqlCmd` is stupid. It gives back an array of 1024 bytes even though the column type is `BINARY(8)`. Yet another reason to dislike it (its lack of support for parameters would be another). Without tooting my own horn too much, I have a [code snippet here](https://stackoverflow.com/a/50582924/4137916) with alternate functions that use ADO.NET directly that don't suffer from this issue. Alternatively, you could simply take the first 16 characters of the final result (`.Substring(0, 16)`), since we happen to know the exact type in this query. – Jeroen Mostert Jan 18 '23 at 12:06
  • Haha I feel your pain, but i feel like for beginners in powershell like me and a simple select statement it would be adequate, as you said we know the length of the md5 hash so did the last thing and added 0x in front of it. Thanks again for your help! – Rick Jan 18 '23 at 12:20
  • As much as I think using `Invoke-SqlCmd` for separate queries is a lost cause, I submitted a [bug report](https://github.com/PowerShell/PowerShell/issues/18966) anyway, since there's no good reason for this behavior to be there. – Jeroen Mostert Jan 18 '23 at 12:41

0 Answers0