Management Studio

You are currently browsing articles tagged Management Studio.

I’ve been using the SQL Server Management Studio (aka SSMS) since it was first released with SQL Server 2005.  And yet, it hasn’t stopped to surprise me.  Earlier this week I’ve discovered a feature which I will use frequently as of now!

While doing my day-time job at the customer, I have a habit of connecting to several servers each time when I open SSMS.  I always connect to them in the same order so that I can quickly locate them in the Object Explorer.

The servers I want open all the time are DEV DB server, DEV SSIS server, UAT DB server and UAT SSIS server.  The SSIS servers are needed to get quick access to the Job Agent, while the DB servers are what I use all the time to actually do my job.  To avoid confusion: on those SSIS servers, I’m connecting to the Database Engine, not the SSIS service.

So, earlier this week I was trying to find a method to easily connect to these servers with as few clicks as possible.  And guess what: I found one! (Well, otherwise I wouldn’t be writing this of course.)

In the following paragraphs I’ll describe a method which you can use to connect to several servers at once, with just two clicks!  I can tell you, it sure beats the Connect To Server popup window!

Screenshots taken using SQL Server 2012, but this should work as of 2005.

First we’ll create a group of all the servers that we’d like to connect to with just two clicks.

Open the Management Studio and switch to the Registered Servers view.  If you can’t find it, use the menu to select View > Registered Servers or hit CTRL+ALT+G on your keyboard.

Open the Registered Servers view

Right-click the Local Server Groups node located under Database Engine and select New Server Group… to create a new group.

Creating a new registered servers group

Give it a good name and click OK.

New Server Group Properties

Right-click the new group and select New Server Registration….

Enter the server credentials as appropriate.

New Server Registration

What’s important here, besides the obvious such as entering correct credentials, is the Registered server name.  The servers will be ordered alphabetically using that name, and that’s also the order in which they’ll be opened!  So if you’re like me and you want your servers to be opened in a specific order, you’ll need some naming creativity or use numbers.  In my case I’m glad that DEV orders alphabetically before UAT, which is what I want. Smile

Ow, in case you’re wondering about that server name shown in the screenshot above, the dot refers to localhost and sql2012 is the instance name.

To demonstrate that I’m not joking, I’ll now register a second server and name it “Another server”.  After clicking the Save button, here’s what the Registered Servers window displays:

"Another server" shown above "My DB Server"

As I told you, “Another server” is shown above “My DB Server” even though it was created later.

Now, what you’ve all been waiting for, how do I tell SSMS to connect to these servers?

Click number one is a right-click on the group name, My Favorite Servers in this case:

Use Object Explorer on the group name to open all servers in the group!

And click number two is the one on Object Explorer!

SSMS will now switch to the Object Explorer window and connect to the servers, in alphabetical order!

Object Explorer is connected to multiple=

How’s that for a time-saver huh?  I hope you’ll enjoy this as much as I will!

As a little extra, let’s quickly discuss one more feature of the Registered Servers window.  When you right-click a group, you also get a New Query menu item.  Clicking that will open a query window that’s connected to all the servers in the group.  You’ll be able to tell because the database dropdown in the menu reads <multiple>:

Query window connected to multiple=

And the status bar mentions the name of your server group and also <multiple>:

Query window connected to multiple=

Having a query window connected to several servers at once let’s you do interesting things, such as quickly checking what version and edition you’re running on all those servers:

Running the SELECT @@VERSION command on more than one server at once

Of course, I’m sure I don’t need to remind you but I will anyway, this means it’s actually a dangerous window as well.  In the database dropdown, you’ll get a list of all databases that are found on each server.  There’s no advanced logic used in the process of building that list: if the database name is found on all servers in the group then the name of the DB is added to the list.

You can select one of those databases and execute queries against them, just like this:

Running a query against more than one database over several servers

The status bar will now mention the actual database it’s connected to, which is actually its regular behavior.  And the messages pane will mention how many servers you’ve run the statement against.

In the case above, I created a table in two tempdbs on two servers.  Imagine that I ran a DROP TABLE instead, and I forgot that one of the servers in the group was the production server on which I didn’t want to drop that table.  So, be careful with those windows.  To avoid errors, close them as soon as you’ve finished the job for which you needed them open!

