Using SQL 2000 for SSRS 2005 metadata storage



When setting up SQL Server Reporting Services 2005, can you use SQL 2000 as the host for the reportserver database? Yes, you can (with an obvious caveat in one scenario).


 


How?


 


Scenario 1:


  


SQL Server 2000 currently is not running SSRS, so it does not host the ReportServer database.


 


1. Run SQL Server 2005 setup, including the Reporting Services component.


 


2. Don’t choose to go with the “install default configuration” option of SSRS. Instead,


    choose the second option to manually configure SSRS after setup has completed.


 


3. Finish setup


 


4. Launch the Reporting Services Configuration tool, and choose the Databases Setup task.


 


5. Provide the name of your 2000 server and click the New button. When prompted, provide


   credentials necessary to create a database on SQL 2000…you’ll also need to provide the


   name of the database (ReportServer).


 


6. After the database has been created, use the lower portion of hte Database Setup screen to


   specify the credentials SSRS should use when connecting to the database in the future.


 


I used these steps with SQL Server 2000 SP3, and had no problems.


 


Scenario 2:


 


SQL Server 2000 is already hosting SSRS 2000 and/or is managing a ReportServer database created for SSRS 2000


 


Warning: Following these steps will “break” your SSRS 2000 report servers as we’ll upgrade the ReportServer database to a schema 2000 doesn’t undersand.


 


1. Repeat steps 1-4 as above.


 


2. Type in the name of the 2000 server and click Connect. Choose the ReportServer database


   from the drop-down list (or just click the Upgrade button).


 


3. When prompted whether you want to upgrade, choose Yes.


 


4. After the upgrade process has finished, complete step 6 as above. If you have upgraded a


   database that another SSRS server used to use, you may get warning messages around the


   fact that other encryption keys were detected in the database. Since our 2000 report servers


   can’t use this database now anyway, feel free to delete the existing keys.


 


Reminder: If/when you launch Report Manager from an SSRS 2000 server which used to use this database, you’ll get an exception which looks like this:


 


“The version of the report server database is either in a format that is not vaid, or it cannot be read. The found version is ‘C.0.8.40’. The expected version is ‘C.9.6.54.’. To continue, upgrade the version of the report server database and verify access rights”


 


 


 


 

