How to use @@ERROR for EXEC(@SQL)

[narayanrakesh]

Hi,

I am seeking an expert help for the following issue, please find the code am using first ...the problem mentioned below that...
----------------------------------------------
DECLARE
,@DBName VARCHAR(128)
,@LoginName VARCHAR(128)
,@SQL VARCHAR(2000)

SET @DBName='dbname'
SET @LoginName='loginname'

SELECT @SQL=@DBName+'..SP_EXECUTESQL N''SP_REVOKEDBACCESS ['+@LoginName+']'''

EXEC(@SQL)
IF @@ERROR <> 0
PRINT @@ERROR
ELSE
BEGIN
PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +']
PRINT @@ERROR
END
--------------------------------------------------------------

Suppose I am trying to REVOKE a database access which not exist iw will give me a mesage like ,
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'Loginname' does not exist in the current database.

But the @@ERROR will return 0 as it was a successfull execution of EXEC(@SQL) .

So How can I retrieve the error value 15008 in a variable ..?

[arshad]

Hi,

I am seeking an expert help for the following issue, please find the code am using first ...the problem mentioned below that...
----------------------------------------------
DECLARE
,@DBName VARCHAR(128)
,@LoginName VARCHAR(128)
,@SQL VARCHAR(2000)

SET @DBName='dbname'
SET @LoginName='loginname'

SELECT @SQL=@DBName+'..SP_EXECUTESQL N''SP_REVOKEDBACCESS ['+@LoginName+']'''

EXEC(@SQL)
IF @@ERROR <> 0
PRINT @@ERROR
ELSE
BEGIN
PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +']
PRINT @@ERROR
END
--------------------------------------------------------------

Suppose I am trying to REVOKE a database access which not exist iw will give me a mesage like ,
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'Loginname' does not exist in the current database.

But the @@ERROR will return 0 as it was a successfull execution of EXEC(@SQL) .

So How can I retrieve the error value 15008 in a variable ..?

HI
You can catch the @@error if it is not 0 i.e. if not successful and can write custom message like:

declare @err_number as int
declare @err_msg as nvarchar(50)
set err_number=@@ERROR
set @err_msg='Not a valid user name or doesnt exists'

IF @err_number= 15008
PRINT @err_msg
ELSE
BEGIN
PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +']
END

see if it can solve you problem

[Rawhide]

You can only get the results from @@error in the very next statement. That's why you need to assign it to a variable like arshad demonstrated.

In your code reprinted below, when you check @@error in your IF statement, @@error contains the error status of the preceding statement. When you used @@error in the following line, it had been reset to the default status of 0.

EXEC(@SQL)
IF @@ERROR <> 0
PRINT @@ERROR

[narayanrakesh]

Hi,
I know about the @@ERROR needs to be stored into another variable.But my issue is @@ERROR is returning 0 for EXEC(@SQL).

The reason I believe is in the following messages,
------
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'Loginname' does not exist in the current database.
---------
@@ERROR=0 for second messages and for the fisrt error msg how I can store the value ?


-Rakesh

[Rawhide]

That's because Exec() was successful even though the underlying execute failed.

[MAK]

The sps by itself returns zero. Refer example 1 and example 2
But see the statements in example 3 and 4

Example 1
sp_revokedbaccess "abc123xzy"
print @@error

result
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'abc123xzy' does not exist in the current database.
0

Example 2
sp_addlogin 'a','a','x'
go
print @@error
go

result
Server: Msg 15025, Level 16, State 1, Procedure sp_addlogin, Line 56
The login 'a' already exists.
0

create table x (id int)
go
create table x (id int)
go
print @@error
go

result
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'x' in the database.
2714


example 4
create proc aaa as
create table x (id int)
go
exec aaa
go
print @@error
go

result
Server: Msg 2714, Level 16, State 6, Procedure aaa, Line 2
There is already an object named 'x' in the database.
2714

[narayanrakesh]

Hi,

Still I am confused....The example 1 and 2 says that the @@ERROR returns 0. So what should I do in this situation to get the @@ERROR >0 ?

-Narayan

[Rawhide]

What you should do is check to see if that person is a valid user in and has dbaccess to the database first. Checking the data is always better than checking the errors.

[narayanrakesh]

I am sorry to streach this thread like this..

I think you didn't get my requirement. I know that this login doesn't have access to the database or this login is not present in the server.

So in my Sp..I would like to handle these kind of situation to avoid few following steps after this revoking stement.

---Actual requirement---
I have an INSERT statement to history table after this revoke statement.So the login details should insert only if it's getting revoked..any kind of error should exclude this INSERT
......................

Thanks in Advance
Narayan

[mes3]

Ok, your problem is being caused by the return & error codes of sp_revokedbaccess. It does succeed, even though the call it's making does not. You might try this:
SELECT @SQL='use ' + @DBName+' declare @rc int exec @rc = SP_REVOKEDBACCESS ['+@LoginName+'] if @rc<>0 raiserror(''Revoke Failed'',18,1)'

Which works in your example for me. I do not know if there is a way to send the return code from the stored procedure, but that would be better than issuing an additional raise error, but it works for your request! It just doesn't give you the error number since that is not returned anywhere.

You might try executing the command by itself in SQL to see what is set (@@error is 0 for me even though the call generates an error!).

Good luck!