I was working on a big project of mine, automating some kind of data collection from SQL Server with PowerShell by using Invoke-SQLCMD. One of the most annoying error message I got was the following:
Invoke-Sqlcmd : The pipeline has been stopped.
At C:\script.ps1:52 char:19
+ $result = Invoke-Sqlcmd -QueryTimeout 300 -InputFile $input -ServerInsta ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], PipelineStoppedException
+ FullyQualifiedErrorId : SqlExectionError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Invoke-Sqlcmd : Exception of type 'System.OutOfMemoryException' was thrown.
At C:\script.ps1:52 char:19
+ $result = Invoke-Sqlcmd -QueryTimeout 300 -InputFile $input -ServerInsta ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], OutOfMemoryException
+ FullyQualifiedErrorId : SqlServerError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Well, I was using Google/Bing finding answer to this problem, but I have had no luck. It took me a hour to realize that there must be something wrong with my data and not the script! I was right!
Let’s try to run the following PowerShell script:
Invoke-Sqlcmd -QueryTimeout 0 -Query "select * from sys.dm_exec_cached_plans" -ServerInstance ".\DEV01" -MaxBinaryLength ([int]::MaxValue) -MaxCharLength ([int]::MaxValue)
You will have the same out fo memory exception as above. You can ask why because you get the result w/o any error in SSMS. Well, SSMS is a much better product, it is able to deal with varbinary data type! Yes, the problem is the result set contans varbinary data and currently all version of PowerShell (up to V3) do not support it.
Okay, what can I do? Answer is not really straightforward, but can be solved. You need to convert varbinary to a text type. Well a simple CAST or CONVERT will result unreadable character strings and will not be able to use them for the same purpose as the varbinary type.
I have reused a solution from a login copy script written by Robert L. Davis (T | B): based on this article, I rewrote the script as follows:
SELECT
cp.[bucketid],
cp.[refcounts],
cp.[usecounts],
cp.[size_in_bytes],
'0x' + CAST('' As XML).value('xs:hexBinary(sql:column("memory_object_address"))', 'nvarchar(300)') AS [memory_object_address],
cp.[cacheobjtype],
cp.[objtype],
'0x' + CAST('' As XML).value('xs:hexBinary(sql:column("plan_handle"))', 'nvarchar(300)') AS [plan_handle],
cp.[pool_id],
FROM
sys.dm_exec_cached_plans cp
Well, this time worked.
Enjoy! ;-)