{"id":3544,"date":"2009-06-29T00:06:21","date_gmt":"2009-06-29T00:06:21","guid":{"rendered":"https:\/\/blogs.msdn.microsoft.com\/webdev\/2009\/06\/29\/vs2010-beta1-web-application-project-database-package-and-smo-options\/"},"modified":"2009-06-29T00:06:21","modified_gmt":"2009-06-29T00:06:21","slug":"vs2010-beta1-web-application-project-database-package-and-smo-options","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/dotnet\/vs2010-beta1-web-application-project-database-package-and-smo-options\/","title":{"rendered":"VS2010 Beta1 Web Application Project Database package and SMO options"},"content":{"rendered":"

In Visual Studio 2010 Beta1 release, SQL server database schema and data can be packaged for deployment along with the website.  It utilizes Msdeploy SQL Database provider<\/a> functionality in IIS team\u2019s msdeploy release.  <\/p>\n

In Visual studio 2010 Beta1, user can set the database package options in web application project\u2019s Deploy-SQL property page as following.<\/p>\n

\"image\"<\/a><\/p>\n

User can add a package connection by clicking \u201cAdd\u201d button and make sure it\u2019s checked.  To select the source database for package, one can check \u201cPull data from an existing database\u201d check box, and select or enter the database connection string.  In Beta1, only three packaging choices are provided for the database, \u201cSchema only\u201d, \u201cComplete database\u201d and \u201cExclude objects with no schema information from the generated script\u201d (SMO option SchemaQualify).  <\/p>\n

User can also include their own SQL script file in the Database source Scripts list view as well.<\/p>\n

Msdeploy SQL Database provider<\/a> command line provides functionality to use many SMO options to package a database. In order to integrate this functionality to VS2010 Beta1, one can edit the project file directly.  The following sample shows the project settings for the default schema only database.  Note, the corresponding SMO options are set in PreSource tag of the corresponding connection list item.  Advanced user can specify additional SMO option in the project file to script their databases.  The additional SMO options will be preserved automatically even during property page operations. <\/p>\n

<<\/span>PublishDatabaseSettings<\/span>>\n  <<\/span>Objects<\/span>>\n    <<\/span>ObjectGroup <\/span>Name<\/span>=<\/span>"NewConnection1<\/span>" Order<\/span>=<\/span>"1<\/span>">\n      <<\/span>Destination <\/span>Path<\/span>=<\/span>"Data Source=myServerName%3bInitial Catalog=myDeployDBName%3bIntegrated Security=True<\/span>" \/>\n      <<\/span>Object <\/span>Type<\/span>=<\/span>"dbFullSql<\/span>">\n        <<\/span>PreSource <\/span>Path<\/span>=<\/span>"Data Source=myServerName%3bInitial Catalog=myDBName%3bIntegated Security=True<\/span>"
 ScriptSchema<\/span>=<\/span>"True<\/span>" ScriptData<\/span>=<\/span>"False<\/span>" SchemaQualify<\/span>=<\/span>"True<\/span>" \/>\n        <<\/span>Source <\/span>Path<\/span>=<\/span>"objDebugAutoScriptsNewConnection1_SchemaOnly.sql<\/span>" \/>\n      <\/<\/span>Object<\/span>>\n    <\/<\/span>ObjectGroup<\/span>>\n  <\/<\/span>Objects<\/span>>\n<\/<\/span>PublishDatabaseSettings<\/span>><\/span><\/pre>\n
<\/a><\/p>\n
In VS2010 Beta1, there are some known problems regarding the database packaging, including the following:<\/p>\n
1. transacted=false SMO option does not function as expected. (For certain database objects, such as full text catalog, full text index, user roles, transacted=false SMO option is required in order to be able to deploy successfully.)\n  
2. triggers using certain objects  maybe scripted before the objects is defined.  This is resolved with the April 2009\u2019s SQL server 2008 feature pack\u2019s management objects download<\/a>.<\/p>\n
3. SMO options supported is limited by Msdeploy SQL Database provider<\/a>\u2018s capacity.  Currently, it only support SMO option\u2019s Boolean values.<\/p>\n
In addition, we are still modifying the feature based on user\u2019s feedback.  Please contact us with your advices.  Thanks.<\/p>\n
Xinyang Qiu\n  
SDETII <\/p>\n
Visual Studio Web Tools<\/p>\n","protected":false},"excerpt":{"rendered":"
In Visual Studio 2010 Beta1 release, SQL server database schema and data can be packaged for deployment along with the website.  It utilizes Msdeploy SQL Database provider functionality in IIS team\u2019s msdeploy release.  In Visual studio 2010 Beta1, user can set the database package options in web application project\u2019s Deploy-SQL property page as following. User […]<\/p>\n","protected":false},"author":404,"featured_media":58792,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[197],"tags":[7341,7323,7329],"class_list":["post-3544","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-aspnet","tag-database","tag-visual-studio-2010","tag-web-deployment"],"acf":[],"blog_post_summary":"
In Visual Studio 2010 Beta1 release, SQL server database schema and data can be packaged for deployment along with the website.  It utilizes Msdeploy SQL Database provider functionality in IIS team\u2019s msdeploy release.  In Visual studio 2010 Beta1, user can set the database package options in web application project\u2019s Deploy-SQL property page as following. User […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/3544","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/users\/404"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/comments?post=3544"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/posts\/3544\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media\/58792"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/media?parent=3544"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/categories?post=3544"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/dotnet\/wp-json\/wp\/v2\/tags?post=3544"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}