Love to code although it bugs me.

20 Jun 2022

Powershell Error on Invoke-Sqlcmd? Ditch the pipe operator.

As you might have read, I’m a fan of getting things done. Having to develop a quick batch solution for some data copying between different SQL Server databases, I chose to use Powershell scripting and the Invoke-SqlCmd cmdlet. However I was faced with unexpected errors for something as trivial as an export/import job. The first version of my script went like the example below:

On execution, it read the first record correctly and then aborted with the following error:

Invoke-Sqlcmd : The WriteObject and WriteError methods cannot be called 
from outside the overrides of the BeginProcessing, ProcessRecord, 
and EndProcessing methods, and they can only be called 
from within the same thread.
Validate that the cmdlet makes these calls correctly, 
or contact Microsoft Customer Support Services.

Perform the usual lazy Google search got me this Stack Overflow post. Feel free the learn the thread management and parallelism that Powershell performs when using its pipe operator. I was pressed for time, so I just went with a simpler explicit programming iterations solution, abandoning the ForEach-Object Cmdlet and going with a For loop. The new code, real similar to the previous one, looks like this:

Performance was not key, being this a nightly batch, and parallelism was perfectly dispensable. So, it was a good solution. Hope this helps anyone getting unstuck from the same error.