Smo issue when generating drop default constraint script

In SQL Server 2005, using the following code to script out dropping default constraints, it always fails with the following exception:
 
Exception: {"Script failed for DefaultConstraint ‘DF_Employee_VacationHours’. "}
InnerException: {"Object reference not set to an instance of an object."}
using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace Test2
{
 class Program
 {
  static void Main(string[] args)
  {
   Server s = new Server(".");
   StringCollection sc;
   ScriptingOptions so=new ScriptingOptions();
   so.ScriptDrops = true;
   s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
   foreach (Table t in s.Databases["Adventureworks"].Tables)
   {
    foreach (Column c in t.Columns)
    {
     if (c.DefaultConstraint!=null)
     {
      Console.WriteLine(string.Format("{0},{1}", t.Name, c.Name));
      sc=c.DefaultConstraint.Script(so);
      foreach (string st in sc)
      {
       Console.WriteLine(string.Format("{0}", st));
      }
      sc = null;
     }
    }
   }
   return;
  }
 }
}
Microsoft has confirmed that this was a defect in smo (Ref. the reply to my post in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1532434&SiteID=1). To workaround this before it’s fixed, we can generate the script by StringFormat or WriteLine function.
The following is one sample:
using System;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
namespace Test2
{
    class Program
    {
        static void Main(string[] args)
        {
            Server s = new Server("aprnd0623");
            ScriptingOptions so = new ScriptingOptions();
            //so.ScriptDrops = false;
            so.ScriptDrops = true;
            //so.IncludeIfNotExists = false;
            so.IncludeIfNotExists = true;
            s.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;
            string dfCheckExists;
            string dfCheckNotExists;
            string dfDrop, dfCreate;
            dfDrop = "ALTER TABLE [{0}].[{1}] DROP CONSTRAINT [{2}]";
            dfCreate = "ALTER TABLE [{0}].[{1}] ADD CONSTRAINT [{2}] DEFAULT {3} FOR [{4}]";
            if (s.Information.Version.Major >= 9)
            {
                dfCheckExists = "IF EXISTS(SELECT 1 FROM sys.default_constraints a, sys.schemas b WHERE a.schema_id=b.schema_id AND a.name=’{0}’ AND b.name=’{1}’)";
                dfCheckNotExists = "IF NOT EXISTS(SELECT 1 FROM sys.default_constraints a, sys.schemas b WHERE a.schema_id=b.schema_id AND a.name=’{0}’ AND b.name=’{1}’)";
            }
            else
            {
                dfCheckExists = "IF EXISTS(SELECT 1 FROM dbo.sysobjects a, dbo.sysusers b WHERE a.uid=b.uid AND a.name={0} AND b.name={1})";
                dfCheckNotExists = "IF NOT EXISTS(SELECT 1 FROM sys.default_constraints a, sys.schemas b WHERE a.schema_id=b.schema_id AND a.name=’{0}’ AND b.name=’{1}’)";
            }
            foreach (Table t in s.Databases["Adventureworks"].Tables)
            {
                foreach (Column c in t.Columns)
                {
                    if (c.DefaultConstraint != null)
                    {
                        Console.WriteLine(string.Format("{0},{1}", t.Name, c.Name));
                        if (so.ScriptDrops == true)
                        {
                            if (so.IncludeIfNotExists)
                            {
                                Console.WriteLine(dfCheckExists, c.DefaultConstraint.Name, t.Schema);
                            }
                            Console.WriteLine(dfDrop, t.Schema, t.Name, c.DefaultConstraint.Name);
                            Console.WriteLine("GO\r\n");
                        }
                        else
                        {
                            if (so.IncludeIfNotExists)
                            {
                                Console.WriteLine(dfCheckNotExists, c.DefaultConstraint.Name, t.Schema);
                            }
                            Console.WriteLine(dfCreate, t.Schema, t.Name, c.DefaultConstraint.Name);
                            Console.WriteLine("GO\r\n");
                        }
                    }
                }
            }
            return;
        }
    }
}
Advertisements
This entry was posted in SQL Server 2005 SMO. Bookmark the permalink.

One Response to Smo issue when generating drop default constraint script

  1. Chandu M says:

    Looks Good

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s