SSRS 2005 Connection to DB Failing

Hello - Here's a little background:

I recently had a server crash; it had to be rebuilt.  Part of what that server did was host the SQL Server Reporting Services.  It connected to another server that had the report database.

When I reinstalled SQL Express 2005 on the server (including SSRS), I got to the configuration step of connecting to the remote database and I got an error stating that this edition of SSRS would not access a remote database.

So I decided to just install SSRS on the server that has the database.  I got further with this, but now I have another error.  Everything in the Configure Report Server dialog was successful with one problem.  In the Database Setup section, I got this error:
Database Connection ErrorI then try to Browse in IIS ReportServer and I get this:
IIS Browse ReportServerThe database existed prior to all of this and SSRS was working great.  Obviously I've installed a different edition of SSRS that's not compatible with the database.

I'm not very familiar with SQL, SSRS, or IIS, so I'm not really sure how to get the correct database / SSRS matchup.  Any assistance would be greatly appreciated.

I'm running:
Win Server 2003 SP2
SQL Server 2005 (9.0.3353)
IIS 6.0

I'm sure more information is required so please ask.

Jono
Jono MartinSystems AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
As you've noticed, you cannot use the Express (i.e. free) edition of SSRS to connect to another edition of the database engine.  You should find out what edition your database engine is and use that installation package to add SSRS to the server (after uninstalling the Express edition of SSRS).

To get an idea of what your database server is running, execute SELECT @@VERSION.  Somewhere in the middle of the string that's returned you'll see the edition of the database engine.
0
Jono MartinSystems AdministratorAuthor Commented:
Hi ValentinoV - Thanks for the response.

Here is the result of SELECT @@VERSION on the database:
Microsoft SQL Server 2005 - 9.00.3353.00 (Intel X86)   Aug 14 2009 18:07:44   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Here's the result of going to SQL Server BIDS > Help > About > SQL Server Reporting Services:
Microsoft SQL Server Reporting Services Designers
Version 9.00.3353.00

As I mentioned, I don't know a lot about SQL Server but it looks like they're the same version.  What am I missing?

Jono
0
ValentinoVBI ConsultantCommented:
Version and edition are two separate things.  The versions are indeed the same, but if you've used the installer of SQL Server 2005 Express edition (with advanced services most likely) to get SSRS installed then the editions are definitely different, which won't work.

Your database engine is Standard Edition, so you should find the Standard Edition DVD (or .iso) and use that setup to get SSRS installed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jono MartinSystems AdministratorAuthor Commented:
I have been looking most of the day for the disk or ISO, and I don't know where or if we have one.  I also went to the Microsoft licensing site and it doesn't show that we've ever purchased SQL Standard at all.  We may have installed an illegitimate version.

If I recreate this with SQL Express and create a new database, what will I lose by not using the Standard edition?  In other words, what does the Standard edition give me (regarding SSRS) that the Express edition doesn't?

Thanks again!

Jono
0
ValentinoVBI ConsultantCommented:
Have a look here for a feature comparison: http://msdn.microsoft.com/en-us/library/ms143761(v=sql.90).aspx

(scroll down to the header that reads "SQL Server 2005 Reporting Services Features")

There are actually quite a lot of limitations in the SSRS Express edition, looking at that feature list...  But then again, perhaps the available features are all that you used/needed in the past?  You'd of course be the better judge of that...
0
Jono MartinSystems AdministratorAuthor Commented:
OK, I have an ISO now and I'm running into a error that says this:
To change an existing instance of Microsoft SQL Server 2005 to a different edition of SQL Server 2005, you must run SQL Server 2005 Setup from the command prompt and include the SKUUPGRADE=1 parameter.
I tried to do some research on how to do that, but there are a TON of different parameters.  I tried just running this, but I came up with the same problem as running it from the ISO.
F:\Servers>setup.exe /SKUUPGRADE=1 /FEATURES=RS /INSTANCENAME={instancename}

Can you give me some direction on this?  I was thinking of uninstalling SQL altogether on here and starting fresh, but I CANNOT lost the databases that are already on this server.

1. If I uninstall SQL, will it uninstall the databases?
2. Once I install a fresh SQL server, how do I get the databases back into the new SQL installation?
3. Is there a better way to do this besides uninstalling and reinstalling?

Thanks,
Jono
0
Jono MartinSystems AdministratorAuthor Commented:
I have uninstalled SQL from the newly re-built server and installed SQL Server 2005 Standard, with the intention of setting up the sites on this server and connect to the other server.  I'm now getting these messages in Reporting Services Configuration Manager at the Database Setup section:
1st warning2nd warningThis is where I stand now, with no idea where to go next.  Any assistance would be greatly appreciated.

Thanks,
Jono
0
ValentinoVBI ConsultantCommented:
Sorry for not getting back sooner. Regarding your previous questions:

"1. If I uninstall SQL, will it uninstall the databases?"

No, the .MDF/.LDF files will remain where they are now. But or course it's always a good idea to make sure your backups are up-to-date before doing something like that!

2. Once I install a fresh SQL server, how do I get the databases back into the new SQL installation?

You can attach the existing .MDF or you can restore a backup.  Don't forget that you'll need to set up the logins and any other server configuration you might have.

3. Is there a better way to do this besides uninstalling and reinstalling?

Yes.  To get SSRS installed on your existing SQL Server it should be possible to do this using the "Add features to an existing installation" option in the installer. So not "upgrade existing installation", this is not really an upgrade.  Unless you were trying to upgrade the SSRS Express?  Not sure if/how that would work to be honest...

Regarding the error on your new installation: you need to make sure that you're running the same version patch level on both servers.  As far as I can tell, your DB server is currently running SP2 with some additional patches installed?  I'd try the following: install the latest service pack on your new SSRS installation and try to get it to work.  If you're still having issues, install that same service pack on your existing SQL installation.  with both on the same level you should be able to get it to work.
(don't forget to make sure your backups are up-to-date before upgrading the DB server)
More info: http://technet.microsoft.com/en-us/library/bb630446.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jono MartinSystems AdministratorAuthor Commented:
Hi ValentinoV -

Here's what I'm getting now; it seems contradictory to me, but it's more likely just my lack of understanding.  Thanks for sticking with me through this.

The Technet site you referenced says to "Start the Reporting Services Configuration manager. On the Report Server Status page, view the Product Version value and compare it to the Version Information table below in this topic."

I don't see the Product Version value on that page.  Here's a screenshot:
Report Server Status PageBut, I ran all of the MS updates on this server with included SQL Svr 2005 SP4.

Now, here is the result of SELECT @@VERSION on the database (on a different server):
Microsoft SQL Server 2005 - 9.00.3353.00 (Intel X86)   Aug 14 2009 18:07:44   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
And here's the result of going to (on this server) SQL Server BIDS > Help > About > SQL Server Reporting Services:
Microsoft SQL Server Reporting Services Designers
Version 9.00.5000.00
Version 9.00.5000.00 indicates SP4 (http://support.microsoft.com/kb/321185).

Here's where the confusion comes in for me.  When I get to the Database Setup section in Report Services Configuration Manager, all goes well except for this error:
Reporting Services DB Setup ErrorWhen I click on the link to show more information, I get a message telling me about a version mismatch:
"The found version is 'C.0.8.54'. The expected version is 'C.0.8.40'."
C.0.8.54 is SSRS SP2
C.0.8.40 is SSRS with no SP

If I'm on SP4 according to BIDS, why is the error telling me that it was expecting the database to be SSRS with no SP?

When I ran across this, I figured that SSRS was not necessarily updated with SQL svr 2005 SP4 and needed it's own SP.  I did a search for SSRS SPs but I didn't find anything like that beyond SSRS 2000.  Is SSRS 2005 supposed to be updated with SQL 2005 service packs?

Thanks again for your help.
0
ValentinoVBI ConsultantCommented:
SSRS is part of SQL Server, there are no separate SPs to upgrade only a part of a product.  Or in other words: to upgrade SSRS or any other component of SQL Server, you need the SQL Server service packs.

In my previous responses I was focusing on your initial issue and forgot to read the warning that you're now still getting.  I'm afraid it's related to your setup.  With the database on a different server, it falls into the "scale out deployment" category and that is not supported by Standard edition:

The Reporting Services edition must be Enterprise, Developer, or Evaluation. Standard edition does not support a scale-out deployment.
(ref. http://technet.microsoft.com/en-us/library/ms156453(v=sql.90).aspx)

Perhaps it's an option to create your report server databases on the DB engine on your SSRS server?
0
Jono MartinSystems AdministratorAuthor Commented:
Hi ValentinoV - I apologize for the delay in getting back here.  I've decided to contact Microsoft Support to have them do the upgrade on the database server.  I'll post the results here for the sake of completeness of this ticket, but I'll award points to you now and end this question.

Thank you for your assistance; you've been a great help.

Jono
0
Jono MartinSystems AdministratorAuthor Commented:
Though all of ValentinoV's comments were helpful, this comment included the most helpful information.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.