Re: Ignore SQL Server 2000 store proc errors and still get results?

You definitely don't want to do 30 round trips by handling the logic
in client code. Another option would be to create a table variable or
temp table in your stored procedure to handle the collection of valid
rows. All errors are appropriately handled or ignored in your T-SQL
code. Once the processing of these rows is complete, you simply select
from the table variable or temp table, and you have your result set
returned to the client in a single RT. Sorry, I don't have time to
look at your app, but this approach might work well for you since it
efficiently encapsulates all processing and error handling in
server-side code.

--Mary

On Thu, 11 Aug 2005 14:37:01 -0400, "Mike Jansen"
<mjansen_nntp@xxxxxxxx> wrote:

>I'm not calling RAISERROR. Errors occur because I have SET LOCK_TIMEOUT 0
>and I skip past records that are locked (I'm not using the READPAST hint
>because it doesn't fit our scenario).
>
>An alternative is to have the stored procedure only do one record at a time
>and have all the looping done entirely in the client (which is actually a
>service). I was trying to avoid this to eliminate 30 round trips per
>invocation. If you are interested in the "big picture" of what I'm trying
>to do, I have posted it for sanity check on
>microsoft.public.sqlserver.programming (look for "Quasi-work table (sanity
>check)", if you Google use "author:mjansen_nntp@xxxxxxxx" in the query).
>
>Thanks,
>Mike
>
>
>"Mary Chipman [MSFT]" <mchip@xxxxxxxxxxxxxxxxxxxx> wrote in message
>news:ghpkf1l0pq3853o99306javp3578jremo7@xxxxxxxxxx
>> Do you have control over the stored procedures? The optimal way to
>> handle server-side errors in this situation would be to NOT use
>> raiserror and instead return the expected @@error values in output
>> parameters instead. That way you can decide in your client-side code
>> whether or not to ignore the output parameter values without
>> triggering an exception. Raising errors and handling exceptions is
>> expensive in both server and client code.
>>
>> --Mary
>>
>> On Wed, 10 Aug 2005 14:39:30 -0400, "Mike Jansen"
>> <mjansen_nntp@xxxxxxxx> wrote:
>>
>>>We have a SQL Server 2000 stored procedure that raises errors when run.
>>>Because of what it is doing, this is expected. However, we have result
>>>sets
>>>that are also being returned from the stored procedure.
>>>
>>>Using either DataAdapter or SqlDataReader, we'd like to be able to execute
>>>the stored proc and still get the result sets back. However, exceptions
>>>are
>>>thrown whenever the stored proc raises an error. Is there any way to
>>>ignore
>>>or suppress these errors and still get the result sets?
>>>
>>>Thanks,
>>>Mike
>>>
>>
>

.

Relevant Pages

  • Re: Ignore SQL Server 2000 store proc errors and still get results?
    ... catch (Exception ex) ... > You definitely don't want to do 30 round trips by handling the logic ... > temp table in your stored procedure to handle the collection of valid ... >>> expensive in both server and client code. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Appdomain.CreateDomain throws SecurityException
    ... Communication of any success or failure messages back to the calling ... Launching the external process may be quite costly, ... a good choice if the stored procedure is called often from client code. ...
    (microsoft.public.dotnet.security)
  • Re: Page Loads again
    ... Is there a Meta-refresh tag in the client code that was generated? ... > The stored procedure is called in the Click event of the button ... after some time (when the stored procedure is still executing) ... > If I have just a smaller process running in the Click event instead of the ...
    (microsoft.public.dotnet.framework.aspnet)