Comments (28)

  1. I’ve seen tons of questions around if you can use various components of SRS 2000 with SSRS 2005…

  2. Jerry Bedell says:

    Finally, from your notes, I’ve found out how to upgrade my RS2000 to RS2005 with my SQL Server 2000.  My concern is, I own SQL Server 2000 but I do not own SQL Server 2005.  When I use the SQL Server 2005 setup, I won’t have to go out and purchase SQL Server 2005 will I?

  3. russch says:

    Yes, you need SQL 2005.

  4. Michael Lohss says:

    Will this also install SQL Server 2005 on the machine?  Will this try to upgrade a SQL Server 2000 instalnce if this is on the same machine as SSRS 2000?

    Thanks!

    Mike

    mlohss@gmail.com

  5. russch says:

    Yes, it will install 2005. That’s the point :)

  6. Don Stickle says:

    How can I ulitize the dataset in the code window in RSS 2000? I need to bring in the dataset and put into list and then have a function pass in data to run against this.

    Thanks,

    Don

    donstickle@fastmail.fm

  7. urpalshu says:

    Thank you Russch for this article.

    1. I Ran the Setup for SQLServer2005 Standard Edition for 32Bit.

    2. I Ran the Disk1 on my test server. It installed the SQL Server 2005 setup Support files.

    3.  In the SQl Server Setup Wizard, where I had to select the components to install.

    I selected SQL Server database services and Also Reporting Services. I hope I was right?

    4. Used a Domain user account and completed the installation.

    5. I followed your steps above.

    6. I have SQl Server 2000 SP4.

    7. I deleted the Encryption keys.

    8. I gave a new Report Server Virtual Directory.

    9. Checked the server status.

      Instance name: MSSQLSERVER

      Instance ID: MSSQL.1

    Initialised : Yes

    Status: Running.

    10. Then I went to Web Service Identity:

       Selected the default settings

       ASP.NET Server account: LocalSystem

       Report Server: DefaultAppPool

    11. When I check my server status:

       Initialized was set to No.

       Please help.

    12. When I tried to delete Encryptions again I got this error:

    ReportServicesConfigUI.WMIProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.

    —> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)

      — End of inner exception stack trace —

      at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)

      at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.DeleteEncryptedInformation()

    13. Under Services The RPC status reads as Started.

    Please can you help me resolve this issue.

    I installed the client components on my local server and it is all working fine. I need to deploy my reports or run it as a URL.

    Thank you,

  8. urpalshu says:

    Error: The Remote Procedure Call (RPC) service failed to start.

  9. russch says:

    You probably have a firewall issue which is blocking RPC traffic. If you have firewalls installed on either machine, turn them off and see if the messsage goes away.

  10. Puja says:

    Can I have SQL Reporting 2000 running on a 2000 instance & SQL Reporting 2005 running on a 2005 instance ON the SAME Server?

  11. russch says:

    Sure, but you’re going to need quite a bit of RAM on that machine.

  12. urpalshu says:

    Thank you very much,

    I have the client Components installed on my computer.

    And I have the server components instaled on my company’s test server.

    I have installed Reporting Serverices 2005 with SQl Server 2000 being the host.

    I generated some reports, and deployed them on the test server exactly how you have told.

    When I go to the URL of the test server. I can view  and run the reports.

    But my manager using a different computer cannot view the reports on the ReportServer.

    http://TESTSERVER/ReportServer

    Please could you tell me how I could resolve this. I want my manager to be able to access the reports generated by me.

    Please could you tell me where I need to be changing the security permission?

    Thank you,

  13. John says:

    Is it possible to set up a SQL 2005 server with SSRS 2005 on it and then link it to data from a SQL 2000 server?  I do have an SSRS 2000 setup on my SQL 2000, but I don’t want to upgrade and break the reports on there.  I’d rather be able to migrate them to the new server w/o taking down the current setup.

  14. russch says:

    John, I’m not 100% sure I understand your question, but I think you’re asking "Can I get SSRS 2005 to use my SSRS 2000 metadata database running on SQL 2000". The answer is No. You certainly CAN report against SQL 2000 data from a SSRS 2005, that’s no problem at all.

  15. jon says:

    When trying to create the database i get an error that says remote connections is not allowed for SQL Server…but I checked and it is.  Any ideas?

    THank you.

  16. TA says:

    I upgraded my reporting services from SQL server 2000 to 2005 after upgrading the SQL server itself. But I got some errors and after some researches I uninstalled the SSRS 2000 and reinstalled SSRS 2005. Everything seems fine at this point. However, I am having trouble copying data from SSRS 2000 tables (ReportServer & its temp table & log tables) to SSRS 2005 tables. I’m assuming by copying all these data I will have all my SSRS2000 reports available at SSRS 2005. Is this assumption correct? If so how to import the data (I’m running into duplicate records and index problem while using the DTS to do that import) correctly? Any ideas?

    Thank you for all your helps!!

    TA

  17. russch says:

    You should not attempt to copy meta data between copies of the ReportServer database. This probably won’t work anyway since the db schema has changed a bit between 2000 and 2005. I’d try upgrading again.

  18. i have been a while trying to know if i can run SQL reporting services 2005 over SQL server 2000 and…

  19. Rob says:

    I have a server running SQL 2000 and a separate web-server with IIS on it for my application. I want to install SSRS 2005. Do i need to install it on the web server and then point it at the SQL Server database or do i install RS directly on to the SQL server (which doesn’t have IIS on it)?

    Thanks

  20. russch says:

    Install on the web server, then point to the instance of SQL 2000

  21. Kanaka says:

    I have Sql Server 2005 and SSRS 2005 on a server. I have my web applications developed in vs2003 on my localhost. I had

    created all my reports in RS 2003. Now, I have changed the datasource to all my 2003 reports to the 2005 database and

    they are working well. I deployed them and I am able to run them from Report Manager.

    To run the reports from my local machine I had a sql server 2000 on my machine. I cant possibly have 2005 also on my

    machine. So how can I run the reports that are hosted on the remote server from my application on localhost. I have

    configured the web.config with database path, impersonation as administrator, the reporting services.asmx path.

    I get 401 : unauthorized error when I run the reports. Can you help me with this?

  22. russch says:

    Why don’t you just stick a frame into your application and have it request the reports which live on the remote box?

  23. BenR says:

    I have SQL Server 2000 in one server SSRS2005 to be installed on another server.  SQL Server 2000 will eventually be upgraded to SQL 2005.  

    For now, we are planning to use SSRS2005/VS2005 to create reports from data in the SQL 2000 server.  

    During installation, where should the ReportServer and ReportServerTemp databases be created, on the 2000 server or the 2005 server?  Does 1 have setup have more advantages than the other?  Any insight would help, thank you very much.

  24. Sérgio Gomes says:

    Does this works with Reporting Services in SQL Server 2005 Express Edition with Advanced Services.

    We are trying to use this Reporting Services to generate reports from SQL 2000 database with no success. Is get an error Connection to Data Source in Report Manager.

  25. Lim Ming Tat says:

    Do I need the license (I have the license for SQL 2000) of SQL 2005 to use SSRS 2005 Express Edition with Advanced Services?