Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


2
1
Sign in
to vote
Type: Bug
ID: 773907
Opened: 12/9/2012 4:15:36 PM
Access Restriction: Public
0
Workaround(s)
view
1
User(s) can reproduce this bug

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.
Details
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
Sign in to post a workaround.