Checking Query Settings

Kevin Feasel



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.

Related Posts

In Praise Of Tabular Editor

Teo Lachev shares a positive review of Tabular Editor, a community tool for working with Tabular models: What tool do you use for Analysis Services Tabular development? SSDT right, what else? Here is a little secret. I almost don’t use SSDT anymore, except for limited tasks, such as importing new tables and visualizing relationships. I […]

Read More

A Tool For Analyzing AG Replica Latency

Simon Su has an interesting tool available: I wrote an article to discuss about data movement latency between AG groups: Now I develop a tool to analyze AG log block movement latency between replicas and create report accordingly. Click through for more info and check it out on Github.

Read More


March 2017
« Feb Apr »