Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Report Data Source Refuses to point to correct database Expand / Collapse
Posted Tuesday, March 19, 2013 6:40 AM

SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, November 25, 2016 8:40 AM
Points: 47, Visits: 241
I have a bunch of reports that point to a production database using a shared data source. For this example, we will say DataSource1 points to ProdDB1 with user ProdDBUser.

I moved ProdDB1 to new disks by using the following method:
1 - Backup database.
2 - Restore database into ProdDB1_Restore.
3 - Stop all applications from accessing ProdDB1.
4 - Backup ProdDB1 tlog.
6 - Restore tlog backup into ProdDB1_Restore.
7 - RENAME ProdDB1_Restore to ProdDB1
8 - Disable login access to ProdDB1_DONOTUSE
9 - Bring apps back online.

Everything works great with every other application and login. Go to reporting services, and it is trying to connect to ProdDB1_DONOTUSE. It is the strangest thing I've ever seen. It's happening from the reporting server and from BIDS on my desktop.

I have deleted and recreated the ProdDBUser. I even deleted and recreated the datasource. I can log into the database as the ProdDBUser and it execute the same query in the report directly into the SSMS and it hits the correct database. This is only occurring in SSRS.

Here is what is even more bizarre... the ProdDBUser doesn't even hit the ProdDB1 database directly. It connects to a ProdReporting database that has a passthrough query to ProdDB1.

How did SSRS get the database name ProdDB1_DONOTUSE?

Post #1432610
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse