A really annoying problem I hit recently with a some Azure Runbooks was the following message occuring:
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.
After doing some digging, I found out that Invoke-SqlCmd isn’t thread safe, so other runbooks running on that Azure Automation account, could interfere with each other. Which would explain the above thread error.
Luckily MVP Stoyan Chalakov had already blogged about a solution - here.
Unfortunately, this didn’t work.
Unable to find type [Microsoft.SqlServer.Management.PowerShell.OutputType].
Damn.
I tried updating the SqlServer PowerShell module, no joy. Re-reading article again to see if I missed something and spotted the only question on the script.
https://gallery.technet.microsoft.com/Invoke-Sqlcmd-optimized-d6b4a0a3/view/Discussions#content
Damn. No answer to the nearly 3 year old question.
At this point I thought, dbatools. A quick look at the docs, bingo. Invoke-DbaQuery.
Few mins later I had dbatools installed in the Azure Automation account and it was just then a quick change
Replacing:
- Invoke-SqlCmd - with Invoke-DbaQuery
- Username / Password - with SqlCredential
- (and the troublesome) OutputAs - with As
- ServerInstance - with SqlInstance
(I also added -ReadOnly - its just a select statement that exports to CSV after all)
Net result. No more errors.