SMO v11.0 behavior change when generating scripts

Question

I have an application which generates a SQL script to load an empty database with our table objects. When testing with the [new] SQL Server 2012 SMO assemblies, I noticed the constraints were not generated with the tables (INDEX, etc.). Our application references a SQL Server 2008 R2 database from which to generate the SQL script. As I understood, the new SMO assemblies are backward compatible with prior versions of SQL Server.

I put together a sample application to reproduce the issue. If I reference the V10 assemblies, I get the correct behavior. If I reference the V11 assemblies, the sample application does not include index, and the like.  The referenced database is running under SQL Server 2008 R2 with Compatibility level set to SQL Server 2008 (100).  It used to be a SQL Server 2005 database, and then upgraded when that SQL Server was upgraded to SQL Server 2008 R2, if that matters.

Here is a sample output using the v11 assemblies:

SET ANSI_PADDING ON

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblValidPriceSource]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tblValidPriceSource](

                [PriceSource] [dbo].[PriceSource] NOT NULL,

                [Description] [dbo].[Comments] NOT NULL

) ON [PRIMARY]

END

GO

Here is a sample output using the v10 assemblies.  I realize some of the scripting options that I am using may be incorrect, but the fact that the output changes between SMO versions indicates the behavior has changed.

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblValidPriceSource]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tblValidPriceSource](

                [PriceSource] [dbo].[PriceSource] NOT NULL,

                [Description] [dbo].[Comments] NOT NULL,

 CONSTRAINT [PK__tblValidPriceSource] PRIMARY KEY CLUSTERED

(

                [PriceSource] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

END

GO

Here is the sample code for a VB.Net console application:

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common


Module Module1

 

    Sub Main()

        Dim oSQLServer As Server = Nothing                                                              ' SMO - SQL Server 

        Dim oSQLConnection As ServerConnection = New ServerConnection

        Dim oDatabase As Microsoft.SqlServer.Management.Smo.Database = Nothing                          ' SMO - database

        Dim scrpDrop As Scripter = Nothing

        Dim scrpCreate As Scripter = Nothing

        Dim urns As New List(Of Microsoft.SqlServer.Management.Sdk.Sfc.Urn)

 

 

        ' *** CHANGE THESE VALUES ***

        Dim SQLServer As String = "SQLServer"

        Dim SQLUser As String = "sa"

        Dim SQLPassword As String = "password"

        Dim sFileName As String = "C:\Tables.SQL"

        Dim sDatabase As String = "MyUserDatabase"         ' reference a user database                            


        Try

            '

            ' Step 1) Connect to the SQL Server

            '

            With oSQLConnection

                .ServerInstance = SQLServer

                .SqlExecutionModes = SqlExecutionModes.ExecuteSql   'no need to buffer sql commands...ExecuteAndCaptureSql

                .ConnectTimeout = 10  '15

                .StatementTimeout = 5   'was 0

                .LoginSecure = False ' If False, SQL Server Authentication is used to establish a connection with the server.

                .Login = SQLUser

                .Password = SQLPassword

                .DatabaseName = sDatabase 

                .ApplicationName = My.Application.Info.Title & " - SMO"

                ' Go ahead and connect

                .Connect()

            End With

            oSQLServer = New Server(oSQLConnection)

            oDatabase = oSQLServer.Databases(sDatabase)



            ' 

            ' Step 2) Output the table objects.

            '

            scrpDrop = New Scripter(oSQLServer)

            scrpCreate = New Scripter(oSQLServer)

            With scrpDrop.Options

                .IncludeIfNotExists = True      ' True means to only include object if not exists in destination database!

                .ScriptDrops = True             ' True means to ONLY script drops!

                .ScriptData = False

                .WithDependencies = True

                .AllowSystemObjects = False

                .FileName = sFileName

                .AppendToFile = True

                .Encoding = Text.Encoding.UTF8    ' oFile.Encoding

                .ToFileOnly = False

                .IncludeHeaders = False

 

            End With

            With scrpCreate.Options

                .IncludeIfNotExists = True      ' True means to only include object if not exists in destination database!

                .ScriptDrops = False            ' True means to ONLY script drops!

                .ScriptData = False

                .WithDependencies = True

                .AllowSystemObjects = False

                .FileName = sFileName

                .AppendToFile = True

                .Encoding = Text.Encoding.UTF8    ' oFile.Encoding

                .ToFileOnly = False

                .IncludeHeaders = False

 

                .PrimaryObject = True

 

                .Bindings = True

                .Indexes = True

                .ClusteredIndexes = True

                .SchemaQualify = True

                .NoIndexPartitioningSchemes = False

                .NoFileGroup = False

                .AllowSystemObjects = False

                .ExtendedProperties = True

 

                .DriAll = True

                

                .DriIncludeSystemNames = True

                .AnsiPadding = True

                .IncludeDatabaseContext = False

                .OptimizerData = False

                .Statistics = False

                .NoCollation = True

 

                .Triggers = False

            End With

 

               Debug.Print("   Collecting User-defined Data Types")

            For Each oDataType In oDatabase.UserDefinedDataTypes

                urns.Add(oDataType.Urn)

            Next

 

            Debug.Print("   Collecting Tables")

            For Each oTable In oDatabase.Tables

                If Not oTable.IsSystemObject Or (1 = 1) Then

                    urns.Add(oTable.Urn)

                Else

                    Debug.Print("System object: " & oTable.Name)

                End If

            Next

 

 

            If (urns.Count > 0) Then

                Debug.Print("   Scrpting Tables and User-defined Data Types")

                Dim tree As DependencyTree = scrpCreate.DiscoverDependencies(urns.ToArray(), True)

                Dim dc As DependencyCollection = scrpCreate.WalkDependencies(tree)

                removeUrnType(dc, "UserDefinedFunction")

                removeUrnType(dc, "StoredProcedure")

 

                scrpCreate.ScriptWithList(dc)

            End If

 

            Console.Read() 'to allow the user to read the contents on the console

        Catch ex As Exception

            Console.WriteLine("Exception: " & ex.Message)

 

        End Try

    End Sub

 

    Private Sub RemoveUrnType(ByVal dc As DependencyCollection, ByVal type As String)

        Try

            For i As Integer = dc.Count - 1 To 0 Step -1

                If dc(i).Urn.Type = type Then

                    dc.RemoveAt(i)

                End If

            Next

        Catch ex As Exception

            Debug.Print("Error in  RemoveUrnType: " & ex.Message)

        End Try

    End Sub

 

End Module

Any suggestions would be greatly appreciated.

 - Paul -

Answer 1

Hello Paul,

You should explicit define the option you want to script, otherwise the default settings/options are used and they may change with version change.

In this case you should set DriPrimaryKey to TRUE; by default it's false.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hi Olaf,

Thank you for the response but I do not think that it is.  I already set

.DriAll = True

By explicitly setting DriPrimaryKey to True, there is no change in behavior for me (I am still missing the indices when using the v11 assemblies).  I agree some default may have changed between versions.  Do you know of any documentation of what may have changed in SQL Server 2012 SMO?  There are too many options to try and determine which default affects me by changing the options on an individual basis.

Answer 3

You could take a look at sample powershell script

http://blogs.msdn.com/b/sethus/archive/2012/10/01/powershell-script-to-script-out-tables-with-dependencies-along-with-enforced-declarative-referential-integrity.aspx

Thanks

Sethu  Srinivasan [MSFT]

SQL Server

Answer 4

Paul, Did you ever find a solution for this?? Im having the exact same issue now we some scripts at my job since we have upgraded the server to SQL 2012