E-Squared
asked on
Retrieve the SQL Error Number from a SOAP Exception
I am using C# with framework 2.0, but I don't need working code, just some info about the SoapException object returned when using web services to run SSRS reports.
If the SQL Server creating the report encounters an error, I do get the exception and can see all sorts of information. However, if it is a SQL error from the SQL server for the datasource, it lacks the information I need to build sensible error handling on it. For example, consider the below XML SOAP exception I got (this is SoapException.Detail.Inner
What I need instead of the full "denied on object ObjectName" message is a simple error number or identifier for "EXECUTE permission denied on object" no matter what the object is.
And for a deadlock error, I'd need the error code for deadlock, not the nearly useless message "Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This will look like a new error message for each new process ID.
There has to be a better way than parsing strings to find out what the error is so I can take the correct action. (E.g., deadlock = try again. Something wrong with this report = keep trying the other reports. Something wrong with sql connection = stop all reports.)
So far in my exploration and searches, I haven't found what I need. Anyone have any ideas?
Note: I have turned on "remote error handling" or whatever it was.
Erik
If the SQL Server creating the report encounters an error, I do get the exception and can see all sorts of information. However, if it is a SQL error from the SQL server for the datasource, it lacks the information I need to build sensible error handling on it. For example, consider the below XML SOAP exception I got (this is SoapException.Detail.Inner
What I need instead of the full "denied on object ObjectName" message is a simple error number or identifier for "EXECUTE permission denied on object" no matter what the object is.
And for a deadlock error, I'd need the error code for deadlock, not the nearly useless message "Transaction (Process ID 74) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." This will look like a new error message for each new process ID.
There has to be a better way than parsing strings to find out what the error is so I can take the correct action. (E.g., deadlock = try again. Something wrong with this report = keep trying the other reports. Something wrong with sql connection = stop all reports.)
So far in my exploration and searches, I haven't found what I need. Anyone have any ideas?
Note: I have turned on "remote error handling" or whatever it was.
Erik
<ErrorCode>rsProcessingAborted</ErrorCode>
<HttpStatus>400</HttpStatus>
<Message>An error has occurred during report processing.</Message>
<ProductName>Microsoft SQL Server Reporting Services</ProductName>
<ProductVersion>9.00.3042.00</ProductVersion>
<ProductLocaleId>1033</ProductLocaleId>
<OperatingSystem>OsIndependent</OperatingSystem>
<CountryLocaleId>1033</CountryLocaleId>
<MoreInformation>
<Source>Microsoft.ReportingServices.ProcessingCore</Source>
<Message msrs:ErrorCode="rsProcessingAborted">An error has occurred during report processing.</Message>
<MoreInformation>
<Source>Microsoft.ReportingServices.ProcessingCore</Source>
<Message msrs:ErrorCode="rsErrorExecutingCommand">Query execution failed for data set '{DataSetName}'.</Message>
<MoreInformation>
<Source>.Net SqlClient Data Provider</Source>
<Message>The EXECUTE permission was denied on the object '{Stored Procedure Name}', database 'Test', schema 'dbo'.</Message>
</MoreInformation>
</MoreInformation>
</MoreInformation>
<Warnings />
Last Comment
PS The above examples are VB but easily convertable to C#. .. Hope it helps ..
Oh an another thing if you want to approach it from the the log files & write code based on the Event ID Error from the Windows Application Log here is the table to gain the definition of each EventID # on the specific error of the log ..
Reporting Services Errors and Events
http://msdn.microsoft.com/
And here is more information on what the variety of log files are available to gain this info from ...
Reporting Services Log Files
http://msdn.microsoft.com/en-us/library/ms157403.aspx
Reporting Services Errors and Events
http://msdn.microsoft.com/
And here is more information on what the variety of log files are available to gain this info from ...
Reporting Services Log Files
http://msdn.microsoft.com/en-us/library/ms157403.aspx
Last Note: Which sounds like you are more looking fro error Id's for events happening on the db server itself ...
For: Reporting Services Errors and Events
http://msdn.microsoft.com/
This is the most current list as of: 9/2007 MSDN -- But take heart, additional cause and resolution information will become available over the next several releases of SQL Server Books Online and the updates usually get updated via the software itself first before it gets posted on the MSDN site so ... to check for the latest updates visit:
Downloading and Updating Books Online
http://msdn.microsoft.com/
thanks!
For: Reporting Services Errors and Events
http://msdn.microsoft.com/
This is the most current list as of: 9/2007 MSDN -- But take heart, additional cause and resolution information will become available over the next several releases of SQL Server Books Online and the updates usually get updated via the software itself first before it gets posted on the MSDN site so ... to check for the latest updates visit:
Downloading and Updating Books Online
http://msdn.microsoft.com/
thanks!
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Well it can only be done when the eventId of error is expanded which mictrosoft does plan to expand in iterations ... if it were me I would do my application to prepare and use what Id's are available then when it is expanded add in the others ....
You would have to decide what to do in each opf your scnarios ie: redirect to another page, send an email, notify a pager dump to an error log etc etc etc.
Using a Try, Catch and Finally Error Code Exception block of course ....
in aswer in regard to points , you decide of course but all we do is answer questions for points and try and help askers we do not gain anything else from helping where we can. Our time is research time and knowledge share so you decide what would be fair. But again it is possible if the id doesn't exist for your specific in time it will and a broad approach filtering to s specific at a later date will happen in time so there is a solution just how specific will depend on your specific environment of which of course we cannot know exactly.
ASKER
Yes, the whole point of this is graceful error handling, especially handling errors in the future that I didn't allow for in the application code itself. The most likely source of unexpected new errors is in the report generation step, so I'd like to use table-driven error handling, and have the application automatically add errors it's never seen before, allowing the response to be user-configurable. This will extend the life of the application, protect it from changes (such as new database connection libraries throwing new never-before-seen exceptions), and run on changes to data in tables rather than only on changes to code that has to be written, compiled, tested, and deployed.
I'd like to award you 175 points out of 250 for a partial solution (you gave me information about what's coming up, but didn't actually solve the problem I'm facing). Do you think that's honorable (if that's even possible)?
I'd like to award you 175 points out of 250 for a partial solution (you gave me information about what's coming up, but didn't actually solve the problem I'm facing). Do you think that's honorable (if that's even possible)?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
There is a whole Exception Class New in 2005 Reporting Services - For More information see: Introducing Exception Handling in Reporting Services @ http://msdn.microsoft.com/
Specifically: Using the Detail Property to Handle Specific Errors; http://msdn.microsoft.com/
You can write code using this class to retreive error codes and even perform actions based on the error..for instance in Visual Basic.. ( // For specific Error Code reference see the SOAPExtension Error Table at: http://msdn.microsoft.com/
// Code for accessing the report server
}
catch (SoapException ex)
{
if (ex.Detail["ErrorCode"].In
{
// Perform an action based on the specific error code
}
}
/// Or just write an error message out ...
Console.WriteLine(ex.Detai
Console.WriteLine(ex.Detai