Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Management Studio with SQL Server 2000

By Rob Farley,

SQL2005's Management Studio with SQL2000

Stuck in SQL2000 land

I've had SQL2005 installed on my local machine for some time. For a while, I just used a virtual machine for it, but when the release candidate came out, I installed it on my machine proper, and frequently try things out on SQL2005. I'm a developer rather than a DBA, and I happily try things out in SQL2005 when I see some way that I could use the new functionality. I now have several queries that have two versions - the one that I'm using on under SQL2000, and the one that I'll use when I get to use SQL2005 in production.

A compromise...

A while back, I was talking to the senior DBA where I work about some of the new SQL2005 things, and he mentioned that he loved using SQL2005's Management Studio. I asked him which of our customers he was using it for, and he said "All of them." Somehow, I had missed the fact that Management Studio works REALLY well with SQL2000 databases. So I started to do the same, and thought I'd write down some of my tips.

Main benefits...

Firstly, it really does work well. It feels like it's supposed to. I can even use it to edit DTS packages, because I have the Feature Pack installed, including the SQL Server 2000 DTS Designer Components. My queries run nicely, it's all the one window, instead of swapping between Enterprise Manager and Query Analyzer all the time, and that's great.

Object Explorer and Summary page

It's worth mentioning the Object Explorer and Summary page. The Object Explorer is a mix of the Object Browser within Query Analyzer and the Explorer part of Enterprise Manager. Now, the first thing that I did when I started using Management Studio was to tell it to start with an empty environment. I don't need it trying to connect anywhere before I've decided which particular server I want to connect to. But then I came to a situation where I wanted to delete about 5 tables, and I couldn't see how to do that easily in Management Studio. So I asked a friend, and he said "Use the Summary page". Doh! Of course. I pressed F7, up it popped, and it showed me everything I needed. Gives me lists of the things that are within the selected item in the Object Explorer. It's handy, but to be honest, I rarely use it. I would rather have a query window there, which is what I do!

Renaming registered servers

Object Explorer is really great, of course. I love being able to see the linked servers. I love the grouping of functions into table, scalar and system. The way that the windows can dock is great. Personally, I think I'm typically going to use the default layout, but it's great to have windows like the Registered Servers pane able to auto-hide when you don't want them. And speaking of Registered Servers - it's great to finally be able to change the display-name of servers. No more IP addresses or annoyingly named servers for me. I can call them useful things like "Test Server in Sydney" or "Production Server for client XYZ", or "DON'T USE THIS ONE, but if you really need to, then it's Jeremy's" - and Management Studio still does the friendly thing of putting the address/servername in brackets after my own description.

Go handled properly

Here's a thing I came across the other day which has won me over even more:

How many times do you have a bunch of code that you want to comment out in Query Analyzer, so you put a /* and later, put a */. Then you run it, and you find that there was a 'go' in there somewhere, giving you not only an error saying that it couldn't find the pairs of the comment brackets, but also running anything in between that you really didn't want to have run! Well, Management Studio is nicer than that. A 'go' statement surrounded by comment brackets is also ignored. So there's no need to highlight it all and do Ctrl-Shift-C (or Ctrl-K, Ctrl-C in Management Studio). Actually, there's another thing - commenting is now consistent with Visual Studio. This is a tremendous thing for anyone in the developer space. Probably not so great if you're a DBA, but I find it good. I do wish that Ctrl-Shift-C still worked, but only because there are times I hit it and find that nothing happened. And you can now undo comments! In Query Analyzer, I used to get really frustrated that I couldn't hit Ctrl-Z and undo my bulk-comment. But now, Ctrl-Z will undo a Ctrl-K, Ctrl-C. And I'm a much less frustrated coder.

use pubs
go
select * from authors
/*
select * from titles
go
select * from employee
*/
select * from jobs

Copying works better

How much did you hate copying stuff out of the grid in Query Analyzer. You'd copy it, paste it into another query, and automatically hit delete to remove the newline character that it put in there for you. Now, it doesn't put the newline in there. I know this could be annoying if you want it, but frankly, I don't! I like the fact that I can now paste a string in between two apostrophes, and not have to hit backspace immediately.

Movement

Finally, I want to point out that Management Studio understands words much better than Query Analyzer. If I use Ctrl-Left or Ctrl-Right to move through a string (let's say, a URL) in Query Analyzer, then it consider that each word ends at whitespace. Aargh! That used to kill me. But Management Studio will stop at each dot, hyphen, plus, you name it! Unless you name it 'underscore' - they get ignored, but that's fair enough, because underscores are different. I know that if you like the way that Query Analyzer handled these things, then you might get frustrated by Management Studio, but is there really anyone out there who will find that?

So my conclusion...

I have to recommend using Management Studio to anyone who uses SQL2000. If nothing else, you'll find yourself looking forward to when you get the clearance to use SQL2005 itself. But even if SQL2005 isn't on the horizon, you can use a much nicer interface to the SQL2000 databases you do have.

Total article views: 11884 | Views in the last 30 days: 7
 
Related Articles
FORUM

Cannot execute queries from Management Studio

Cannot execute queries from SQL Server 2008 Management Studio

FORUM

Microsoft SQL Server Management Studio Question

Microsoft SQL Server Management Studio Question

FORUM

Linked Server, show tables in SQL Server Management Studio

"Linked Server" Tables "SQL Server Management Studio"

BLOG

SQL Server 2008 Management Studio

I made the decision today to start using SQL Server 2008 Management Studio at work to manage my 2000...

ARTICLE

SQL Server Management Studio and SQL Azure

A new article from Steve Moore shows us how to use SQL Server Management Studio with your SQL Azure ...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones