SMO ScriptingOptions - ScriptBatchTerminator

  • MG-148046

    SSCrazy Eights

    Points: 9945

    I am trying to script database objects and include a Batch Termintor (GO). I have loaded the SMO.Scripter

    $Scripter=New-Object('microsoft.sqlserver.management.smo.scripter')

    and set the

    $Scripter.Options.ScriptBatchTerminator=$true

    but no terminators (GO) appear in the script. Other options work.

    Any help would be appreciated.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Grant Fritchey

    SSC Guru

    Points: 396551

    According the Microsoft rep here[/url], you should only see the GO statements where they're required. Is that not true? Have you tried testing the script generated?

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MG-148046

    SSCrazy Eights

    Points: 9945

    I spent 2 days trying to find out what ScriptBatchTerminator wasn't working. There are any number of forums that have that question and not one had an answer:angry:

    I completely missed the NoCommandTerminator boolean in the documentation for ScriptingOptions. I'll try setting that to false and see what happens.

    It also seems that i may need to use the FileName property rather than the Out-File commandlet (I'm using Powershell).

    Thanks for the link!

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Grant Fritchey

    SSC Guru

    Points: 396551

    If it still doesn't work, post back here. I'll try to see if I can get it to work too.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • MG-148046

    SSCrazy Eights

    Points: 9945

    Okay - it works with:

    $Scripter.Options.ScriptBatchTerminator=$true

    $Scripter.Options.NoCommandTerminator=$false

    $Scripter.Options.FileName = <Filepath and name or variable with that>

    $Scripter.Options.ToFileOnly=$true

    $Scripter.Options.AppendToFile=$true

    The caveat though, is that there are objects that don't have a script method so it either becomes a mix and match of the FileName option and the Out-File cmdlet or just use the cmdlet for all of it. I'm scripting out the whole server, lock, stock and barrel so I'm torn as to which way to go since I've already got a script with the cmdlet and I'm adding the batch terminator as an extra out-file where I think the terminator is needed. It would be nice to just let the script method do it, though. :unsure:

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply