EXEC errors...

[BradF]

How can I trap an EXEC error? @@rowcount and @@error aren't ever available... The SP seems to stop running altogether once the return is not a result set...

For example:

	SET @strOracleSQL = ''
	SET @strOracleSQL = @strOracleSQL + 'Select role from session_roles'
	
	SET @strSQL = ''
	SET @strSQL = @strSQL + 'SELECT Role '
	SET @strSQL = @strSQL + 'FROM OPENROWSET(''MSDAORA'',''192.168.1.100'' ; ''testuser'' ; ''password'', '''+RTRIM(@strOracleSQL)+''') '
	EXEC (@strSQL)

If the username/password is incorrect, the return is:

OLE DB provider 'MSDAORA' reported an error. Authentication failed.
[OLE/DB provider returned message: ORA-01017: invalid username/password; logon denied
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].

I want to trap an error somehow...

 

[SQLDenis]

Some errors are not trapable, specially severity 16 errors

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[mrdenny]

I don't think you are going to be able to trually trap the error.

Try running it like this.

...
declare @RET bit
set @RET = 0
    SET @strOracleSQL = ''
    SET @strOracleSQL = @strOracleSQL + 'Select role from session_roles'
    
    SET @strSQL = ''
    SET @strSQL = @strSQL + 'SELECT Role '
    SET @strSQL = @strSQL + 'FROM OPENROWSET(''MSDAORA'',''192.168.1.100'' ; ''testuser'' ; ''password'', '''+RTRIM(@strOracleSQL)+''') '
    EXEC @RET = @strSQL
    if @RET = 1
    BEGIN
       RAISERROR('There was an error talking to Oracle', 16, 1)
       ROLLBACK
       RETURN(1)
    END

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[mrdenny]

I don't think you are going to be able to trually trap the error.

Try running it like this.

...
declare @RET bit
set @RET = 0
    SET @strOracleSQL = ''
    SET @strOracleSQL = @strOracleSQL + 'Select role from session_roles'
    
    SET @strSQL = ''
    SET @strSQL = @strSQL + 'SELECT Role '
    SET @strSQL = @strSQL + 'FROM OPENROWSET(''MSDAORA'',''192.168.1.100'' ; ''testuser'' ; ''password'', '''+RTRIM(@strOracleSQL)+''') '
    EXEC @RET = @strSQL
    if @RET = 1
    BEGIN
       RAISERROR('There was an error talking to Oracle', 16, 1)
       ROLLBACK
       RETURN(1)
    END

The inablility to trap error messages is just one of the problems with Dynamic SQL.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com