@@ERROR returns 0 after error

[Aristotle Spyropoulos]

I execute an UPDATE statement using EXEC after I build it into a string:
@lcSQLString = 'UPDATE ' + @@mytable + 'SET x = y ...'
EXEC(@lcSQLString)

When the UPDATE fails, @@ERROR is nevertheless set to 0, because it returns the status of the EXEC command (which succedded) and not the @lcSQLString (which failed). At least, that is the explanation, I think. If I execute the statement directly by substituting the variables with their values, @@ERROR displays the correct value.

How can I capture @@ERROR within EXEC?

Thank you,
Aristotle

[Aristotle]

Gurukiran,
here is a very nice fix that someone suggested to me:

DECLARE @cmd VARCHAR(1000), @ExecError INT

CREATE TABLE #ErrFile (ExecError INT)
SET @cmd = 'SELECT * FROM MyTable WHERE ...' +
'INSERT #ErrFile VALUES(@@ERROR)'

EXEC(@cmd)
SET @ExecError = (SELECT * FROM #ErrFile)

And it works very well!
Aristotle

------------
Gurukiran at 1/29/01 12:39:20 PM

I have come across this situation before, but I can honestly say that I have not been able to find a direct solution. there is a rather involved and circuitous solution ( some may consider it bizarre, but it works ) :

If you can build your update statement and throw it into a table created expressly for this purpose, ( A table with a single varchar 8000 column ) The next line inserted can be an error checking statement like if @@ERROR ...., bcp out the contents of this table (using xp_cmdshell ) into a sql script file on to the hard disk, run xp_cmdshell 'osql.exe -i"file" ' etc, the desired effect is achieved. If you are using this as a part of a transaction, then it might become tougher.


------------
Aristotle Spyropoulos at 1/26/01 4:57:16 PM

I execute an UPDATE statement using EXEC after I build it into a string:
@lcSQLString = 'UPDATE ' + @@mytable + 'SET x = y ...'
EXEC(@lcSQLString)

When the UPDATE fails, @@ERROR is nevertheless set to 0, because it returns the status of the EXEC command (which succedded) and not the @lcSQLString (which failed). At least, that is the explanation, I think. If I execute the statement directly by substituting the variables with their values, @@ERROR displays the correct value.

How can I capture @@ERROR within EXEC?

Thank you,
Aristotle

[Sushruth Nanduri]

I guess something like this might work for your situation.
declare @lcSQLString nvarchar(4000)
@lcSQLString = 'UPDATE ' + @@mytable + 'SET x = y ...' + char(13) +
&#39;if @@error = 0 begin&#39; + char (13) + &#39;<process statements>&#39; + char(13) +
&#39;end &#39; + char(13)
&#39;else begin&#39; + &#39;<raiseerror statements>&#39; + char(13) + &#39; end&#39;
sp_executesql(@lcSQLString)
This way we are evaluating @@error immediately after the execution of the
update statement.

good luck.
Sushruth Nanduri.

------------
Aristotle at 1/29/01 2:13:36 PM

Gurukiran,
This sounds like a complicated process. And, unfortunately, this statement is part of a transaction. All I am trying to accomplish is execute the statement. If it fails, I want to roll back the transaction and discontinue this process.

I am glad that someone else has come across something similar. Perhaps I should experiment with RAISEERROR. Maybe that reports the right error message.

Aristotle

------------
Gurukiran at 1/29/01 12:39:20 PM

I have come across this situation before, but I can honestly say that I have not been able to find a direct solution. there is a rather involved and circuitous solution ( some may consider it bizarre, but it works ) :

If you can build your update statement and throw it into a table created expressly for this purpose, ( A table with a single varchar 8000 column ) The next line inserted can be an error checking statement like if @@ERROR ...., bcp out the contents of this table (using xp_cmdshell ) into a sql script file on to the hard disk, run xp_cmdshell &#39;osql.exe -i&#34;file&#34; &#39; etc, the desired effect is achieved. If you are using this as a part of a transaction, then it might become tougher.


------------
Aristotle Spyropoulos at 1/26/01 4:57:16 PM

I execute an UPDATE statement using EXEC after I build it into a string:
@lcSQLString = &#39;UPDATE &#39; + @@mytable + &#39;SET x = y ...&#39;
EXEC(@lcSQLString)

When the UPDATE fails, @@ERROR is nevertheless set to 0, because it returns the status of the EXEC command (which succedded) and not the @lcSQLString (which failed). At least, that is the explanation, I think. If I execute the statement directly by substituting the variables with their values, @@ERROR displays the correct value.

How can I capture @@ERROR within EXEC?

Thank you,
Aristotle