Angela Henry had a query which worked fine in Management Studio but not in Powershell:
After I dusted off my PowerShell 2.0 documentation, I got my script written and started testing. I processed several folders and their files before I received the following error while running my PowerShell script:
Invoke-Sqlcmd : String or binary data would be truncated.
The statement has been terminated.
At line:127 char:36
+ … MyResults = Invoke-Sqlcmd -ServerInstance $ServerName `
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
+ FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand
Interesting. I added some Write-Host statements for troubleshooting and found the offending entry. Like any good programmer, I tested my stored procedure call in SQL Server Management Studio (SSMS) to make sure it really was a SQL Server error and guess what? It worked just fine! No errors what so ever. WTH?! This is where my tunnel vision sets in. If it works in SSMS but not in PowerShell, then PowerShell must be the problem, right? Well, sort of.
Read on for the solution.