July 31, 2013
  1. Aboutopenbve Data Publishing Studio App
  2. Aboutopenbve Data Publishing Studio Tutorial
  3. Aboutopenbve Data Publishing Studio Software
  4. Aboutopenbve Data Publishing Studio Design

Publishing your documents and applications from the RStudio IDE to RStudio Connect is extremely easy. Once you are happy with your work and ready to publish, simply click the 'Publish' button in the Code pane or the rendered document, and follow the steps in the deployment wizard. Attention: Please contact me in the bulletin board if you wish to remodel the route data of openBVE JR Yokohama Line. 注意:当 openBVE横浜線 の路線データを 改造して使用する際は揭示版まで連絡お願いします。. Get familiar with the RM application REST service and the various URLs needed in order to extract data from the RM application when you are using Rational Publishing Engine. To locate the information center for your version of the RM application, see the Rational DOORS Next Generation product documentation. The app is now associated with the store and Visual Studio has created two PFX signing certificate files for it, which you can see listed on the right (by default in the Solution Explorer) in Visual Studio. The one we need is the GameNameStoreKey.pfx: You should now right click on the file name and select the Properties option.

Publishing a Database Project

Visual Studio 2012 introduced a new SQL Server Database Project. This post first covers what happens when you publish a Database Project. It then details how to publish your Database Project.

See this link for an introduction to the Visual Studio 2012 SQL Server Database Project.

What Does Publish Do?

  • When publishing a Database Project, you define a connection and a database name specifying the 'target' of the publish operation.
  • The SQL Server Data Tools (SSDT) looks at the schema of the selected (target) database.
  • If the selected database does not exist, SSDT creates a database using the table, stored procedure, data, and other scripts in the Database Project.
  • If the database does exist, SSDT compares the selected database schema against the information in the DACPAC and builds an update script. It then runs the update script against the selected database.
  • In either case, the selected (target) database schema then matches the schema as defined by the Database Project.

WOW! When you think about this, it is amazing! Especially if you have been doing this manually. No more schema compares in an attempt to figure out the database changes. No more manually developed update scripts. No more issues from running an update script on a database that is a version behind.

With the new Database Project publish feature, each target database is separately compared and a unique update script is generated and executed to ensure *that* target database is updated to the desired schema.

There are a large number of publish settings that you can set to tailor this process. For example:

  • Block incremental deployment if data loss might occur.
    • If this setting is selected, a change defined in the generated update script may not execute successfully if the change could cause data loss.
    • For example, if changing a column from nvarchar(20) to nvarchar(15), data loss could occur.
    • If this setting is set, the column change will not be applied.
  • DROP objects in target by not in project.
    • If this setting is selected, any tables, stored procedures, or other database objects that are in the selected database but not in the project will be deleted.
    • For example, if you change the name of a stored procedure from CustRetrieve to CustomerRetrieve, the selected database will still have the CustRetrieve stored procedure in addition to the new one.
    • If this setting is set, then the CustRetrieve stored procedure will be deleted.

Now that you know what it does, let’s look at how to do it.

How Do I Publish?

To publish a Database Project:

1) Right click on the Database Project in Solution Explorer and select Publish.

2) A dialog appears allowing you to enter a publish profile, which are settings to use during the publish operation.

Qatar 2018 kitsempty spaces the blog. 3) Click the Edit button to specify the SQL Server instance you wish to publish to (target).

In this case, we are publishing to our development SQL Express database called ACM.

Publishing

4) The publish script name is defaulted, but you can change it. The publish script file is the file that SQL Server Data Tools (SSDT) create during the publish operation.

5) Click Create Profile to save the entered settings as a named profile. That way you won’t have to enter the settings again next time.

Saving a profile adds a publish.xml file into your Database project with the entered settings.

6) Click Publish to start the publishing process.

The publish processing displays in the Data Tools Operations window with links to view the script or results.

You can create any number of publish profiles. For example, you can create one to publish to your local development database, one to publish to a local test database, and one to publish to the master test database. Just be sure to give each one a clear name.

Double-click on a publish profile to open the Publish dialog using the selected profile.

If there is a publish profile that you use often, such as the profile you use to update your development database, there is a way to set a default.

To set a default publish profile:

1) Right-click on the publish profile file in Solution Explorer and select Set as Default Publish Profile.

2) Then when you select to publish your Database Project, it will default to the setting defined in your default profile:

Use the publish feature any time you want to update (or create) a target database using the database schema defined in your Database Project.

Enjoy!

By: Nai Biao Zhou Updated: 2018-05-11 Comments (2) Related: More >Database Administration


Problem

Aboutopenbve Data Publishing Studio App

With a Microsoft Visual Studio Database Project, we can use version control softwareto manage changes to databases and we may face these problems:

  1. The database project deployment failed, and the error message said“The schema update is terminating because data loss might occur”.
  2. A database object, for example a table, was removed in the project,but the object was still in the target database after a successful deployment.
Solution

When publishing a database project to the target database server using MicrosoftVisual Studio, we can solve these problems through the “Advanced Publish Setting”window. When deploying the dacpac file by using command “SqlPackage.exe”,we can solve these two problems with adding specific parameters.

The solution was tested with Microsoft Visual Studio Community 2017 on Windows10 Home 10.0 <X64>. The DBMS is Microsoft SQL Server 2017 Enterprise EvaluationEdition (64-bit). The sample data was retrieved from theAdventureWorks sample databases.

