none
SMO ScriptingOptions.NoCommandTerminator question RRS feed

  • Question

  • I'm trying to get the batch separator to show when I get the script for a SMO object. I see that I can get or set the batch separator using the ServerConnection.BatchSeparator() method.

    First question: Is the batch separator the same as the command terminator referenced in the ScriptingOptions.NoCommandTerminator (i.e., basically the "GO" statement by default)?

    By default, ScriptingOptions.NoCommandTerminator is set to false, so I'd expect the GO statement to be in there. Setting this to true or false doesn't change anything...I still don't get the batch separator in the script. I know in DMO, setting this No Command Terminator option to false would put the batch separator in.  What am I missing?

    Also, for the string collection returned from the script, is each string in the collection basically a separate batch? It appears to be the case, but I wanted to confirm. If so, and this issue is an actual bug, I could work around it by manually adding the batch separator for each string in the string collection, but I wanted to make sure I wasn't missing anything first.

    Johnny.

    Thursday, January 19, 2006 5:49 PM

All replies

  • Answer on first question: yes it is.
     
    Secondly, the batch separator is only emitted when a single batch (one of the strings in the StringCollection that is returned) requires one. Maybe your batch doesn't require one. If you still think it is broken, please show an example.
     
    Third question: yes each string is a batch. If you are executing mutiple batches by concatenating these in a single script, then you are responsible for adding the batch separators (GO).
     
    HTH
    Thursday, January 19, 2006 11:10 PM
  • Here's an example:

    In DMO, pubs.dbo.authors scripts out like this with NoCommandTerminator set to false:

    CREATE TABLE [authors] (
     [au_id] [id] NOT NULL ,
     [au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
     [address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
     [contract] [bit] NOT NULL ,
     [junk] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    which ends with a GO.

    In SMO, same table scripts out like this with NoCommandTerminator set to false:

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    CREATE TABLE [dbo].[authors](
     [au_id] [id] NOT NULL,
     [au_lname] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [au_fname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [phone] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [contract] [bit] NOT NULL,
     [junk] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
     

    with no GO at the end. I would also expect each SET statement to have a GO after it as well.  If I script this table out in Management Studio, I see the GOs in it. Is this a case where I manually have to add the GOs?

    Just to be clear, can you give examples when a batch separator is required and the scripter itself will put one in it?

    Different behavior with the same settings. If this is by design in SMO, is there some documentation somewhere that explains different behavior for the same options between DMO and SMO? 

    Friday, January 20, 2006 1:01 AM
  • I also just discovered that if you set the ScriptingOptions.FileName property so it writes out to a file, the batch separator does get put in for this file if NoCommandTerminator is set to false. It just doesn't seem to put it in the string collection. 

    Johnny.

    Friday, January 20, 2006 7:01 PM
  • Don't know if the reason I hadn't heard back is because it was marked as answered. I still had more questions about it though, so I unmarked that reply that partially answered it.
    Tuesday, February 7, 2006 12:15 AM
  • DMO was somewhat different in that aspect as it does return the script in one string.

    SMO emits each batch in a string. It puts all batches in a stringcollection.

    If you want to execute all batches in one script, then you need to seperate these with GO.

    If you are executing these seperately, it is not needed.

    SMO will put GO between statements if needed. If NoCommandTerminator == true then no GO will emitted.

    Saturday, February 11, 2006 12:36 AM
  • SMO only seems to put GO between statements in an output file when you set the FileName scripting property - it doesnt include it in the string collection returned.

     

    So if i set the filename property and script a table, i get this put into the file

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[BadMessages](
     [MessageXML] [xml] NULL,
     [MessageType] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
     [Error] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
     [DateEntered] [datetime] NULL
    ) ON [PRIMARY]

    GO

     

    But the string collection returned is

     

    SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    CREATE TABLE [dbo].[BadMessages](
            [MessageXML] [xml] NULL,
            [MessageType] [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
            [Error] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [DateEntered] [datetime] NULL
    ) ON [PRIMARY]

     

    without the GO statements.

     

     

    Tuesday, October 7, 2008 1:13 PM
  • Hello antxxxx,

     

    It is normal as the scripts were initially conceived for isql/osql/sqlcmd which need GO as statement separator

    the 2nd form is intented to be used inside a prog with the method ExecuteNonQuery ( so no GO necessary )

     

    Have a nice day

     

     

    Monday, October 13, 2008 9:34 PM
    Moderator