execute sql task resultset

hi all,

in my execute sql task, i have a try catch statement to catch error code if there's an error...

begin try
insert into person.contact (contactid)
values ('1')

end try
begin catch

select @.@.error as ErrorCode
end catch

this ErrorCode will be mapped to a user variable.

my problem is the value for ResultSet.

in order to assign ErrorCode to the variable, i have to set the ResultSet value to Single row, but if this task does not fail, i need to set ResultSet value to None in order for it to run successfully.

How do i go about solving this problem?

Please help. thanks!

Try a case statement:

select case when @.@.error <> 0 then @.@.error else 0 end as ErrorCode|||

hi Phil,

thanks for your reply. but what resultset value should i set ? None or single row?

there'll still be an error if i put single row and there's no error.

if i put none value and there's an error, the error code won't be assign to the user variable..

|||It needs to be set to single row...

Are you saying you tested it with single row and my case statement and it still errored?

How about getting rid of the try and catch statements?

insert into ...

select case ...|||

without the try.. catch statement, the error code will return 0 even though there's an error.

declare @.errorvar int
insert into person.Contact (contactid)
values (1)

set @.errorvar = @.@.error

select case when @.errorvar<>0 then @.errorvar else 0 end as ErrorCode

i ran the above statement in SSMS and there's value for @.errorvar.

|||

Minority80,

I would tweak the approach in your first post to something like:

begin try

insert into contact (contactid)

values ('1')

Select -9999 as ErrorCode -- a predefined 'succesfull' execution code

end try

begin catch

select @.@.error as ErrorCode

end catch

That way, a single row would be always returned. Just make sure -9999 or the code you decide to use is not a valid error code. Then set the ResultSet property of the Execute SQL task to SingleRow.

|||I guess I don't understand. @.@.error should always return a value. So the first approach should work, at least according to BOL. So then, is it in how SSIS handles the SQL?|||

Phil Brammer wrote:

I guess I don't understand. @.@.error should always return a value. So the first approach should work, at least according to BOL. So then, is it in how SSIS handles the SQL?

Phil,

You are right, if there is no error @.@.error returns '0'. So, let me correct my suggestion:

begin try

insert into contact (contactid)

values ('1')

select @.@.error as ErrorCode -- Add this line

end try

begin catch

select @.@.error as ErrorCode

end catch

If you look again; the original post is missing the 'Add this line' I am suggesting; hence no 'SingleRow' is retrieved by the begin-end block and the execute sql task will error when configured as ResultSet= 'SingleRow'.

|||So then is the begin catch...end catch required?|||

thanks Rafeal and Phil too...

the begin catch .. end catch is required so that when error occurs, the errorcode can be captured..

|||

Phil Brammer wrote:

So then is the begin catch...end catch required?

Phil,

Actually is not required. Try and catch is new addition (a good one!) in SQL 2005 and it provides you more control over the error handeling logic in a t-sql block.

|||hmm.... now i'm confused. Rafael, why isn't try.. catch statement required?|||

I said that it is not required because if you omit it, the T-SQL block would be still valid. But if you want to avoid the Execute SQL task to fail because an error within the T-SQL statement, then you have to use it. Actually Try...catch is not available in version previous to 2005.

|||

oh.. but if i omit the try catch statement, the @.@.error value can't seem to be assigned to the package variable.. @.@.error always return 0.

i don't mind if the task fail cos then i can use event handler but @.@.error always return 0.

i assign @.@.error value to a package variable. i check the value by displaying the msgbox i put in a script task with the following code:

MsgBox(Dts.Variables("ErrorID").Value).

The value always return 0 even though there's an error.

Did i do something wrong? Thanks!

|||Well, I am not quite sure, but I think when you don't use the try....catch and a error occurs; the variable will not get populated as the task fails before populating the variable. The value '0' you get when it fails it may be the initial value of the variable (the one given in the variables panel). I am just guessing...