You also need to test for @@rowcount since you're not necessarily
going to capture the failure to modify data in @@error. You also need
to write @@error to a variable since its value does not persist past
the statement that triggers it:
SELECT @Err = @@ERROR, @Rows = @@ROWCOUNT
Then you can test to see if the value of @Err is <> 0 (no error was
triggered) and the value of @Rows is > 0 (data was modified
successfully) and branch in your code accordingly.
--Mary
On Sat, 27 Mar 2004 23:50:41 +0200, "Roy Goldhammer"
[quoted text, click to view] <roygoldh@hotmail.com> wrote:
>Hello there
>
>I'm working with SQL Server 2000.
>
>My application is Access 2000.
>
>When i run sql store procedure from access it brings me the errors that done
>in the store procedure
>
>Now i started using Transaction on my store procedures and not all the error
>raised in access.
>
>Here is the stracture i use:
>
>Alter Proc ProcName
>AS
> Begin Tran
>
> ..... insert, update ect...
>
> If @@Error <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
> COMMIT TRAN
>
>Where can be the problem of not getting the errors?
>
>any helo would be useful
>
The reason you are not getting an error in your Access app is that you
are handling it in your T-SQL code. You can re-throw from T-SQL, or (my
preference) use a return code to signify to your application there was
an error.
The return statement returns 0 by default, so in your error handling
block you would return say -100. In Access you can then test the return
value to find out if an error occurred. Se this
http://www.knieff2.com/programming/TsqlErr.html for some more on error handling in T-SQL.
Also, note that @@error is set after each statement. You need to check
it after each and every statement. Errors in T-SQL are nothing like VB.
HTH
BK
[quoted text, click to view] Roy Goldhammer wrote:
> Hello there
>
> I'm working with SQL Server 2000.
>
> My application is Access 2000.
>
> When i run sql store procedure from access it brings me the errors that done
> in the store procedure
>
> Now i started using Transaction on my store procedures and not all the error
> raised in access.
>
> Here is the stracture i use:
>
> Alter Proc ProcName
> AS
> Begin Tran
>
> ..... insert, update ect...
>
> If @@Error <> 0
> BEGIN
> ROLLBACK TRAN
> RETURN
> END
> COMMIT TRAN
>
> Where can be the problem of not getting the errors?
>
> any helo would be useful
>
>
Don't see what you're looking for? Try a search.
