June 8, 2010 at 2:39 am
Hi. I have a report that is set to run on a shared schedule. Upon completion it sends excel output to a group of users. It has not run for the last week. Report server returns 'Failure sending mail: An error has occurred during report processing'. The job history states the job executed OK. I have run on a one time schedule, this appears to be hit and miss (i.e. it will not email, re-schedule and it does send email).
Database mail 'Test' works fine.
Any ideas as to how I might troubleshoot?
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 8, 2010 at 2:53 am
I have reviewed the report server log file. I note the following error:
ReportingServicesService!processing!4!6/8/2010-04:04:26:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'Requirements'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set 'Requirements'. ---> System.Data.SqlClient.SqlException: Could not complete cursor operation because the table schema changed after the cursor was declared.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 8, 2010 at 6:46 am
Seems like a pretty straightforward error.
Could not complete cursor operation because the table schema changed after the cursor was declared.
Looks like in your dataset named Requirements you're using a cursor perhaps against a temp table(I'm guessing here) and you are making changes to the temp table schema after you've declared your cursor.
Long story short, why use a cursor? How about posting the query that makes up dataset requirements and perhaps we can suggest a way to do it without using a cursor. Cursors are typically slower than set based code because they process the data one row at a time instead of dealing with one set. Think about what you want to do to a table, not what you want to do to a row.
Have a look at the first article in my signature for how to post the appropriate information to get a tested solution quickest.
-Luke.
June 8, 2010 at 11:28 am
Hi Luke
Long story short, why use a cursor? How about posting the query that makes up dataset requirements and perhaps we can suggest a way to do it without using a cursor.
That would be good however it is a third party application and they developed this report referencing an encrypted stored procedure. I have looked at a fair bit of their code and they have a tendency to always use cursors.
Thanks for the offer.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 8, 2010 at 11:52 am
Phil, since it's a 3rd party report referencing an encrypted stored procedure do you have a support contract that you can beat them over the head with to fix it?
If there is no support agreement then it's fairly trivial to reverse engineer encrypted stored procedures in order to fix them. I believe Red-gate's SQL Prompt does this out of the box. You may want to download a trial version and see what you can see or you can take a look at this from mssqltips.
Just be aware that by decrypting and modifying the stored procedure you may be impacting your support agreement, but then again if you already have a support agreement, you should be hitting the vendor over the head with it until the fix your problem.
But then again that's just my $.02. For some other views on encrypted stored procedures you might want to read what Johnathan Kehayias or Jeremiah Peschka have to say on the topic.
Cheers,
-Luke.
June 8, 2010 at 3:37 pm
Luke thanks for all the tips. The support do their best but they are not in the business of re-writing the code, even if they were they would still use cursors as they do not think in sets like you sql gurus.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
June 8, 2010 at 6:55 pm
If they aren't in the business of re-writing code, what does your support contract get you? perhaps you could look at a cost savings for your company by renegotiating it. Additionally, you could perhaps get you to give you the unencrypted procedure and suggest a fix to them that they may be able to use to make their product better.
-Luke.
June 10, 2010 at 12:54 am
Problem solved (Luke made a comment in a PM that made me think). An Index Rebuild ran at the same time as the report. I moved the report schedule and the report ran without error.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply