Minimum config to upgrade 2000 to 2005?

I am trying to upgrade from SQL Server 2000 Developer Edition (SP4) to SQL Server 2005 Developer Edition. I want to be able to use Reporting Services in the 2005 version. But the 2005 Upgrade Advisor says:

Reporting Services is either not installed or is installed incorrectly.
- Virtual directory settings do not match the default configuration
- Custom virtual directory settings block edition upgrades
- Custom extensions are deployed on the report server
- ASP.NET account information is encrypted

What do I need to install from the 2000 product in order to be able to upgrade to the 2005 version?

You should only get this warning if one of these conditions is true. Have you verfied each one?

Here is how to check:

1) Have you modified the IIS settings for either the Report or ReportServer vdir in any way?

2) Did you modify the config file to add any custom extensions?

3) Is your machine.config file set to encrypt the asp.net account password?

If you check all of this and still can't find the issue, you can always uninstall RS (this will still leave the RS catalog around), Install RS 2005. Use the 2005 configuration tool to point RS to the old DB and upgrade it to the new schema. The use that DB as the report server DB. You will then continue to maintain all of your old reports but will gain all the new features of RS 2005.

|||

Daniel, thanks for your help. I'm on the (very) low end of experience with all this.

>1) Have you modified the IIS settings for either the Report or ReportServer vdir in any way?

I uninstalled all SQL Server components (both 2005 and 2000) and then reinstalled 2000, 2000 SP4 and 2005. So, it seems like the answer here is no.

>2) Did you modify the config file to add any custom extensions?

I don't think so. Where should I look?

>3) Is your machine.config file set to encrypt the asp.net account password?

Where should I look for this file? (I guess the fact that I'm even asking this implies I have not messed with it ... but don't assume too much.)

>If you check all of this and still can't find the issue, you can always uninstall RS

I don't think it was ever installed in the first place.

|||It sounds like you are just trying to install RS2005. You do not need to install RS 2000 to get RS2005. Just install 2005.|||

Daniel Reib wrote:

If you check all of this and still can't find the issue, you can always uninstall RS (this will still leave the RS catalog around), Install RS 2005. Use the 2005 configuration tool to point RS to the old DB and upgrade it to the new schema. The use that DB as the report server DB. You will then continue to maintain all of your old reports but will gain all the new features of RS 2005.

I have a case where I need to upgrade both the DB and the RS from 2000 to 2005. The procedure above seem to work great except from one thing - I don't know how to transfer the encryption keys. I have made the keyfile with rskeymgmt but in the 2005 config screen i get an error when trying to restore the encryption keys:

ReportServicesConfigUI.WMIProvider.WMIProviderException: Could not find stored procedure 'SetKeysForInstallation'.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, String password)

Anyone knows what this means?

Regards,

|||This procedure was added for RS2005. It appears like you are still pointing to your RS 2000 db or it has not upgraded correctly.|||

Still pointing to the RS 2000 db, isn't that what you say we should do? And RS 2005 will upgrade the schema? That step reported no errors whatsoever..

|||When you point to the 2000 DB the config tool should tell you that it is upgrading. If it did not you can always script the upgrade (using the config tool) and then just run the scripts.|||

That step didn't report any errors, it worked out great. The only config step I still have problems with is Encryption Keys (which reports the error I posted earlier) and Initialization (reports an x in the icon, and I'm not able to click it. It's grayed out).

I tried the following now:

C:\Documents and Settings\Administrator>rskeymgmt -a -fdesktop\keys.snk -ppassword

Could not find stored procedure 'SetKeysForInstallation'.

C:\Documents and Settings\Administrator>

Which is basicly the same error as I posted earlier... any idea of what step I've missed that can cause this? I see that it's true.. there is no sp names SetKeysForInstallation in the db..

Minimum config to upgrade 2000 to 2005?

I am trying to upgrade from SQL Server 2000 Developer Edition (SP4) to SQL Server 2005 Developer Edition. I want to be able to use Reporting Services in the 2005 version. But the 2005 Upgrade Advisor says:

Reporting Services is either not installed or is installed incorrectly.
- Virtual directory settings do not match the default configuration
- Custom virtual directory settings block edition upgrades
- Custom extensions are deployed on the report server
- ASP.NET account information is encrypted

What do I need to install from the 2000 product in order to be able to upgrade to the 2005 version?

You should only get this warning if one of these conditions is true. Have you verfied each one?

Here is how to check:

1) Have you modified the IIS settings for either the Report or ReportServer vdir in any way?

2) Did you modify the config file to add any custom extensions?

3) Is your machine.config file set to encrypt the asp.net account password?

If you check all of this and still can't find the issue, you can always uninstall RS (this will still leave the RS catalog around), Install RS 2005. Use the 2005 configuration tool to point RS to the old DB and upgrade it to the new schema. The use that DB as the report server DB. You will then continue to maintain all of your old reports but will gain all the new features of RS 2005.

|||

Daniel, thanks for your help. I'm on the (very) low end of experience with all this.

