December 5, 2001 at 3:46 am
I have created this stored procedure:
create procedure dibtrantest
@des varchar(10)
as
begin tran
insert into ces_dev.dbo.rob1 (des) values (@des)
IF @@ERROR <> 0
BEGIN
ROLLBACK tran
RETURN -1
END
insert into cmss_skoda_3.dbo.rob1 (des) values (@des)
IF @@ERROR <> 0
BEGIN
ROLLBACK tran
RETURN -1
END
commit tran
return 0
GO
The table cmss_skoda_3.dbo.rob1 does not exist and I want it to exit gracefully but when I run it in Query Analyzer it bombs out with these messages:
Invalid object name 'cmss_skoda_3.dbo.rob1'.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
There are no other transactions happening.....can anyone tell me what I am doing wrong?
Thanks.
Rob
December 5, 2001 at 4:02 am
My guess is that this is not a high enough severity level to generate an error as such - more of a warning. If it's not in the error log then I'm on the right track.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
December 6, 2001 at 6:09 am
It will try to ersolve the object cmss_skoda_3.dbo.rob1 at run time as it is in another database. This doesn't exist so it can't generate a query plan for the statement and will exit without executing it. It is exiting without entering the error code check which is why you don't roll back the transaction.
You will get the same effect from a constraint violation in a trigger.
To be safe you should terminate the connection on an error otherwise you can end up within a transaction without expecting it.
Cursors never.
DTS - only when needed and never to control.
December 8, 2002 at 6:45 pm
I have observed that query manager behaves somewhat differently that one would expect. Try running your procedure as part of a SQL Server Agent job or calling it from your application. I put this one down to 'strange but true' and just work around it.
December 9, 2002 at 7:55 am
If you put the reference to your external table in dynamic SQL then the SP will the compile, you can then check for existence before you try and use it
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply