Checking Query Settings

Kevin Feasel

2017-03-17

Tools

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

Categories

March 2017
MTWTFSS
« Feb Apr »
 12345
6789101112
13141516171819
20212223242526
2728293031