Trying to extract and execute TSQL commands larger than 8K

This topic contains 2 replies, has 2 voices, and was last updated by Profile photo of Roustam Akhmetov Roustam Akhmetov 1 year, 7 months ago.

  • Author
    Posts
  • #28599
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    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.

  • #28606
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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.

  • #28658
    Profile photo of Roustam Akhmetov
    Roustam Akhmetov
    Participant

    Thanks Rob, this solved my problem.

You must be logged in to reply to this topic.