Exception Handling

Hello,

I am trying to catch an error, but not able to. I am storing the value of @.@.Error soon after the statement which I believe would generate error. In order to produce the error I have deliberately used a wrong table name. So, the statement breaks but never comes to my error handling code snippet. Instead, it just throws the SQL server error message and quits. What am I doing wrong here? Here is the code snippet. The actual name of the temporary table is #TEMPO_TABLE, but in order to generate the error I have used #TEMPO_. Since this would surely error out, it should go to the label ROLLITBACK. But is not going to that label. Neither is it printing the error number as per the PRINT statement there. It just throws the SQL error and quits the execution. (Or should it NOT?). The Error that it throws it this:

Server: Msg 208, Level 16, State 1, Procedure FEED_PULL_XX, Line 157
Invalid object name '#TEMPO_'.

Can you please let me know whats going wrong here? Thanks a lot in advance.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0
Begin
PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'
GOTO ROLLITBACK
End

Hi Mannubhai,

Which version of SQL server you are using.. if you are using SQL05, try using Try..catch block.

If you are using SQL2k.. then in above script there is no variable declared and there is no rollitback lable..

And YOU SHOULD GET THE ERROR SAYING THAT THE TABLE DOES NOT EXISTS.. AND THIS ERROR HAS THE SEVERITY LEVEL OF 16.. which says that the error can be fixed by the user and terminates the batch.. so you are not able to catch it.

Have a look on the severity levels for more details.

Regards,

|||

There are errors that cannot be caught.

Erland has written up some good articles on the subject. Please take a look.

http://www.sommarskog.se/error-handling-I.html

http://www.sommarskog.se/error-handling-II.html

|||

Hey Thanks Sajid. I am using SQL 2000. The Code that I have given is just a snippet. It is not the complete one. The Complete SP has the variables declared and yes, the ROLLITBACK label is also present. The error as I said is, INVALID OBJECT #TEMPO_. But can this error NOT be caught in the way I have attempted? I think it does not even execute this statement --> SET @.ErrNo = @.@.Error. Why does this happen? Please let me know. Appreciate the response.

Mannu.

|||

You can refer to Erland's documents on Error handline.. spcially the first link provided by OJ. .that will surely help you out to understand the Error handling in details..

Regards,

|||

Hi All,

I seem to have solved this. The below insert which I was using, I put that into a variable and executed it using sp_executesql. A Dynamic SQL.

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_

I put this like this:

SET @.SQL = '

INSERT INTO Table1
(
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
)
SELECT
[ITAM_ASSET_EUP_BUS_LINE],
[ITAM_MACHINE_STATUS],
[ITAM_OWNER_ID],
[ITAM_ASSET_ADMIN],
[ITAM_MODEL],
[ITAM_ASSET_NAME],
[ITAMMACHINE_PURPOSE]
FROM
#TEMPO_'

EXEC sp_Executesql @.SQL

SET @.ErrNo = @.@.Error.

IF ErrNo <> 0

....

....

This gave the proper results. Let me know if more information is required.

Mannu.

|||

I partially agree with your solution.

But there is few risks you might face if you given the access permissions on SP level (readonly at Table)..

The alternate possible solution might be,

Option 1#:

Code Snippet

If Exists (Select * from tempdb..sysobjects where id=object_id('tempdb..#tempo_'))

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Else

Goto RollITBack

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End

Option 2#:

Wrapp the insert statement in new proc & call it from your main proc.

Code Snippet

Create Proc InsertFromTemp

as

Insert into Table1

(

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

)

Select

[itam_asset_eup_bus_line],

[itam_machine_status],

[itam_owner_id],

[itam_asset_admin],

[itam_model],

[itam_asset_name],

[itammachine_purpose]

From

#tempo_

Go

Exec InsertFromTemp

SET @.ErrNo = @.@.Error

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

IF @.ErrNo <> 0

Begin

PRINT '@.ErrNo is ' + ltrim(str(@.ErrNo)) + '.'

Goto RollItback

End