@@error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

@@error

Author

Topic

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-10-21 : 12:58:42

Hi,

I have 2 SQL Statements

insert into test_table(1,1,1)
select @@error

I know that the insert statement will fail because the table does not exist. I then want to see the error - when running both of these commands together.

However, because the insert statement fails it never reaches the select @@error statement unless I run it separately afterwards.

From Books online my understanding is that by selecting @@error after any statement it should return the error code if the previous statement fails.

Is there something else I should be doing here to allow it to return the error code after the failing statement.

Note: I eventually want to use this method in stored procedures - that's why selecting the @@error value afterwards will not work for me.

Many Thanks for any ideas or suggestions.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-21 : 13:12:40

@@error returns errors when data changes. your error is non existant table. you have to check this with
select objectid([tablename]) -- this returns null if the objevct doesn't exists.

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 13:17:30

If your "insert into" was in a child SProc the calling SProc would be able to detrmine that the child had failed (I believe this is true for cases such as syntax errors / table not existing, but I don;t know if its true for more exotic errors)

Dunno if that leads you to a solution though ...

Kristen

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-10-21 : 13:30:44

Thanks for the replies

I have tried testing the value of @@error after the child SP was called - it just stalls where the error occurs.

Also was just using an example of a non existing table for simplicity. good thinking to overcome that one spirit1!!

I have also caused a statement to fail by inserting an incorrect data type (e.g. inserting a string instead of int). But my error still isn't selected afterwards.

I thought this was general error handling code to pick up ANY error caused by the previous SQL statement????

Kristen
Test

22859 Posts

Posted - 2004-10-21 : 13:49:35

Seems to work here (the parent SProc is able to detect that the Child SProc failed)



SET NOCOUNT ON
GO
PRINT 'CREATE TABLE test_table'
GO
CREATE TABLE test_table
(
	A	int,
	B	int,
	C	int
)
GO

PRINT 'CREATE PROCEDURE KBM_SP_1'
GO
CREATE PROCEDURE KBM_SP_1
AS
INSERT INTO test_table VALUES(1,1,1)
IF @@ERROR<>0 PRINT 'KBM_SP_1:*** Some error calling KBM_SP_1 ***'
SELECT [Rows in test_table] = COUNT(*) FROM test_table
GO

PRINT 'EXEC KBM_SP_1'
EXEC KBM_SP_1
-- Works OK

GO

PRINT 'CREATE PROCEDURE KBM_SP_2'
GO
CREATE PROCEDURE KBM_SP_2
AS
EXEC KBM_SP_1
IF @@ERROR<>0 PRINT 'KBM_SP_2:*** Some error calling KBM_SP_1 ***'
GO

PRINT 'EXEC KBM_SP_2'
EXEC KBM_SP_2
-- Works OK
GO

PRINT 'DROP table test_table'
DROP table test_table
GO

PRINT 'EXEC KBM_SP_1 (table does not exist)'
GO
EXEC KBM_SP_1
-- Error message: Invalid object name 'test_table'.

PRINT 'EXEC KBM_SP_2'
GO
EXEC KBM_SP_2
-- KBM_SP_2:Some error calling KBM_SP_1
GO

PRINT 'Drop everything'
GO
DROP PROCEDURE KBM_SP_1
GO
DROP PROCEDURE KBM_SP_2
GO
DROP table test_table
GO

SET NOCOUNT OFF
GO

Kristen

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-10-22 : 06:14:52

Thanks Kristen

I tried that and it works well. I then tested it using a different scenario and the error wasn't returned to the parent SP.

This is the one I tested before I made the posting yesterday.

Parent Stored Procedure
exec sp_bulk_insert
Print @@error

Child Stored Procedure -- sp_bulk_insert

-- Creates a Bulk Insert Statement
-- Execute Bulk Insert statement

I am purposely causing the Bulk Insert Statement to fail. Control is not returned to the Parent Stored Procedure. The SP fails at the point of the Bulk Insert statement and progresses no further.

Any ideas??

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-10-22 : 07:37:27

Hi,

Some code to illustrate my previous posting.

This is code similar to Kristen's but instead of inserting values into a table, data is bulk inserted into a table.

I am causing it to fail by dropping the table, into which the data is BCPd.

Does anyone have any ideas as to why the error might not be caught and reported by checking @@error?

Many Thanks

----------------------------------------------------------------------
drop procedure jones_insert
create procedure jones_insert
as

BULK INSERT figdb.dbo.[sa_AluAccountCurrency]
FROM 'c:\technology\fig\fig_list\rtaccu.txt'
WITH
(
FIELDTERMINATOR = 'Ý',
ROWTERMINATOR = '\n',
TABLOCK
)

