用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'))
BEGIN
CREATE 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中去实现，网上好多大牛已经实现过了