SMO for SQL2012 ignores some scripting options when scripting against 2008/R2 Databases - by Tamarick Hill

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


2
1
Sign in
to vote
ID 773907 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 12/9/2012 4:15:36 PM
Access Restriction Public

Description

SMO for SQL2012 seems to ignore some scripting options when scripting against 2008 and 2008R2 instances. I have a Powershell script that scripts out all tables in my environment. These scripts include Primary key definitions, default constraints, and indexes using the "DriAll = $True" flag for scripting options. After upgrading to SQL2012, only the table definition is scripted without Indexes, Primary keys, or default constraints. 
Sign in to post a comment.
Posted by Microsoft on 1/31/2013 at 3:12 PM
Hello

We didn't receive a response, so we assume that the issue was resolved and we will go ahead and close it. You can always reactivate it and add more details.

Thank you
Alex Grach[MSFT]
Posted by Microsoft on 1/16/2013 at 3:55 PM
Hi Tamarick,
I tried in my development environment but I was unable to reproduce the defect. Could you take a look at the code below and see if I missed anything. We really appreciate it if you can help to create a repro. Thanks

. Create a test table in database [TestDatabase]
USE [TestDatabase]
GO

CREATE TABLE [dbo].[Customers](
    [cust_id] [int] NOT NULL PRIMARY KEY,
    [cust_name] [varchar](30) NOT NULL,
    [last_updated] [datetime] NOT NULL,
)
GO

CREATE NONCLUSTERED INDEX [NonClstdIndexCustName] ON [dbo].[Customers]([cust_name])
GO

ALTER TABLE [dbo].[Customers] ADD DEFAULT ('new customer') FOR [cust_name]
GO

ALTER TABLE [dbo].[Customers] ADD CONSTRAINT [df_Customers_last_updated] DEFAULT (getdate()) FOR [last_updated]
GO


. Script the table using C#
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;

namespace vsts1147843
{
    class Program
    {
        static void Main(string[] args)
        {
            string serverName = @"MYMACHINE\MYKJ"; // Please use your server name
            string databaseName = "TestDatabase";
            string tableName = "Customers";

            Server server = new Server(serverName);
            Database database = server.Databases[databaseName];
            Table table = database.Tables[tableName];

            ScriptingOptions so = new ScriptingOptions();
            so.DriAll = true;
            so.NonClusteredIndexes = true;
            StringCollection sc = table.Script(so);

            foreach (string s in sc)
            {
            
                Console.WriteLine(s);
            }

            Console.ReadLine();
        }
    }
}

I've run the above C# code against KJ using KJ and Denali asseblies. They both produce the same result.

. Powershell
try {
    $serverName = "MYMACHINE\MYKJ"
    $databaseName = "TestDatabase"
    $tableName = "Customers"
    [Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [Reflection.Assembly]::Load("Microsoft.SqlServer.SmoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfoExtended, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
    [Reflection.Assembly]::Load("Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

    $server = new-object -typeName microsoft.sqlserver.management.smo.server -argumentlist $serverName
    $databases = $server.Databases
    $database = $databases[$databaseName]
    $table = $database.Tables[$tableName]
    $so = new-object -typeName microsoft.sqlserver.management.smo.ScriptingOptions
    $so.DriAll = $true
        $so.NonClusteredIndexes = $true
    $script = $table.Script($so)
    $script
} catch [system.exception] {
" !! A system exception occured !!"
}

The powershell script above loads the Denali assmblies (can be changed to KJ) and produces the same result, with all the indexes, default constraints and PK scripted.

Any thought?

Thank you very much.

Jinlin Zhang
Microsoft