Stored Procedure on SQL Server Rollback

[eighthman11]

Hey everyone,
Using Access 2003 and SQL Server 8.0

I have a stored procdure on SQL Server that is launched from my
access database where I send the parms.

The stored procedure on the server has several updating steps and
delete steps. I do rollbacks on these steps so if one step fails all
data returns to it status prior to the stored procedure being
launched.

On the rollback I do a raiserror in the SQL stored procedure. Is
there any way of capturing that error message in my Access database to
let the user know about the error.

Any help appreciatted. Thanks ray

[Rich P]

This is very hit and miss. Sometimes Access can read a RaiseError
message from sql server, but my experience has been that 99% of the time
-- Access cannot read those messages from the sql server. The solution
I went with was to migrate stuff to .Net. In .Net I have been able to
capture 100% the error messages from the sql server. I sense that
capturing error messages from sql server is a limitation in Access.
Matter of fact, the only time I can think of that I could read a message
from the sql server in Access was from a trigger RaiseError -- and that
was hit and miss.

Rich

*** Sent via Developersdex http://www.developersdex.com ***