fetch successful resultset after exception

Ok, here is the deal. In T-SQL, an error is handled depending on its severity. For example, a severity of 15 or less (or is it 10 or less? doesn't matter) will only raise a warning that can be caught through the message event. 16 or higher (or whatever) will cause an exception to be thrown on the .NET side, and 20 or higher causes the connection to be closed.

So far so good.

Now here's my issue: I have a stored procedure that does 2 queries (inside the same SP). Sometimes, the first query will succeed, while the second one will cause an error of severity 16. Again, in .NET that will throw an exception, making me unable to fetch the first resultset (which I require for logging purpose). If the error was, let say, severity 9, I could simply subscribe to the message event to get my error, yet still be able to get the first result set. But unfortunately life isn't perfect, and the error is indeed a severity 16.

Anyone know of a way to be able to get the first result set, even if the second query (within the same SP) completly fails?Why don't you wrap the two SQL statements in a transaction, so that if one fails, all attempted changes will be rolled back. You certainly wouldn't want something to work half-way--it should be an all or nothing deal. If you don't mind it working half-way, then perhaps the SQL should be put into separate calls.|||Well, I answered my own question: nothing is stopping me from accessing the data. If using a datareader, the exception isn't thrown until you move to the resultset that errored out. If using a dataset with multiple tables, the tables that succeeded are filled just fine as normal, and the ones that failed have 0 rows. Not the behavior I expected, but it did the trick.

As to why a non-atomic process is wrapped in a single stored procedure? Its because the SP was made for a relatively limited (in features) scheduling software, and it can only call one SP on the trigger. A bit silly, but I'm not the one who set that up originally, I'm just handling a migration. The requirement here was so that we could log what succeeded so we can give the log away to a supplier or some such.

Anyway, seems like ADO.NET handles that requirement just fine after all. I'm surprised, honestly.

Fatal error

Dear All,
I got a WIndows 2000 Server running MSDE. Recently it keep crashing with
this error in event viewer:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I have updated it to SP4 still the same problem. Does anyone know
anything about this error message please
Thank you very muchHi Andrew
Have you checked the windows event log for any messages that may indicate an
issue, also it is probably worth running CHKDSK to make sure that there is no
corruption on the disc drives.
John
"Huy (Andrew) V Nguyen" wrote:
> Dear All,
> I got a WIndows 2000 Server running MSDE. Recently it keep crashing with
> this error in event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I have updated it to SP4 still the same problem. Does anyone know
> anything about this error message please
> Thank you very much
>|||Hi John,
The error message is from Events Viewer. I check everything else at
around the same time nothing special. I had scheduled a CHKDSK to run
yesterday with no issues found. Any other idea please?
Andrew
John Bell wrote:
> Hi Andrew
> Have you checked the windows event log for any messages that may indicate an
> issue, also it is probably worth running CHKDSK to make sure that there is no
> corruption on the disc drives.
> John
> "Huy (Andrew) V Nguyen" wrote:
>> Dear All,
>> I got a WIndows 2000 Server running MSDE. Recently it keep crashing with
>> this error in event viewer:
>> Error: 0, Severity: 19, State: 0
>> SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
>> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
>> I have updated it to SP4 still the same problem. Does anyone know
>> anything about this error message please
>> Thank you very much|||Hi Andrew
Without knowing what was actually happening on the server at the time, it is
almost impossible to say what has caused this error. There are many issues
where the message may have been generated http://tinyurl.com/7kglf but I have
not found one that was not cured prior to SP4. You may want to raise a call
with PSS if this persists
http://support.microsoft.com/common/international.aspx?rdpath=1&gprid=2855&c1=508
John
"Huy (Andrew) V Nguyen" wrote:
> Hi John,
> The error message is from Events Viewer. I check everything else at
> around the same time nothing special. I had scheduled a CHKDSK to run
> yesterday with no issues found. Any other idea please?
> Andrew
> John Bell wrote:
> > Hi Andrew
> >
> > Have you checked the windows event log for any messages that may indicate an
> > issue, also it is probably worth running CHKDSK to make sure that there is no
> > corruption on the disc drives.
> >
> > John
> >
> > "Huy (Andrew) V Nguyen" wrote:
> >
> >> Dear All,
> >>
> >> I got a WIndows 2000 Server running MSDE. Recently it keep crashing with
> >> this error in event viewer:
> >>
> >> Error: 0, Severity: 19, State: 0
> >> SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
> >> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> >>
> >> I have updated it to SP4 still the same problem. Does anyone know
> >> anything about this error message please
> >>
> >> Thank you very much
> >>
>|||I am getting the same error
SqlDumpExceptionHandler: Process 58 generated fatal exception running sQL
2000 sp4 and latest windows 2000 OS updates.
But I am also running in a clustered environment.
One of my servers never crashes (with pretty much the same patches etc.)
while the other server continues to crash.
I starting to think it has something to do with the actual machine hardware
and not the software.

Fatal error

Dear All,
I got a WIndows 2000 Server running MSDE. Recently it keep crashing with
this error in event viewer:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I have updated it to SP4 still the same problem. Does anyone know
anything about this error message please
Thank you very much
Hi Andrew
Have you checked the windows event log for any messages that may indicate an
issue, also it is probably worth running CHKDSK to make sure that there is no
corruption on the disc drives.
John
"Huy (Andrew) V Nguyen" wrote:

> Dear All,
> I got a WIndows 2000 Server running MSDE. Recently it keep crashing with
> this error in event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I have updated it to SP4 still the same problem. Does anyone know
> anything about this error message please
> Thank you very much
>
|||Hi John,
The error message is from Events Viewer. I check everything else at
around the same time nothing special. I had scheduled a CHKDSK to run
yesterday with no issues found. Any other idea please?
Andrew
John Bell wrote:[vbcol=seagreen]
> Hi Andrew
> Have you checked the windows event log for any messages that may indicate an
> issue, also it is probably worth running CHKDSK to make sure that there is no
> corruption on the disc drives.
> John
> "Huy (Andrew) V Nguyen" wrote:
|||Hi Andrew
Without knowing what was actually happening on the server at the time, it is
almost impossible to say what has caused this error. There are many issues
where the message may have been generated http://tinyurl.com/7kglf but I have
not found one that was not cured prior to SP4. You may want to raise a call
with PSS if this persists
http://support.microsoft.com/common/...id=2855&c1=508
John
"Huy (Andrew) V Nguyen" wrote:

> Hi John,
> The error message is from Events Viewer. I check everything else at
> around the same time nothing special. I had scheduled a CHKDSK to run
> yesterday with no issues found. Any other idea please?
> Andrew
> John Bell wrote:
>
|||I am getting the same error
SqlDumpExceptionHandler: Process 58 generated fatal exception running sQL
2000 sp4 and latest windows 2000 OS updates.
But I am also running in a clustered environment.
One of my servers never crashes (with pretty much the same patches etc.)
while the other server continues to crash.
I starting to think it has something to do with the actual machine hardware
and not the software.

Fatal error

Dear All,
I got a Windows 2000 Server running MSDE. Recently it keep crashing with
this error in event viewer:
Error: 0, Severity: 19, State: 0
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I have updated it to SP4 still the same problem. Does anyone know
anything about this error message please
Thank you very muchHi Andrew
Have you checked the windows event log for any messages that may indicate an
issue, also it is probably worth running CHKDSK to make sure that there is n
o
corruption on the disc drives.
John
"Huy (Andrew) V Nguyen" wrote:

> Dear All,
> I got a Windows 2000 Server running MSDE. Recently it keep crashing with
> this error in event viewer:
> Error: 0, Severity: 19, State: 0
> SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005
> EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
> I have updated it to SP4 still the same problem. Does anyone know
> anything about this error message please
> Thank you very much
>|||Hi John,
The error message is from Events Viewer. I check everything else at
around the same time nothing special. I had scheduled a CHKDSK to run
yesterday with no issues found. Any other idea please?
Andrew
John Bell wrote:[vbcol=seagreen]
> Hi Andrew
> Have you checked the windows event log for any messages that may indicate
an
> issue, also it is probably worth running CHKDSK to make sure that there is
no
> corruption on the disc drives.
> John
> "Huy (Andrew) V Nguyen" wrote:
>|||Hi Andrew
Without knowing what was actually happening on the server at the time, it is
almost impossible to say what has caused this error. There are many issues
where the message may have been generated http://tinyurl.com/7kglf but I hav
e
not found one that was not cured prior to SP4. You may want to raise a call
with PSS if this persists
[url]http://support.microsoft.com/common/international.aspx?rdpath=1&gprid=2855&c1=508[
/url]
John
"Huy (Andrew) V Nguyen" wrote:

> Hi John,
> The error message is from Events Viewer. I check everything else at
> around the same time nothing special. I had scheduled a CHKDSK to run
> yesterday with no issues found. Any other idea please?
> Andrew
> John Bell wrote:
>|||I am getting the same error
SqlDumpExceptionHandler: Process 58 generated fatal exception running sQL
2000 sp4 and latest Windows 2000 OS updates.
But I am also running in a clustered environment.
One of my servers never crashes (with pretty much the same patches etc.)
while the other server continues to crash.
I starting to think it has something to do with the actual machine hardware
and not the software.