issue when Script Deployed with Powershell (POSH)

I’m able to execute SP without any issue if I’ve deployed directly from SSMS, But when I’ve deploy it using Powershell, SP created successfully and when i tried to execute its throwing an error like…

Msg 8152, Level 16, State 14, Procedure usp_bpcheck, Line 5704
String or binary data would be truncated.

Msg 8152, Level 16, State 4, Procedure usp_bpcheck, Line 6093
String or binary data would be truncated.

I tried multiple ways in POSH to deploy the SP every time I’m getting same error when SP executed. Please help me how to deploy the Store Proc using POSH without any run time errors.

POSH command used :

<sup>$attachSQLCMD1 = @" … BP CHECK Script … "@</sup>

<sup>Invoke-Sqlcmd $attachSQLCMD1 -QueryTimeout 0 -ServerInstance $SQLServer -maxcharlength ([int]::MaxValue)</sup>

Thanks In advance!!

There is a Stored Procedure (SP) that is being executed and returning data? Are you passing parameters to the SP? Is it a large string? You’re using [int]::MaxValue which is over 2 billion (2,147,483,647) for the returned data. There are other similar posts setting the -MaxCharLength:

https://stackoverflow.com/questions/28374805/invoke-sqlcmd-doesnt-return-long-string

Have you looked at the length server-side to see how big the data is? It’s more than 2 billion characters?

[quote quote=217521]There is a Stored Procedure (SP) that is being executed and returning data? Are you passing parameters to the SP? Is it a large string? You’re using [int]::MaxValue which is over 2 billion (2,147,483,647) for the returned data. There are other similar posts setting the -MaxCharLength:

https://stackoverflow.com/questions/28374805/invoke-sqlcmd-doesnt-return-long-string

Have you looked at the length server-side to see how big the data is? It’s more than 2 billion characters?[/quote]

Thanks for your response, I’ve issue while executing the SP after deployment. I’ve notice some changes in SP which deployed through powershell.

Powershell did some fancy work to my existing script, Its replace all the $Some_text (Prefix with $) with Null (Noting Just a space). How do I handle it.

like an example Write-Output $diskpart to Write-Output