I am trying to do the following:
$Query = "SELECT [ScriptText] FROM MyDB.[MySchema].[DBScripts] where DBName = 'ABC' and ScriptName = '13-StoredProcedures.sql'"
$Script = Invoke-Sqlcmd -query $Query
Invoke-Sqlcmd -query $Script
I am getting an error message about some unclosed quotation mark and the value of the $script variable is truncated to around 8KB.
Even though the below query tells that the length of the string is 2543909 characters.
SELECT len([ScriptText]) FROM MyDB.[MySchema].[DBScripts] where DBName = 'ABC' and ScriptName = '13-StoredProcedures.sql'
Any idea how can I extract and execute the large script?
It is very possible that the query you are extracting from SQL needs to be reformatted to handle quoting issues. Not sure about the 8K limit. Can you run your TSQL queries in the SQL Management console?
Nope, this is not about re-formatting or quotes. It is about the size of the text.
This solved my problem:
$Script = Invoke-Sqlcmd -query $Query -maxcharlength ([int]::MaxValue)