Posted - 01/22/2013 : 21:20:44
| The primary question that I would like answered is,
Do Standard Subscriptions work with Custom Data Processing Extensions?
and then if so,
What am I doing wrong?
I read the there were some restrictions with Data-Driven Subscriptions and Custom Data Processing Extensions, but no mention of Standard Subscriptions.
I have hundreds of reports, all using the same Custom Data Processing Extension. We use the Custom Data Processing Extension primarily to access 2 Data Sources for each of the reports. Reports work perfectly from the application ReportViewer, and they can also be Viewed from the ReportManager with no problem.
I need to create Standard Subscriptions for some reports to run automatically periodically. I created a test Subscription with the ReportManager on a report that uses the Custom Data Processing Extension. All of the ReportServer tables populated correctly (dbo.Subscriptions, dbo.Notifications, etc.) but when it was time for the report to run, I get an error shown in the ReportServer log:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'DataSet1'. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
I created a simple report with the ReportBuilder NOT using the Custom Data Processing Extension and I could create a Subscription that ran sucessfully. That's the reason for the question. Now of course the reason that the other Subscriptions are failing could be totally unrelated to the Custom Data Processing Extension, and I'm hoping that's the case, so the next question, What am I doing wrong?
I mention that Subscriptions DO work because I found and went through a laundry list of "what to do" for this error, and they all assume that Subscriptions aren't working. All of the components for Subscriptions DO work and DO sucessfully deliver a report to a Share.
The error is long, and I included the entire error, but I think it boils down to "the server was not found". And given that the server is there, and working, and working for another Subscription, I have to conclude that for some reason the Server Agent that logs in to run the Subscription isn't seeing the SQL Server, doesn't have the right permissions to get to the SQL Server(?). The SQL Errorlog has no reference to an error, again, as if it's not even being accessed. When I first tested the Subscription that worked, I put in the wrong password, and that was reflected in the SQL Errorlog, but nothing for any of the other failing Subscriptions shows in the SQL Server log, just in the ReportServer logs.
There's a lot that I tried that I didn't mention, but this post is too long as it is. Please ask questions or if you recognize the problem, please offer some advice. Thanks.
Edited by - PCW on 01/22/2013 21:45:18