Solved

Upgrading SQL 2000 to SQL 2005

Posted on 2014-03-03
18
498 Views
Last Modified: 2014-03-04
Hey all. I'm in the process of starting an upgrade from SQL2000 to SQL2005. I ran the Upgrade Advisor like suggested and I keep receiving an error.

'Cannot use DSO (Decision Support Objects) to connect to Analysis Services, DSO is either not installed ot the installation is corrupted. Error details: Retrieving the COM class factory for component with CLSID {B492C386-0195-11D2-89BA-00C04FB9898D} failed due to the following error: 80040154.'

Any suggestions or ideas?
0
Comment
Question by:regsamp
  • 9
  • 5
  • 3
  • +1
18 Comments
 
LVL 17

Expert Comment

by:dbaSQL
Comment Utility
0
 

Author Comment

by:regsamp
Comment Utility
But there is not an option under " In Select Components, make sure that the Decision Support Objects component is selected" I don't see it listed at all.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
If I were you I would backup all the databases and logins and I would do an install for 2005 or even later version from scratch. If you still have 2000 it is possible that the OS is also outdated and you don't have some of the components needed.

With SQL 2000 servers still alive you either let them be and just use then as before or do a complete upgrade on a different machine with new OS and new SQL server.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
If you don't have Analysis Services installed, then on the first screen where you specify server name, uncheck checkbox "Analysis Services":screenshotIf you in fact have them installed, please clarify.
0
 

Author Comment

by:regsamp
Comment Utility
We need to upgrade our Intranet database so I believe I am going to do like you suggested. Make a back of the database. Copy it to a brand new server with a new OS and a newer version of SQL.

Do you know if SQL 2005 or 2008 can open a mdf from SQL 2000?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Yes, that is not an issue. You can restore them without problems. The other way is not possible, going from newer version to older version.

And yes, you could attach/detach the database files as well. However you would still need to run an update adviser to take care for deprecated syntax using *= or =*. You do that on existing 2000 server. You could do this later on the new server also if you know where to look. After you restore/reattach you should switch the compatibility level to the new version. You do that by right click on the database node > Properties > Options > Compatibility Level dropdown and change.
0
 

Author Comment

by:regsamp
Comment Utility
But I can't run the updated adviser. It errors out. I see. Or could I just make a backup of the current SQL 2000 datbase in an mdf format, copy it over to the new server with the new OS? Have SQL 2000 on the new server and then start to upgrade the new server to SQL 2005?
0
 

Author Comment

by:regsamp
Comment Utility
I also thought there was not a deprecated syntax issue going from SQL 2000 to SQL 2005? We do not have Analysis Services installed.
0
 
LVL 26

Assisted Solution

by:Zberteoc
Zberteoc earned 250 total points
Comment Utility
If you can't run the adviser then just do the backups. I am not sure what you mean by doing a backup in mdf format. There is no such thing to my knowledge. You have 2 choices:

1. Backup the databases om SQL 2000 server and restore them on the SQL 2005 server.
2. Detach databases on SQL 2000, move the mdf and log files to the new server, attach them.

I would recommend the option 1 because this way your original server stays intact, just in case. You can take care of eventual syntax incompatibilities, if any, later.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:regsamp
Comment Utility
Okay. I think option one is the best option as like you said, we will have the original server just in case. But I believe that there should not be a syntax issue going from 2000 to 2005? At least from what I read and heard. I know 2012 has the deprecated syntax issue
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
The article "ALTER DATABASE Compatibility Level (Transact-SQL)" has full list of issues. If you check "other versions", in version 2008 you will also find compatibility level 80, which is sql server 2000.
0
 

Author Comment

by:regsamp
Comment Utility
But where are these values set? I am sorry. This is new for me. Can we backup the databases on SQL 2000 server and restore them on the SQL 2005 server without deprecated syntax issues?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
Here's an example. One of the issues described is:

Compatibility-level setting of 90
"The FOR BROWSE clause is allowed (and ignored) in INSERT and SELECT INTO statements."


Compatibility-level setting of 100
The FOR BROWSE clause is not allowed in INSERT and SELECT INTO statements.

What this means is:
if in your old database you have anywhere FOR BROWSE  in INSERT and SELECT INTO statements, that code will not work in new server. To fix that, you have two choices:

1. review the code and rewrite
2. run the database in compatibility mode. In this case,  FOR BROWSE will be allowed and your code will run as before.

When you run Advisor, it scans all the code in the database and probably would find FOR BROWSE and tell you about this potential problem.

BUT. You may also have code in your client applications that work with the database. Of course the Advisor won't scan them. The ways to resolve that are the same 2 above. If you choose #1, it means that you have to review all your applications that work with this database, and ensure that they don't use any of the features that are different, and rewrite them if they do.

This was only an example, and it was about an issue between 90 and 100, while yours is 80. But it's the same for all issues: if your code, whether in the database, or in the application, is using a feature that has changed in new version, then either rewrite the code, or run the database in compatibility mode.
0
 

Author Comment

by:regsamp
Comment Utility
Okay. Thank you for the clarification. When we copy the SQL 2000 database to the new server running SQL 2005 we will run the analyzer but absolutely run the database in "compatibility mode" as we do not want to rewrite our code all over.
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 250 total points
Comment Utility
>we do not want to rewrite our code all over.

As you can see, sql server 2005 allowed compatibility mode 80; but 2012 already does not. Which means that if your code in app or server is using anything that has changed, compatibility mode 80 will save you in server 2005; but in server 2012 it's gone, so you will have to review the apps anyway when the time comes to drop 2005 same as you now drop 2000.

Predictably, in X years and Y new versions of sql server, compatibility mode 90 and 100 will be also gone.
0
 

Author Comment

by:regsamp
Comment Utility
I was seeing that. Do you know if 2008 will be okay? I think we are going to hold off on 2012  for a little bit as we do not want to deal will all that coding right now. Just getting the database over to a new server with a new OS, in SQL 2005, or 2008 without major issues will be great.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
Depends on many variables, chiefly, whether you have the developers or at least the sources of the apps you are using; whether those are your own apps or 3rd parties, and also how sophisticated those developers were in their writings. I.e. if they never thought about writing anything more complex than SELECT * FROM CUSTOMERS WHERE ID=33, then you are safe :-)
0
 

Author Comment

by:regsamp
Comment Utility
Okay. I guess we will have to just see.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now