Trying to extract and execute TSQL commands larger than 8K

Welcome Forums General PowerShell Q&A Trying to extract and execute TSQL commands larger than 8K

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
3 years, 7 months ago.

  • Author
    Posts
  • #28599

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 631
    Helping Hand
    Rank: Major Contributor

    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

    Participant
    Points: 0
    Rank: Member

    Thanks Rob, this solved my problem.

The topic ‘Trying to extract and execute TSQL commands larger than 8K’ is closed to new replies.

denizli escort samsun escort muğla escort ataşehir escort kuşadası escort