July 8, 2010 at 11:12 am
I'm a newbie to SSRS, so I apologize for what may be a very dumb question. Here's my situation: I've developed a very simple report with one table that pulls data from a Dataset. My report originally had one Dataset: DATA_DEV, which points to a development database. This worked fine as long as I was publishing my report to my development server where SSRS is installed. Then I added a second Dataset: DATA_PROD, which points to, no surprise, a production database. Now, when I want to point my report to my development database, I select the table in Layout view, and choose DATA_DEV as the DataSetName in the Properties window. When I want to point my report to my production database, I choose DATA_PROD instead. To publish to the development server, I go to Project->Properties and change the TargetServerURL property to the appropriate server. Same with publishing to the production server. Now, no matter which server I publish to, I get an error message when I try to open the report. The error message on the prod server references the data source associated with the DATA_PROD Dataset. The error message is:
An error has occurred during report processing.
Cannot create a connection to data source 'NYSQL009_INSTANCE'.
For more information about this error navigate to the report server on the local server machine, or enable remote errors
"NYSQL009_INSTANCE" is the name I gave my DATA_PROD data source. The database instance name is "NYSQL009\NYSQL009", but a backslash is not an acceptable character for the name of the data source.
BUT - when I try to open the report on my dev server, I get a similar error message but it references the data source from an OLD Dataset that I deleted way back when. Why am I getting these error messages and why does publishing my report to my dev server with my DATA_DEV Dataset still throw an error message about an old data source?
More info - I am able to preview the report with both Datasets without a problem. Also, if I connect to the dev server via MSTSC and go to the SSRS URL locally, I get a slightly different error message:
An error has occurred during report processing.
Cannot create a connection to data source 'NYSQL009'. (NYSQL009 is the old, deleted Dataset's data source)
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I've gone back to my DBA about how logins are set up - this may be the problem, but if anyone has any insight, please pass it along. AND - I still don't understand why the report on the dev box still seems to be pointing to an old Dataset. What am I doing wrong? :crazy:
July 9, 2010 at 8:40 am
OK, I've solved this, although I'm still a bit befuddled. One really big problem was that the production database server is in a cluster and had failed over to the other node, so I was publishing to the wrong server, actually (SSRS is not cluster-aware). However, even when we got that in sync, the report was still complaining about my development Dataset. I finally removed it, and everything works fine. So I can't have two datasets, even if I'm only using one in my report? This makes testing changes in dev and then rolling to production much more onerous. Is there something I'm missing here? Or is that just how things are in SSRSLand?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply