Solved

SSRS 2005 Connection to DB Failing

Posted on 2012-04-10
14
677 Views
Last Modified: 2012-04-24
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
0
Comment
Question by:Jono Martin
  • 7
  • 5
14 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37831263
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
 

Author Comment

by:Jono Martin
ID: 37832522
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37832547
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
 

Author Comment

by:Jono Martin
ID: 37834363
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37834500
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
 

Author Comment

by:Jono Martin
ID: 37843567
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:Jono Martin
ID: 37852712
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 37859300
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
 

Author Comment

by:Jono Martin
ID: 37861111
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 37865485
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
 

Author Comment

by:Jono Martin
ID: 37886784
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
 

Author Closing Comment

by:Jono Martin
ID: 37886802
Though all of ValentinoV's comments were helpful, this comment included the most helpful information.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Lync server 2013 Backup Service Error ID 4049 – After File Share Migration
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

746 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

11 Experts available now in Live!

Get 1:1 Help Now