>1) Have you modified the IIS settings for either the Report or ReportServer vdir in any way?

I uninstalled all SQL Server components (both 2005 and 2000) and then reinstalled 2000, 2000 SP4 and 2005. So, it seems like the answer here is no.

>2) Did you modify the config file to add any custom extensions?

I don't think so. Where should I look?

>3) Is your machine.config file set to encrypt the asp.net account password?

Where should I look for this file? (I guess the fact that I'm even asking this implies I have not messed with it ... but don't assume too much.)

>If you check all of this and still can't find the issue, you can always uninstall RS

I don't think it was ever installed in the first place.

|||It sounds like you are just trying to install RS2005. You do not need to install RS 2000 to get RS2005. Just install 2005.|||

Daniel Reib wrote:

If you check all of this and still can't find the issue, you can always uninstall RS (this will still leave the RS catalog around), Install RS 2005. Use the 2005 configuration tool to point RS to the old DB and upgrade it to the new schema. The use that DB as the report server DB. You will then continue to maintain all of your old reports but will gain all the new features of RS 2005.

I have a case where I need to upgrade both the DB and the RS from 2000 to 2005. The procedure above seem to work great except from one thing - I don't know how to transfer the encryption keys. I have made the keyfile with rskeymgmt but in the 2005 config screen i get an error when trying to restore the encryption keys:

ReportServicesConfigUI.WMIProvider.WMIProviderException: Could not find stored procedure 'SetKeysForInstallation'.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, String password)

Anyone knows what this means?

Regards,

|||This procedure was added for RS2005. It appears like you are still pointing to your RS 2000 db or it has not upgraded correctly.|||

Still pointing to the RS 2000 db, isn't that what you say we should do? And RS 2005 will upgrade the schema? That step reported no errors whatsoever..

|||When you point to the 2000 DB the config tool should tell you that it is upgrading. If it did not you can always script the upgrade (using the config tool) and then just run the scripts.|||

That step didn't report any errors, it worked out great. The only config step I still have problems with is Encryption Keys (which reports the error I posted earlier) and Initialization (reports an x in the icon, and I'm not able to click it. It's grayed out).

I tried the following now:

C:\Documents and Settings\Administrator>rskeymgmt -a -fdesktop\keys.snk -ppassword

Could not find stored procedure 'SetKeysForInstallation'.

C:\Documents and Settings\Administrator>

Which is basicly the same error as I posted earlier... any idea of what step I've missed that can cause this? I see that it's true.. there is no sp names SetKeysForInstallation in the db..

sql

Min(NULL) shows up as 12/30/1899 in cube

Hi!

I have a cube with a measure based on a datetime column with aggregation Min in AS 2000 SP4. The Data Type is set to Date and the Format is set to Short Date. Somehow the the measure shows 12/30/1899 when all values in the datetime column are NULL. If I check the properties in the cube browser it says Formatted Value: 12/30/1899 and Value: 12:00:00 AM.

I've implemented a work around in the reports that use this measure to look for 1899 or AM and replace with it NULL, but what I really want is the cube to display the real value (= NULL) when I browse it.

Any help would be greatly appreciated. Thanks!

Nulls don't behave the same in an OLAP database. In fact they often evaluate to 0 which is what is happening here.

In a relational DB 1 + NULL = NULL

In an OLAP DB 1 + NULL = 1

You might have to change your approach, so possible avenues to explore would be

use an aggregation of None and use a calculation to rollup the values, but this could be slow as you would need to drill down to the leaf level to get any values when using an aggregtion type of none. You might be able to attach the date as an attribute to another dimension, it depends what the date measure is.|||

So basically what I have to do is to create a calculated member based on this measure and use that one in the reports?:

iif(the_date_measure == 12/30/1899, NULL, iif(the_date_measure == 12:00:00 AM, NULL, the_date_measure )), which is the same thing I do in the reports.

I've verified it and a date column with NULL becomes 12/30/1899 in AS (in ver 2000 at least). So gotta use a work around as above to fix this.

|||

That would work too.

You could even put that type of calculation in the cube. If it were me I would hide the "raw" measure and create a calculated measure with this sort of logic in it.

|||

I've now created a calculated member as:

IIF( (InStr([Measures].[Adate], "00:00") > 0) OR (InStr([Measures].[Adate], "00.00") > 0), NULL, [Measures].[Adate])

and it seems to work very well. Looking for AM or 1899 does not work since the value will be 12:00:00 AM, 0:00:00, 00:00:00 depending on regional settings. I looked through all regional settings on my computer and only Faeroese and Italy have a dot separator instead of colon in their time setting (00.00.00).

Btw, do you know how date columns with NULL show up in AS 2005? Same behavior here?

Thanks for all your help!

|||Well you have some more options in AS2005, it is not really typical to use dates based measures so it is hard to say without knowing more details. The Min aggregation should behave the same, but you could also potential use something like LastNonEmpty or None with a calculation to do the rollup.