• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 526
  • Last Modified:

Upgrading SQL 2000 to SQL 2005

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
regsamp
Asked:
regsamp
  • 9
  • 5
  • 3
  • +1
2 Solutions
 
regsampAuthor Commented:
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
 
ZberteocCommented:
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Vadim RappCommented:
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
 
regsampAuthor Commented:
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
 
ZberteocCommented:
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
 
regsampAuthor Commented:
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
 
regsampAuthor Commented:
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
 
ZberteocCommented:
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
 
regsampAuthor Commented:
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
 
Vadim RappCommented:
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
 
regsampAuthor Commented:
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
 
Vadim RappCommented:
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
 
regsampAuthor Commented:
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
 
Vadim RappCommented:
>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
 
regsampAuthor Commented:
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
 
Vadim RappCommented:
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
 
regsampAuthor Commented:
Okay. I guess we will have to just see.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 9
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now