CODEWITHSUNDEEP
c#.netsmo
user3527893
user3527893

Reputation: 123

.NET SMO stored procedure scripts mysteriously changed

We've been using .NET's Sql Server Management Objects (SMO) to maintain our database scripts (which serve as both install and upgrade scripts). For some stored procedures, we like to have default implementations which can later be customized at the customer's discretion. So, in our install/upgrade script, we had something like so:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STORED_PROC_NAME]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[STORED_PROC_NAME] @yada varchar(50),....
AS
BEGIN
     yada yada yada definition here is within the string passed to executesql
END'
END

Note how the entire definition is located within the string passed to sp_executesql.

This works well since the procedure is not changed if it already existed. However, at some point within the last few days, the scripts generated by SMO have changed to the following format:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[STORED_PROC_NAME]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[STORED_PROC_NAME] AS'
END

GO

ALTER PROCEDURE [dbo].[STORED_PROC_NAME] @yada varchar(50), ...
BEGIN
     yada yada yada this definition is now non-dynamic sql
END

This is obviously cleaner from a readability standpoint since the procedure definition is not located within a string, but now this script will alter stored procedures that already exist. What changed? Code that generated the script below:

Scripter scrp = new Scripter(serv);
scrp.Options.IncludeIfNotExists = true;  //important
scrp.Options.NoCollation = true;

scrp.Options.Encoding = Encoding.Unicode;

scrp.Options.Bindings = true;
scrp.Options.ClusteredIndexes = true;
scrp.Options.DriChecks = true;
scrp.Options.DriClustered = true;
scrp.Options.DriDefaults = true;
scrp.Options.DriForeignKeys = false;
scrp.Options.DriIndexes = true;
scrp.Options.DriNonClustered = true;
scrp.Options.DriPrimaryKey = true;
scrp.Options.DriUniqueKeys = true;

scrp.Options.FullTextIndexes = true;
scrp.Options.Indexes = true;
scrp.Options.NonClusteredIndexes = true;
scrp.Options.Triggers = false;


foreach (String s in scrp.Script(new Urn[] { source.StoredProcedures["STORED_PROC_NAME"].Urn }))
{
    file.WriteLine(s);
    file.WriteLine("GO");
}

Upvotes: 0

Views: 603

Answers (1)

Dan Forbes
Dan Forbes

Reputation: 2824

Assuming that your code did not change, I would have to guess that the library you are referencing changed. Are you now referencing a different version of the SMO library?

Upvotes: 1

Related Questions