Add NOT NULL Columns to a Table

We have a staging table “[dbo].[Stage_Special_Offer]” in the databaseproject “DWH_ETL_STORE”. The following screenshot presented the tablestructure. The table has some data.

We added a new column “Offer_Description” with data type “NCARCHAR (50) NOTNULL”. Then we published the project by using the database project publishingwizard. We received this error message:

We found this comment in the “DWH_ETL_Store.publish.sql”:

/* The column [dbo].[Stage_Special_Offer].[Offer_Description] on table [dbo].[Stage_Special_Offer]must be added, but the column has no default value and does not allow NULL values.If the table contains data, the ALTER script will not work. To avoid this issue,you must either: add a default value to the column, mark it as allowing NULLvalues, or enable the generation of smart-defaults as a deployment option. */

Some developers may add a default value to the column. I do not think this isa preferable solution for a table in a data warehouse. A not NULL column usuallywas needed on the basis of business requirements. Adding a default value seems tobypass the requirement unless a business requirement asks to do this. In addition,ETL developers could not find a data integrity error immediately if some bugs inthe ETL process added a NULL value to the not NULL column.

Publish Table Changes by Enabling Smart Defaults with GUI

A preferable solution for a data warehouse table is to enable the generationof smart defaults as a deployment option, and then the ETL process ensures the dataintegrity and validation.

Here is the process to publish a database project with the generation of smartdefaults as a deployment option enabled.

Step 1

Right click the project name in the “Solution Explorer” window andselect “Publish” from the pop-up menu. Configure the “Target Database Settings”as follows, then click on the Advanced button.

Step 2

On the Advanced Publish Settings window, check the “Generate smart defaults,when applicable” checkbox. Then click the “OK” button. This gets us back to the above screen where we can use the Save Profile Asif we want to save these settings for next time.

Step 3

Then click the “Publish” button.

The following screenshot shows the data in the table after the database projectwas published successfully. The new column “Offer_Description” was addedwith empty values.

Publish Table Changes by Enabling Smart Defaults using Command Line

If we want to do this from the command line, we can use this command line syntaxto deploy the project with the command “SqlPackage.exe”.

'C:Program Files (x86)Microsoft SQL Server140DACbinSqlPackage.exe'/Action:Publish /SourceFile:'DWH_ETL_STORE.dacpac' /TargetConnectionString:'DataSource=IDEA-PC;Integrated Security=True;Initial Catalog=DWH_ETL_STORE;'/p:GenerateSmartDefaults=True

This shows the project was deployed successfully with these confirmation messages.

Alter Not NULL Columns in a Table

Sometimes we might receive the same “data loss” error when we changea name of a not NULL column. This might not always happen, it depends on how wechange the column name. If we change the name in the “T-SQL” panel, the error occurs.

To publish the project successfully, we need to change the not NULL column namein the “Design” panel. Note that other versions of Microsoft Visual Studio may providea “Rename” menu item in the “Refactor”context menu.

When we changed the column name in the “Design” panel, a “refactorlog”file “DWH_ETL_STORE.refactorlog”, was generated with these XML elements:

With this “DWH_ETL_STORE.refactorlog”, the publish scripts used “sp_rename”to rename the column, thus no data loss occurred.

A system table “[dbo].[__RefactorLog]”was created in the target database to trace the database refactoring.

Drop Objects In Target But Not In Source

We might find tables deleted from the database project are still in the targetdatabase. The database deployment could add new tables, but the deployment did notremove unused tables. We can solve this through the “Advanced Publish Settings”.

Check the checkbox “Drop objects in target but not in source”in the “Advanced Publish Settings” window as shown in the screenshot.This will remove objects that were deleted from the database project.

Here is the command line syntax to deploy the project with the command “SqlPackage.exe”.

'C:Program Files (x86)Microsoft SQL Server140DACbinSqlPackage.exe'/Action:Publish /SourceFile:'DWH_ETL_STORE.dacpac' /TargetConnectionString:'DataSource=IDEA-PC;Integrated Security=True;Initial Catalog=DWH_ETL_STORE;'/p:GenerateSmartDefaults=True /p:DropObjectsNotInSource=True /p:UnmodifiableObjectWarnings=False

References

[1] AdventureWorks sample databases.Retrieved March 2, 2018https://msdn.microsoft.com/en-us/library/ms124825(v=sql.100).aspx/.

[2] Walkthrough: Apply DatabaseRefactoring Techniques. Retrieved April 7, 2018https://msdn.microsoft.com/en-us/library/dd193272(v=vs.100).aspx/.

Next Steps
  • In practice, we usually use scripts to deploy a database project to targetservers automatically. This document aboutSqlPackage.exe is very helpful to construct the automation scripts. MicrosoftVisual Studio also provides a function to compare the schema between the databaseproject and the target database. We can use this function to ensure all of thenot NULL column name changes were captured in the “refactorlog”file. This file should be stored under the version control system.
  • Check out these related tips:

Last Updated: 2018-05-11



Aboutopenbve Data Publishing Studio Tutorial

About the author
Nai Biao Zhou is a Senior Software Developer with 20+ years of experience in software development, specializing in Data Warehousing, Business Intelligence, Data Mining and solution architecture design.
View all my tips

Aboutopenbve Data Publishing Studio Software


Aboutopenbve Data Publishing Studio Design


Coments are closed
Scroll to top