SQLServerCentral / Discuss Content Posted by Boris Baliner / Article Discussions / Article Discussions by Author / SQL 2000 to SQL 2005: Where have all the old features gone? / Latest Posts

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

icata — Fri, 04 May 2007 09:08:00 GMT

i think the application runs better and has much more features, but from admin point of view - day by day tasks I sometimes miss the EM

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Markus — Fri, 04 May 2007 02:29:00 GMT

I don't know if it is THAT back to pick oracle over ss...

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

htt — Thu, 03 May 2007 19:04:00 GMT

is this time for new customers to pick Oracle instead of MS SQL?

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

icata — Thu, 03 May 2007 17:44:00 GMT

that would be quite true for rebuilding system databases

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

htt — Thu, 03 May 2007 11:02:00 GMT

so we are going backward using command lines instead of GUI? What a smart move by MS.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Philip Kelley — Mon, 30 Apr 2007 08:34:00 GMT

The original point of the article was essentially about using the SQL Server 2005 client tools to manage SQL Server 2000 instances. Based on my experiences of the past few months, I say that doing this is a big mistake.

The SQL Server 2000 client tools were for all intents and purposes "second generation" -- 2.0 versions, revamped and revised from SQL Server 7.0. From what little I've heard, the SQL Server 2005 client tools were rewritten from the ground up in .Net 2.0 (which some say explains why they're so darn slow). Thus: on the one hand, no way could they recreate *everything* in their brand-new rewrite on the first go; on the other, maybe the original (C++?) coders cashed in their stock options, and MS had to recruit a fresh batch of college students.

[I'll site the interview by Rick Chapman with Joel Spolsky that he included in the back of his (Champamn's) book "In Search of Stupidity" for why the "rebuild from the gound up" trick is so very dangerous. Can't find it online, alas.]

