I am Berke

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! ;-)