SMO "Create Procedure" generated script is unusable by DWalker

Hi,

Thanks for the prompt reply.
I understand that you are having problem if you execute the 2 SETS and CREATE PROC in a single batch.
But SMO had generated them as seperate batch hence they were in StringCollection as seperate strings and if they are executed using SMO's ExecuteNonQuery they will execute fine.

Also if they are scripted to file with a GO(using batchterminator option) they will execute using SSMS which will read GO as batchterminator.

Now coming to question what is the purpose of these two SET statements.These two set statement set the ANSI_NULLS and QUOTED_IDENTIFIER to what was their value when the stored proc was originally created.

Regards,
Alok

Alok, I didn't really understand your message. Originally, Vinod said this would get fixed.

You are showing how to add a "GO" statement, which is not the problem. The problem is that there are statements before the Create Procedure statement, and so SMO generates scripts that you can't use.

SMO creates scripts that you can't use even when you are scripting to a file, which is what I am doing.

The script generated by SMO should be executable. They should be valid scripts. That's all I'm saying.

The Create Procedure script should not have any statements before the CREATE PROCEDURE line.

The NoCommandTerminator thing came up because Vinod mentioned it. But it doesn't help the issue; the two lines before the CREATE PROCEDURE line are still there. The GO statements are not related.

Please let me know if this part -- the two illegal SET commands -- can get fixed. Thanks.

David Walker

Hi,

This has been closed by design as Script method generates a StringCollection and each string in the collection needs to be executed seperately.
One can use ExecuteNonQuery methods (in Database class)to execute the script on the server.

Also if one really needs a GO or anyother text one can add one as follows

foreach (var item in s.Databases["AdventureWorks"].StoredProcedures[0].Script())
                {
                    Console.WriteLine(item);
                    Console.WriteLine("GO");
                }

NoCommandTerminator option is applicable only in case you are scripting to file using ScriptingOptions.FileName

Regards,
Alok

I don't like this item being closed "by design". I'm sure that Microsoft didn't intend to have SMO create a script that is not usable. The closing should have been "it's a bug" or "fix in the future" which is what your comment said.

Please don't let this get forgotten in SQL 2008. Thank you.

Yes, I know that I can hack the generated script myself to make it usable.

I know you can't commit to when this will be fixed, but I hope it can be fixed in a service pack instead of waiting for a "future release" of SQL itself. **Scripts that are generated by SMO should be usable!**

I submitted this 8 months ago, and I'm glad it's finally getting a response.

And PLEASE, please, don't make any more options that are negative (like NoCommandTerminator). If any similar options are created in the future, name it CommandTerminator and default it to False instead of calling it NoCommandTerminator. Having an option named NoCommandTerminator that we may need to set to False, is just silly. :-)

Thanks.

Hi,

Thanks for reporting this issue. This is a known issue in SMO. Here is a workaround for now.
StoredProcedure sp = new StoredProcedure(db, "procedureName");
sp.TextMode = false;
sp.TextBody = "select 1";
sp.ExecutionContext = ExecutionContext.Caller;
sp.AnsiNullsStatus = true;
sp.Create();
ScriptingOptions so = new ScriptingOptions();
so.IncludeIfNotExists = true;
sc = sp.Script(so);
foreach (String s in sc)
{
         Console.Write(s);
}

If the above solution isnt useful, you can also try scripting to a file and you will get the batch terminators.
            ScriptingOptions so = new ScriptingOptions();
            so.NoCommandTerminator = false;
            so.ToFileOnly = true;
            so.FileName = "sp.sql";

We will fix this in the future releases.How this workaround will help you.
Regards
Vinod

Hello? Is anyone going to respond to this item?