Have fun!

Valentino.

Share

Tags: , , ,

Here’s another silly one but it catches me every time.  So I decided to write a quick post about it, to never forget about it again!

Ever seen this warning message while using the Management Studio?

Warning: Saving changes is not permitted

All I did was open the AdventureWorks.Production.Product table in the designer, moved the ListPrice column above the StandardCost column and hit CTRL+S to save changes.  Instead of changing the table, it tells me:

Saving changes is not permitted.  The changes you have made require the following tabled to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

What a message huh?!  It needs to re-create the table, so what, go ahead!  Hang on, even though this is just a Warning, there’s no Continue button!  Ow come on, are you kidding?

Like any good developer that wants to get some work done, you stop reading such a long message after the second line and try to find a solution for your problem.  Too bad, you should have just continued reading the warning message because the solution is actually hidden in there!

If you open up the Options screen from the Tools menu in SSMS, and then drill down to the Designers > Table and Database Designers node, you’ll see an option called Prevent saving changes that require table re-creation.  And by default, that option is checked.  Remove the check from that box and you’ll be able to use the table designer as it was intended.

The "Prevent saving changes that require table re-creation" setting

So, here’s your ADSOTD (Annoying Default Setting Of The Day)!

Small note: be careful when you want to use this feature on a large table.  The script that gets generated in the background to make the changes needs to copy all records from the old table into the new one and that will obviously require some time!

Have fun!

Valentino.

Share

Tags: , ,

One is never too old to learn, right?  Here’s a Management Studio feature which has been introduced in SQL Server 2008.  And I’ve discovered it about a month ago.  Since then, I use it every day!

What am I talking about?  Well, the Management Studio allows you to link a color to a connection.  Each time when you open a window, the color of the status bar will change to the one linked to the connection to which your window is connected.  Still with me?  Alright, I’ll get the drawing board out.

To link a color to a connection, open up the Connect dropdown in the Object Explorer and choose Database Engine.

Object Explorer: Connect to Database Engine

That opens up the following familiar screen:

Connect to Server window

Do you see the Options button indicated with the red arrow?  Click it to open up additional options that you can set on your connection.

Connection Properties: Use custom color

To link a color to the connection specified in the Login page, activate the Use custom color checkbox and select a color.

Now click the Connect button and open up a new query window.  My favorite way of doing that is to open up the Databases tree node in the Object Explorer, select the database in which I’m interested, and hit CTRL+N.

With the new window open, did you notice the status bar?

Here you can see the status bars from two different query windows connected to two different instances on the same machine.

Purple status bar connected to SQL Server 2008 R2

Green status bar connected to SQL Server 2008

The way I use these colors is as follows:

  • Green for the servers in the Development environment
  • Orange for the servers in the Acceptance environment
  • Red for the servers in the Production environment

This gives you an extra safeguard to ensure that you’re executing that TRUNCATE TABLE statement on the right server.

However, as usual there are some things to take into consideration.

Things To Keep In Mind

localhost is not the same as <YOUR_MACHINE_NAME>

Be careful when you’re connecting to SQL Server instances on the local machine.  As the title above indicates: “localhost” and “BigBlue” are not the same (assuming that your PC is called BigBlue).  If you want to avoid trouble, set up the same color for both connections from the beginning.

Registered Servers

If you’ve got a habit of using the Registered Servers window, it’s important to know that the color specified here is completely separated from the color specified on the same connection through the Connect to Server window.

In fact, I believe all connection settings are set up separately when using this tool.

Change Connection On Open Window

Change Connection buttonBe careful when you use the Change Connection button on an open window: it messes up the coloring.  More precisely, it will keep the color of the previous connection.

There’s a bug filed on Microsoft Connect related to this, current status is Won’t Fix.  Which seems a bit weird: I noticed different behavior on SQL Server 2008 R2.  When changing my connection from SQL Server 2008 to R2, it would update the color to the one linked to R2.  In the other direction, disconnecting from R2 and connecting to SQL 2008, it would not change the color.

Update: I’ve found a couple additional bug reports on Connect related to this feature. If you’d like to see some consistent coloring behavior (and avoid the risk of executing a TRUNCATE TABLE on the wrong server), please take a moment to vote Yes at the following pages.