if @@error <> 0
print 'Bulk Insert Error'
-----------------------------------------------------------------

drop procedure jones_call_error
create procedure jones_call_error
as

exec jones_insert
if @@error <> 0
Print 'There has been an error 1'

drop table sa_aluaccountcurrency

declare @error_no int
declare @error_desc varchar(500)
Print 'About to bulk insert after table is deleted'
exec jones_insert
set @error_no = @@error
if @error_no <> 0
Print @error_no
Print 'There has been an error 2 '
set @error_desc = (select description from master.dbo.sysmessages where error = @error_no)
Print @error_desc

----------------------------------------

declare @error_no int
declare @error_desc varchar(500)
exec jones_call_error
set @error_no = @@error
Print 'There has been an error 3 '
set @error_desc = (select description from master.dbo.sysmessages where error = @error_no)
Print @error_desc

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-10-22 : 11:02:02

Hi,

I now think that this is something to do with BULK INSERT and error handling.

In the database the sa_AluAccountCurrency table does not exist.

The following code will return the error descriptions based on @@error
---------------
drop procedure jones_insert
create procedure jones_insert
as
select * from sa_AluAccountCurrency
------------------------

declare @error_no int
declare @error_desc varchar(500)
exec jones_insert
set @error_no = @@error
Print @error_no
Print 'There has been an error Jones '
set @error_desc = (select description from master.dbo.sysmessages where error = @error_no)
Print @error_desc

NOW if I test a simple Bulk insert into the nonexistent table, I would expect that a similar error to that above should be returned in the error handling. Instead of doing a select we are now doinga bulk insert and errors should be trapped the same way.

-----------------------
drop procedure jones_insert
create procedure jones_insert
as
BULK INSERT warehouse.dbo.[sa_AluAccountCurrency]
FROM 'c:\technology\fig\fig_list\rtaccu.txt'
WITH
(
FIELDTERMINATOR = '¦',
ROWTERMINATOR = '\n',
TABLOCK
)

------------------------------------

declare @error_no int
declare @error_desc varchar(500)
exec jones_insert
set @error_no = @@error
Print @error_no
Print 'There has been an error Jones '
set @error_desc = (select description from master.dbo.sysmessages where error = @error_no)
Print @error_desc

The error handling in the second example doesn't work. It doesn't get as far as the error handling code. The error is displayed but is not followed by the error handling statements.

Is there a setting I should be using in the BULK INSERT or is there some database setting I need to change?

Or does BULK INSERT revent error handling working properly?

Any ideas will be much appreciated.

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-10-26 : 06:11:22

Hi,

I am now taking a different approach to this. Instead of doing a BULK INSERT I am now using BCP.

I can get back a success or failure code from xp_cmdshell, however it doesn't seem to return an actual error code either. Ideally I want an error code in order to log the error description.

Here is the code I am using:
declare @error_retd int
EXEC @error_retd = master..xp_cmdshell 'bcp warehouse..sa_AluAccountCurrency in c:\technology\fig\fig_list\rtAcCu.txt /e rtAcCu.err /b 100000 /c /t "¦" /r "\n" /S dbsvr /T', no_output
select @@error
select @error_retd

Has anyone implemented any code to catch an error when doing a BULK INSERT or BCP?? They are both Transact SQL Statements so @@error should contain the error numner if a problem occurs.

I would be very interested to hear how other people catch errors when doing BCP or BULK INSERT, especially using @@error.

Many Thanks

jones_d
Yak Posting Veteran

61 Posts

Posted - 2004-11-01 : 13:35:17

Hi,

We contacted Microsoft about this and the reply was as follows:

Our development team has confirmed that this is a by-design issue of error handling with @@error.

Errors with BULK INSERT (or bcp, linked servers, full text indexing, sp_OA_method etc) are batch terminating, because they are generated externally to SQL Server, and all external errors cause SQL Server to terminate the batch. This ensures the stability of SQL Server by not having to able to handle all possible external errors, which is of course impossible. Also, this makes SQL Server itself very robust, as it severely limits the possibilities of an external source crashing the server.

It makes handling errors in SQL itself more difficult however and if you want to write import/export routines with extensive error handling, DTS is probably the best choice. In addition, if you just check whether the object exists, you can use IF OBJECT_ID('object_name') > 0 Then ---- to determine the existence of the object and raise the error for error handling.

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-01 : 15:12:05

Interesting...I would disagree with the following however....

quote:
Originally posted by jones_d

DTS is probably the best choice.

And as far as the bcp....I create a batch file and place the bcp command in there...I then another batch file which executes that bat file and redirect the output to a "log" file.

I then load the log file to a table and interogate that file to see what happened.

I think Nigel has some samples

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-11-01 : 16:48:35

Useful feedback jones_d, thanks.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources