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.