Trying to extract and execute TSQL commands larger than 8K

PowerShell Help
1

Hi,

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
$Script[0]
Invoke-Sqlcmd -query $Script[0]

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?

Thanks.

2

Hey Roustam,

Take a look at these tips for SQL and Powershell: http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/06/10-tips-for-the-sql-server-powershell-scripter.aspx

Specifically #4 references what I believe is your issue, which is by default Invoke-SQLCmd only returns 4000 characters. There is a -MaxCharLength that can make it larger. If you run “Select Max(Len([ScriptText])) MyDB.[MySchema].[DBScripts] where DBName = ‘ABC’ and ScriptName = ‘13-StoredProcedures.sql’”, it should tell you the largest script size and use that to get your adjustment.

3

Thanks Rob, this solved my problem.