Anyway, my point and strong recommendation is this: manage SQL 2005 instances with the SQL 2005 client tools, and manage the SQL 2000 instances with the SQL 2000 client tools. It makes no sense to manage 2000 in 2005 *if* you have no 2005 instances to support as well. (And if I did, I'd have both sets of client tools available, if only to maintain my sanity.)

Philip

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

David Lu — Fri, 27 Apr 2007 14:49:00 GMT

100% agree. Event after using sql 2005 for around 8 months, still missing those 'lost features'.

Probably, some R&D people in MS decided to take them off since they are just from schools? just kidding

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Mark Marinovic — Fri, 27 Apr 2007 14:41:00 GMT

Thankfully, SP2 (perhaps SP1 as well) has a host of reports that mimic the old TaskPad (which I used extensively). Just right-click on the database name, select 'Reports' --> Standard Reports. There are 17 to choose from, they're actually pretty nice. We (SQL Server community as a whole) must've got someone's ear at MS.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Alex A-400810 — Fri, 27 Apr 2007 10:21:00 GMT

Is this article joke? Everything mentioned exists in SQL 2000 ...xp_fixeddrives backupset ,restorehistory etc..I used those features since 2000 came out.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

icata — Fri, 27 Apr 2007 08:57:00 GMT

I miss seeing easyly when a job had failed or a database been marked suspect because datafiles wasn't available

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Markus — Fri, 27 Apr 2007 08:14:00 GMT

I have a list of complaints about Mgt. Studio vs. Ent. Manager, ones that people have not addressed...

Can no longer grant a user access to a list of tables in one GUI screen. Each table grant is a seperate task.

I really miss taskpad. I liked the mdf/ldf allocated and used in your face so you can see what part of the logspace is being used and not used.

Simple easy naviation is gone.

Query tool install as a stanalone product. Some developers only need to query a table from time to time. Why do I have to install ALL of this bulky stuff and give them a lesson on how to simply log in and be able to run one select statement. Why can't I simply install a Query Analyzer on a persons PC that only needs to run a query once in a while. We have tons of these type situations here.

Why is using the import / export wizard to simply move data from a table so bulky. After the package is created it is not easily viewable and understandable what that package does.

Granted, there are some improvements in Mgt. Studio, however, I have never see so many features removed for a product upgrade.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

David McKinney — Fri, 27 Apr 2007 04:05:00 GMT

Where have all the new articles gone?

It's a good article and an interesting subject....but it's a repeat!

Also, since its original publication, we've had SP2, so some of the comments (notably about scripting) are no longer valid.

Shouldn't this be in the One Year Ago section? (Or is it too old for that?)

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Phil Factor — Fri, 27 Apr 2007 01:59:00 GMT

It is in SSMS Express that Microsoft took out the addins and extensibility. One can only guess that Microsoft saw it as an easy way of differentiating the two products, in the hope that you'll want to upgrade. It makes it more awkward for any provider of Add-ins like SQL Refactorer because of the need to explain to customers, and those trying them out, why the product won't work on certain versions of SSMS.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

James Hebben — Fri, 27 Apr 2007 01:30:00 GMT

Given that SSMS is a rework of the Visual Studio, why oh why did MS turn off all the good stuff? Specifically, all the Addin and extensibility bits. If they had left this in then, as a community, I am sure we could put all the "missing features" back in.

RedGate seem to have hacked addins into SSMS so perhaps there is hope for us yet...

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

dma333 — Fri, 23 Mar 2007 05:47:00 GMT

Well intended, but remember we're...we're not supposed to query those good old sys tables anymore (sysfiles)! There's another sys. view that replaced that, but I'll have to find that and post again.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

slsmithstone — Fri, 16 Mar 2007 06:32:00 GMT

Very interesting article.

I too miss the ability to copy stored procedures! One has to wonder if they simply forgot about it . . .

I am somewhat surprised no one is mentioning how screwed up Diagrams are. Why didn't they just leave them alone???

To the person complaining about 2005 being too slow -- I put more memory in one of my PCs and that perked it up a bit.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Kostas Stefanou — Fri, 16 Mar 2007 03:42:00 GMT

Very interesting subject.I have a couple of questions. 1. I am wondering what happened to the feature that you could select multiple objects to script and select the output to one file per object. 2. When I choose windows ANSI format in sql2k, comments in greek were scripted OK. In SSMS are not.Is there any fix?

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Paul Paiva — Fri, 27 Oct 2006 10:58:00 GMT

I miss the ability to play a given .wav file when a query finishes. In SSMS we only have the option to play the Windows default beep.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

icata — Fri, 27 Oct 2006 08:17:00 GMT

I couldn't find a way to right click on a database, back it up and in the same time schedule that backup. It helped me a lot in 2k to schedule databases backup.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

J.T. Shyman — Tue, 06 Jun 2006 07:01:00 GMT

If this really were true then most of the backwards compatiblity would be gone. You can connect to SQL 2000 servers using SSMS and, by and large, manage them pretty well. If this were a new product that wouldn't exist.Then again, Microsoft would lose a lot of marketshare if they did that. Yes, it is a new product but part of me believes the team for SQL2005 made some design decisions in a vacuum.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

J.T. Shyman — Tue, 06 Jun 2006 06:59:00 GMT

This is easy. Open the Registered Servers window in SSMSRight-click on "Database Engine" and choose "Previously Registered Servers"It will ask you some questions and pull in any SEM registered servers you have on the same computer.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Phil Ekins — Fri, 31 Mar 2006 09:33:00 GMT

Has anybody found an easy way to migrate registered servers between 2K and 2005 ? - i have a couple of hundred servers plus groups and i am not looking forward to setting them up by hand...

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

TF-245998 — Thu, 16 Mar 2006 11:49:00 GMT

Nice article.

External tools also don't work the same in sql2k5. Managing 100+ sql server environments, using the [SRV] parameter in enterprise manager's external tools is handy for accessing explorer, server manager & event viewers on current sql server environments. Even though sql2005 books online says it works the same, it doesn't work in management studio.

Polling is controllable in sql 2k enterprise manager, but no control in sql2k5 management studio. With several DBAs having management studio open with 100+ servers registered = a lot of clutter in security event logs, unneccessary network traffic & overhead on workstations.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Bikeman — Thu, 16 Mar 2006 10:54:00 GMT

I'm the gui one, will benifit from it the most I guess

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Bikeman — Thu, 16 Mar 2006 10:49:00 GMT

John, I was talking about connecting to sql 2000, in which case sys tables are still there. U r right, in 2005 you'd want to use DMVs.

Just to nitpick a bit (it was a nice article), but last I heard the systables are being depricated, as well, in favor of the system views. As such you would want to do away with the calls to sysobjects:SELECT name FROM sysobjectsshould beSELECT name FROM sys.objectsand Select crdate as DateCreatedFrom dbo.sysobjects should beSelect create_date as DateCreatedFrom sys.objects

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Bikeman — Thu, 16 Mar 2006 10:03:00 GMT

Alex, I certainly agree that SQL 2005 is a new product, and a shift in paradigm. I even agree, at the risk of being strangled by other dbas, with doing away with some of the old commands and features that arn't terribly useful. I guess what I was trying to get at is: why take away something that's useful, and not replace it with anything? Like bookmark, for example... But that's another story

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

John Laska — Thu, 16 Mar 2006 09:55:00 GMT

Just to nitpick a bit (it was a nice article), but last I heard the systables are being depricated, as well, in favor of the system views. As such you would want to do away with the calls to sysobjects:SELECT name FROM sysobjectsshould beSELECT name FROM sys.objectsand Select crdate as DateCreated From dbo.sysobjects should beSelect create_date as DateCreated From sys.objects

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Kevin O'Donovan — Thu, 16 Mar 2006 08:25:00 GMT

> Never mind the features, why is SQL Management Studio so pathetically slow! > Even if you put the -nosplash switch on the SQLWB.EXE it takes an age to load.Is it just loading you find is slow? I've found actual interaction with database to be faster, in particular things like right clicking on a server to select connect from the context menu - in enterprise manager you would be waiting quite a few seconds beofore the context menu appeared (presumably it was interrogating the server??!?), but in SSMS the menu is there straight away. Other operations seem to be as fast or faster.> How the hell do you show the contents of a system stored procedure? Am I missing something? Don't you just locate it under the system stored procedures subfolder of stored procedures, and right click on it and select Modify? Bit of a misnomer if all you want to do is look at it, but it still has the same effect?Does anyone know how to add a column to a table in a merge publication? You used to bring up the properties of the publication, go to filter columns, find the table and click the add column button, but filter columns seems to have disappeared. Can obviously do it via sp_repladdcolumn, but it would be nice to have the option to do it via the GUI as well.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

richardd — Thu, 16 Mar 2006 08:06:00 GMT

Apparently, the first (and only) CTP of SQL 2005 SP1 has just been posted:http://blogs.msdn.com/euanga/archive/2006/03/16/552349.aspx

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Ion Freeman — Thu, 16 Mar 2006 07:40:00 GMT

"improve they're typing skills"Not to nitpick, but that's a pretty funny clause to have a typo in.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Loner — Thu, 16 Mar 2006 07:39:00 GMT

Alex

I agreed with you. SQL Server 2005 is another product. I feel liked I have to learn SQL Server all over again.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

noeld — Thu, 16 Mar 2006 07:22:00 GMT

How the hell do you show the contents of a system stored procedure? I know sp_helptext but there MUST be a better way than that!

SELECT OBJECT_DEFINITION (OBJECT_ID ('sys.sp_help') )

I suffered this one too

Cheers,

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

TDuffy — Thu, 16 Mar 2006 07:21:00 GMT

Some good new features, some missing old friends...what can you do. Do register your comments with MS and maybe somethings will be restored.

You will be upgraded. Resistance is futile. (Bill Gates)

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Alex Grinberg-230981 — Thu, 16 Mar 2006 07:03:00 GMT

I would like to emphasis that SQL 2005 is another product versus just another version of SQL 2000. On the surface it may seem some of the features have disappeared in the product, but in actuality, it provides the DBAs with more flexibility. For instance, there is an extended stored procedure facility that allows DBAs to write his own sprocs that would enhance the current xp_fixeddrives routine currently provided by today's version of SQL. SQL 2005 also has the additional benefit of allowing the programmer or DBA wrote routines using VB.NET or C#.

In case to replace xp_fixeddrives just use scripting runtime library class Drive, which has property FreeSpace.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

G Bryant McClellan — Thu, 16 Mar 2006 06:06:00 GMT

This one is pretty minor, but since the thread is managing SQL2K with SQL2005 tools...When you look at legacy maintenance plan history, SQL2005 cannot sort the history records by date. It implies that it can because clicking on the End Date heading changes the display, but the date is sorted as text! Consequently, 10 March is after 1 March but BEFORE 2 March. Hey Microsoft, this makes sense because...?

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

Ryan-209402 — Thu, 16 Mar 2006 06:05:00 GMT

Great article. I'm glad that I am not the only one who is missing these features. I especially miss the ability to DTS objects, such as SPs, from one db to another. That feature is now completely gone in SSIS! I don't see any reason why MS should not add it back in sp1.

Please MS, give us an easy way to script or copy stored procedures because some organizations rely on low tech admins to perform such tasks. It used to be available in the SQL 2000 DTS wizard and was called "Copy Objects and Data Between Servers" but now that functionality is completely gone in SSIS. It supposedly can still be done by creating a SSIS project in Visual Studio but that is too much for your average admin.

You're right, I think Advil's stock is going to go up as a direct result of this.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

richardd — Thu, 16 Mar 2006 05:22:00 GMT

Some other problems using SSMS with a SQL 2000 instance:

You can't view or modify multi-statement TVFs. If you try, you get an error message indicating that you don't have permission to access the object, even if you are the dbo.
You can't view or modify full-text indexing properties. This produces a very similar error message.
You no longer have the option to see a list of permissions for a user. Instead, you have to select the specific objects, and then select each object in the list to see the associated permissions.

The first two are apparently fixed in SP1, but there doesn't seem to be any indication of when we can expect to see it. The only current workaround is to re-install the SQL 2000 tools.

To work around the third problem, you can use the sp_helprotect procedure. However, this doesn't provide any simple way to see the list of objects which a user doesn't have access to.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

G.R.Prithiviraj Kulasingham — Thu, 16 Mar 2006 05:07:00 GMT

It is a great surpise to see that in sql2k5 we have lost the fucntionality to script a set of objects based on the settings i want. In sql2k, I can select a few objects (based on name, owner, created data or even randomly) and create the script. then i will be presented with the option of what to script (whether to have drop and create, script indexes, permissions etc) It is no more there.

RE: SQL 2000 to SQL 2005: Where have all the old features gone?

David.Poole — Thu, 16 Mar 2006 04:39:00 GMT

Never mind the features, why is SQL Management Studio so pathetically slow!Even if you put the -nosplash switch on the SQLWB.EXE it takes an age to load.There are bits I like, or there would be if I could get to them before the next ice age.How the hell do you show the contents of a system stored procedure? I know sp_helptext but there MUST be a better way than that!

SQL 2000 to SQL 2005: Where have all the old features gone?

Bikeman — Sat, 18 Feb 2006 09:48:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bBaliner/sql2000tosql2005wherehavealltheoldfeaturesgone.asp