SSMS Archives » SQLErudition.com

Gotcha – SSIS ImportExport Wizard Can Kill Your Diagrams

April 28, 2015Aalam Rangi1 Comment

Some things are meant to be learnt the hard way. And that is how I learnt about today’s gotcha.

Overview of [sysdiagrams]

I have been working on an ERD (Entity Relationship Diagram) recently and used the Database Diagram feature in SSMS for this purpose. When you try to create a diagram for the first time in a database, a message box asks you if you would like to create diagramming objects.

Confirmation dialog — Image 1 (Click to enlarge)

On clicking Yes, a system table by the name of [sysdiagrams] is created in the same database that you are creating the diagram in. The diagrams are stored in this table. SSMS shows the diagram in the Database Diagrams node of the Object Explorer tree view.

Diagram and its table — Image 2 (Click to enlarge)

The Scenario

I was creating my diagram in a test environment. At some point I had to refresh all data from the production environment. The easiest way for me to do a full refresh is to use the Import and Export Wizard. The wizard can be launched either via SSMS context menu or in the Business Intelligence Developer Studio as an SSIS project. As usual in the case of quick data refreshes, I selected all tables using the top-left Source checkbox in the wizard, and chose the options to delete and reinsert all rows with identity values.

Select all tables and reinsert rows — Image 3 (Click to enlarge)

When the wizard ran successfully, my database diagram at the destination test system was missing!

Gotcha

Upon some research and trials I found that that if the diagramming capabilities in the source and destination servers are enabled, the wizard includes the [sysdiagrams] table automatically in the list of tables to refresh. As you can see, there are no other system tables in the wizard except the [sysdiagrams] table so it is easy to miss it in a long list.

sysdiagrams is included — Image 4 (Click to enlarge)

So in my case, all data in the destination [sysdiagrams] table was deleted. There were no diagrams at the source so nothing was imported for this table. This outcome would have been the same with the drop and recreate option too because the destination table would have been recreated.

Conclusion

One needs to be careful while using the Import and Export Wizard. Uncheck this table in the selection list to preserve the diagrams at destination.

How To Connect SSMS to ALWAYSON Read-Only Secondary Database

February 16, 2015Aalam Rangi4 Comments

The databases in PRIMARY availability group can be used for read-write access. The databases in the SECONDARY availability group can be used just for read-only access.

An attempt to connect to a SECONDARY availability group database with a normal connection, which is read-write by default, shows the following error message –

Msg 978, Level 14, State 1, Line 1

The target database ('AGDemoDB') is in an
availability group and is currently accessible
for connections when the application intent is
set to read only. For more information about
application intent, see SQL Server Books Online.

To resolve the issue, the connection string needs to have the Application Intent = ReadOnly parameter. How do you pass parameters in a SSMS connection?

SSMS has many options that are not too obvious. One of them is to provide additional connection parameter options. All that is needed to resolve the above error is to use the Additional Connection Parameters screen in the connection dialog and put the parameter there.

The keyword should not have any spaces.

Further reading:

The AlwaysOn Professional MSDN blog has more examples of connection strings for various applications.

You may also like to review the Application Intent Filtering feature of AlwaysOn at this and this link.

How to Activate Microsoft SQL Server 2008 R2 180-day Evaluation Version After Expiration with a Product Key (Step-by-step)

December 4, 2012Aalam Rangi2 Comments

The trial period of the Microsoft SQL Server 2008 R2 180-day Evaluation version had come to an end on my laptop and the SQL Server had stopped working. I had bought the Developer Edition media with product key and wanted to use that to have the SQL Server running again. The following are the step-by-step screenshots of the entire activation experience.

Click on images to enlarge.

Following is the error message that comes up while trying to start the SQL Server Management Studio on an expired SQL Server 2008 R2 showing that the trial period has expired.

I have Windows 7 Home Premium (64-bit) running on Intel Core 2 Duo 2.2 GHz, 4 GB RAM. To begin the activation, go to Start > All Programs > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Installation Center (64-bit). Go to Maintenance and click on Edition Upgrade.

An in-progress message appears briefly.

A setup rule verification window appears and shows the progress while the rules are verified. Each status message can be checked by clicking on the link. A summary of all the rules is also presented in HTML format. The path for the HTML file is mentioned at the end of the blog post. Wait for the setup to verify the rules to and click OK.

Another set of rules are verified. Wait for this to complete and click Next.

The next screen presents an option to enter the product key. Enter the key and click Next.

Read and accept the license terms by checking the checkbox. Click Next.

Select the SQL Server instance from the dropdown and click Next.

One more set of rules are verified. After completion click on Next. The next window shows a summary for review and a confirmation that the setup is ready to upgrade the edition. Click on Upgrade. The Upgrade button will be disabled but there will not be any other activity indication like a hour-glass cursor or a progress bar. This could be a little puzzling but wait for it to complete. It took about 4 minutes to complete on my laptop. Your mileage could vary.

The final screen shows a completion message and the path of the setup log file. Click on the log file path to open it. Click OK on the setup window to close it.

HTML report of the setup rules:
C:Program FilesMicrosoft SQL Server100Setup BootstrapLogYYYYMMDD_HHMISSSystemConfigurationCheck_Report.htm

Text log files:
C:Program FilesMicrosoft SQL Server100Setup BootstrapLog
and its child folders.

How To Have a Colorful Status Bar in SSMS. And Why is that Useful!

April 27, 2012Aalam Rangi1 Comment

Would it be helpful if the status bar of SSMS query windows for PROD environment were red, QA were green and DEV were blue? Yes. The query windows will be distinguished and reduce the chances of oops moments. If you frequently open simultaneous query windows for multiple environments/servers in SSMS, then you must learn about this SSMS feature. Don’t miss reading a word of caution at the end!

The following is the default status bar for a query window in SSMS.

The status bar can be made Green (or any other color) like the example below.

All you need to do is to expand the Options on the connection dialog …

… and pick a color of your liking as shown below.

These color choices will persist even when you close and reopen the SSMS.

Although, setting up a different color for all individual servers is possible but having too many colors could be confusing! You might find it useful to set one specific color per environment. For example, Red for PROD servers, Green for QA servers and Yellow for TEST servers.

How to Reset it Back to Default Color:

Open the Connect to Database Engine dialog box and remove the check for the Use Custom Color checkbox. Click on Connect. All new query windows for the server you are trying to connect will revert back to the default color. Any open query windows for that server or others will not be affected. I’m not aware of any option to reset this for all connections at once.

A Word of Caution:

A new color selection is applied to the query window immediately but the effect of removing a color is visible only with new query windows, not the ones currently open. Whenever you change the connection of any open query window, always verify that the expected color is showing on the status bar.