SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Report Data Source Refuses to point to correct database


Report Data Source Refuses to point to correct database

Author
Message
Edward Mlynar
Edward Mlynar
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 Visits: 249
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.
5 - RENAME ProdDB1 TO ProdDB1_DONOTUSE .
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?

...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search