Please help with capturing @@ERROR from sql statements in DTSLookups

• sqlUser-257563

  SSC Rookie

  Points: 35

  More actions

  March 31, 2006 at 12:28 pm

  #67248

  I have a Transformation task which invokes a DTSLookups (from the ActiveX script). The lookup  contains two sql statements separated by the semicolon, like this:

  update table1 set val = ? where id = ?; select @@ERROR, @@ROWCOUNT

  This is how I invoke the lookup from the vbscript:

  errorCodeArray = DTSLookups("mylookup").Execute(val1, val2)

  I also have a step to handle the "On Failure" workflow.

  Let's say for some reason the update statement in the lookup fails. As a result, my "On Failure" workflow

  will be executed. However, in my failure task, I can't seem to get the correct value of the sql error code from the @@ERROR. It gives me a 0.

  Can someone show me how to capture the @@ERROR from the failed sql statement in DTSLookups?

  any help is appreciated.

  Newbie.

• April 3, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Greg Charles

  SSC-Forever

  Points: 45403

  More actions

  April 4, 2006 at 2:56 pm

  #630836

  Try assigning the results of the @@ERROR query to a global variable that the 'on failure' step can read to get the value.

  Greg

  Greg

• sqlUser-257563

  SSC Rookie

  Points: 35

  More actions

  April 5, 2006 at 3:31 pm

  #631109

  Thank you very much for your response.

  But I don't see an option for me to assign @@ERROR to a global variable from within Lookup.