Connect: Update status bar colour when changing connections

Connect: [SSMS] Make color coding of query windows work all the time

Conclusion

Currently, to get consistent coloring all the time, you need to set up the colour three times for each connection: once in the Connect to Server window, secondly in the Connect to Database Engine window (this is the window that you get when clicking the Change Connection button), and thirdly in the Registered Servers pane (if you’re using this pane).

That’s it for now, have fun coloring those status bars!

Valentino.

Share

Tags: , , ,

Using the Management Studio (SSMS) I just received an error which I hadn’t seen before.  Here’s what I was doing.

On my freshly-installed Windows 7 64-bit machine I have both SQL Server 2008 SP1 and SQL Server 2008 R2 (NOV CTP) running (both 64-bit as well).  And I just installed the new Contoso data warehouse and OLAP DB.

After setting up the data source connection of the Contoso_Retail OLAP database and processing it successfully, the next logical step is to try and browse the cubes, through the Management Studio.  So that’s what I tried, but unfortunately it gave me the following error:

Invalid class string (Exception from HRESULT: 0x800401F3 (CO_E_CLASSSTRING)

Hmm, okay, that doesn’t look good!  “Invalid class string”, what does that mean??

So I tried the link that says “Click here to see detailed error information”, which gave me this:

Error pop-up: I don't see the details

I’m sorry, but where exactly are the details in this message?

Luckily a quick internet search gave me the solution (and reminded me of something which I actually knew but just didn’t think of because it’s all running in a totally new environment).

To browse cubes, SSMS is using a component from the “Microsoft Office 2003 Web Components” package, whatever that that means.

And the solution (as described here) is to:

  • Open the list of installed programs (Windows Key + R > appwiz.cpl > enter)
  • Locate the Office 2003 Web Components, right-click and select Change
  • Follow the wizard to repair/reinstall the components

Programs and Features

And now I am able to browse the cube without any issue!

Have fun!

Valentino.

Share

Tags: ,

Ever since I upgraded to SQL Server 2008 Service Pack 1 I noticed that the Management Studio was reporting incorrect version numbers when connected to Integration or Reporting Services.  This incorrect version number is located to the right of the server instance in the Object Explorer.

As usual, a picture says so much more than … :

Object Explorer showing wrong version numbers

As I have posted earlier, 10.0.2531 is the version number for SP1, while 10.0.1600 is the original RTM version number.

I never really spent time looking for an answer to this.  It was obviously a bug but I could live with it and someone else would probably already have filed it as being a bug.  So recently I came across a post by Phil Brammer that mentioned this issue.  This post got a comment from Matt Masson, a developer on the SSIS team.  Have a look at the comment but in short: the version numbers that are being shown in the Object Explorer are actually the version numbers of the service’s .exe file!  And SSMS is now showing the wrong number because these files didn’t get an update in SP1.

After a little search I found the bug report on Microsoft Connect, reported on March 11, 2009, by Dan English.  Its status is Fixed but it seems that it isn’t.  At least, looking at the comments, CU5 (Cumulative Update) for SQL Server 2008 SP1 is still showing the problem.  So I guess you could go over to the Connect page and click on that Yes button if you’re interested in seeing this fixed.  After all, it could be quite misleading to novice DB guys and gals…

On this same subject, there’s another interesting post by Adam W. Saxton, a member of the Microsoft SQL Server Escalation Services Team.  In this post he takes a closer look at the SQL Server 2008 Reporting Services version number after having installed CU2.

Conclusion: if you need to find out what version your server is running, do not rely on the version numbers that you see in the Object Explorer.  As Adam explained, one way is to look at the version numbers of the files that were included in the upgrade.  But that may a bit of an overkill.  My favorite way, assuming that all components of the SQL Server installation have been upgraded to the same version, is to use the following query:

SELECT @@VERSION;

 

On my machine that comes back with the following result:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86)   Mar 29 2009 10:27:29   Copyright (c) 1988-2008 Microsoft Corporation  Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

And remember, have fun!

Share

Tags: , , , , , , , ,

« Older entries

© 2008-2017 BI: Beer Intelligence? All Rights Reserved