用smo 生成sql server 2012 中 AdventureWorks2012表定义脚本 排除PropertyNotSetException

网上的using smo to generate table definition scripts 不能直接运行，提示PropertyNotSetException

查看详细信息，原来是没有设置scripter对象的 server属性

• key words:

1. 未经处理 PropertyNotSetException
   
2. “Microsoft.SqlServer.Management.Smo.PropertyNotSetException”类型的未经处理的异常在 Microsoft.SqlServer.Smo.dll 中发生
3. {"若要完成此操作，请设置属性 Server。"}

• 解决方法：为scripter 设置 server 属性
  

        

Scripter scriptor = new Scripter();scriptor.Server = server;//添加server属性scriptor.Options.Add(ScriptOption.DriAllConstraints);scriptor.Options.Add(ScriptOption.DriAllKeys);scriptor.Options.Add(ScriptOption.Default);scriptor.Options.Add(ScriptOption.ContinueScriptingOnError);scriptor.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);scriptor.Options.Add(ScriptOption.IncludeIfNotExists);UrnCollection collection = new UrnCollection();

• my situation ,test it with a winfor

1. 首先连接数据库
   

         2.生成选中对象的create脚本,保存到文件，这里是生成表的create脚本，保存到多个文件。

   3. 成功生成50个脚本

            4.打开目录

         6.打开：SepFriday10052014_CREATE_[Person].[Person].sql

SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Person].[Person]') AND type in (N'U'))BEGINCREATE TABLE [Person].[Person]([BusinessEntityID] [int] NOT NULL,[PersonType] [nchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,[NameStyle] [bit] NOT NULL CONSTRAINT [DF_Person_NameStyle]  DEFAULT ((0)),[Title] [nvarchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FirstName] [nvarchar](50) NOT NULL,[MiddleName] [nvarchar](50) NULL,[LastName] [nvarchar](50) NOT NULL,[Suffix] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[EmailPromotion] [int] NOT NULL CONSTRAINT [DF_Person_EmailPromotion]  DEFAULT ((0)),[AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,[Demographics] [xml](CONTENT [Person].[IndividualSurveySchemaCollection]) NULL,[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT [DF_Person_rowguid]  DEFAULT (newid()),[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Person_ModifiedDate]  DEFAULT (getdate()), CONSTRAINT [PK_Person_BusinessEntityID] PRIMARY KEY CLUSTERED ([BusinessEntityID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]END IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Person].[FK_Person_BusinessEntity_BusinessEntityID]') AND parent_object_id = OBJECT_ID(N'[Person].[Person]'))ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] FOREIGN KEY([BusinessEntityID])REFERENCES [Person].[BusinessEntity] ([BusinessEntityID]) IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[Person].[FK_Person_BusinessEntity_BusinessEntityID]') AND parent_object_id = OBJECT_ID(N'[Person].[Person]'))ALTER TABLE [Person].[Person] CHECK CONSTRAINT [FK_Person_BusinessEntity_BusinessEntityID] IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Person].[CK_Person_EmailPromotion]') AND parent_object_id = OBJECT_ID(N'[Person].[Person]'))ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [CK_Person_EmailPromotion] CHECK  (([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))) IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Person].[CK_Person_EmailPromotion]') AND parent_object_id = OBJECT_ID(N'[Person].[Person]'))ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_EmailPromotion] IF NOT EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Person].[CK_Person_PersonType]') AND parent_object_id = OBJECT_ID(N'[Person].[Person]'))ALTER TABLE [Person].[Person]  WITH CHECK ADD  CONSTRAINT [CK_Person_PersonType] CHECK  (([PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC'))) IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[Person].[CK_Person_PersonType]') AND parent_object_id = OBJECT_ID(N'[Person].[Person]'))ALTER TABLE [Person].[Person] CHECK CONSTRAINT [CK_Person_PersonType] GO

• 附件：

无论白猫黑猫，抓到老鼠是好猫:附是代码，由于scripter构造函数，为实现一次script一个对象并进行附加操作，借助

UrnCollection.Clear()

example:

 private void CREATE_TABLE(string sql)        {            string fp = Program.path_table + "\\" + DateTime.Now.ToString(format, new System.Globalization.CultureInfo("en-US")) + "_CREATE.sql";            int step = 0;            int max = 0;            SqlDataReader dr = db.ExecSqlDataReader(sql);            onRunSQLProcess(500, step, "begin：" + sql + "\r\n");            if (dr.HasRows)            {                //初始化一个连接                Server server = new Server(new ServerConnection(Program._AttribList["Data Source"], Program._AttribList["User ID"], Program._AttribList["Password"]));                //得到表                 Database dataBase = server.Databases[Program._AttribList["Initial Catalog"]];                //初始化Scripter                 Scripter scriptor = new Scripter();                scriptor.Server = server;//添加server属性                scriptor.Options.Add(ScriptOption.DriAllConstraints);                scriptor.Options.Add(ScriptOption.DriAllKeys);                scriptor.Options.Add(ScriptOption.Default);                scriptor.Options.Add(ScriptOption.ContinueScriptingOnError);                scriptor.Options.Add(ScriptOption.ConvertUserDefinedDataTypesToBaseType);                scriptor.Options.Add(ScriptOption.IncludeIfNotExists);                UrnCollection collection = new UrnCollection();                //得到表                 while (dr.Read())                {                    step++;                    if (step == 1)                        max = Convert.ToInt32(dr[0].ToString());                    string ksbm = dr[1].ToString().Trim();                    //string con = "IF OBJECT_ID('" + ksbm + "', 'U ')IS NOT NULL \n"                    //+ "DROP  TABLE  " + ksbm + " \n"                    //+ getEndFix();                    if (!Program.singlefile)                    {                        fp = Program.path_table + "\\" + DateTime.Now.ToString(format, new System.Globalization.CultureInfo("en-US")) + "_CREATE_" + ksbm + ".sql";                    }                    onRunSQLProcess(max, step, "storing:(" + Convert.ToString(step) + "/" + Convert.ToString(max) + ")" + ksbm);                    //DirFile.AppendText(fp, con);                    //------------------                     //You have to access schema tables this way.                     //db.Tables["test","mdm"]                    Table table = dataBase.Tables[dr[2].ToString().Trim(), dr[3].ToString().Trim()];                    collection.Add(table.Urn);                    StringCollection sqls = scriptor.Script(collection);                     foreach (var s in sqls)                    {                        DirFile.AppendText(fp, s + " \n");                        // System.//console.WriteLine(s);                    }                    collection.Clear();                    DirFile.AppendText(fp, "\n" + getEndFix());                }            }            dr.Close();        }

scripter 构造函数

同时自己可以尝试在powershell中去实现，网上好多大牛已经实现过了