@@ERROR not return anything for some error

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 not return anything for some error

Author

Topic

Georgefu
Starting Member

4 Posts

Posted - 2007-08-31 : 15:12:16

Hi,

Need your help to get the value for @@ERROR.

This is what I tried. If I run a invalid insert statement I do get the value for @@ERROR. But If i create a table which already exist, @@ERROR returns nothing.

Below is my code:

CREATE PROCEDURE dbo.ivos_test1 AS -- default '4.0.0.0'
BEGIN
Declare @sql_stmt1 VARCHAR(4000),
@errnum int
BEGIN
--SELECT @sql_stmt1 = 'CREATE TABLE dbo.xx(y int) '
SELECT @sql_stmt1 = 'inset into dbo.xx VALUES (1) '
select @sql_stmt1 STMT
Exec (@sql_stmt1)
set @errnum = @@ERROR
SELECT @errnum err
end

END
go

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-31 : 16:15:04

You need to get the value of @@ERROR from inside the DSQL.


CREATE PROCEDURE dbo.ivos_test1 AS -- default '4.0.0.0'
BEGIN
Declare @sql_stmt1 NVARCHAR(4000),
	@errnum nvarchar(100),
	@ParmDefinition nvarchar(100)

--SELECT @sql_stmt1 = 'CREATE TABLE dbo.xx(y int) '
SELECT @sql_stmt1 = 'inset into dbo.xx VALUES (1);SELECT @Error = @@ERROR '
Exec sp_ExecuteSQL @sql_stmt1, @ParmDefinition, @Error = @errnum OUTPUT

select @sql_stmt1 STMT
SELECT @errnum err



END
go

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Georgefu
Starting Member

4 Posts

Posted - 2007-09-04 : 12:57:14

Dinakar,

Thank you very much for your reply.

I added this line
SET @ParmDefinition = N'@Error int';
just befor the exec, but the SELECT @errnum err returns NULL, can you help me to fix the problems?

The message is
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'inset'.

Which I did on purpose ( used inset instead correct word insert) to catch this kind errors.

Thanks
George

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-09-04 : 13:07:01

Using inset instead of insert is a syntax error and will not be caught through @@ERROR. It is a fatal error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Georgefu
Starting Member

4 Posts

Posted - 2007-09-04 : 13:28:25

Tara,

I do capture the error if use Exec (@sql_stmt1) instead of Exec sp_ExecuteSQL @sql_stmt1, @ParmDefinition, @Error = @errnum OUTPUT.

The question is how to get all these errors, fatal or minor?

Thanks
George

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources