Retrieving Oracle error message in T-SQL

• mikeg13

  SSCommitted

  Points: 1851

  More actions

  June 5, 2008 at 7:23 am

  #208157

  How do I get the Oracle error message that is returned when I make a remote call with a linked server?

  This has come up a few times, but the most recent was the password changing on the Oracle side and my linked server no longer worked. The linked server 'shadow' has an invalid password in Oracle. If I try the following code...

  BEGIN TRY

  execute sp_testlinkedserver shadow

  print 'Success'

  END TRY

  BEGIN CATCH

  print 'ErrorNumber...'+ CAST(ERROR_NUMBER() as varchar)

  print 'ErrorSeverity...'+ CAST(ERROR_SEVERITY() as varchar)

  print 'ErrorState...'+ CAST(ERROR_STATE() as varchar)

  print 'ErrorProcedure...'+ IsNull(ERROR_PROCEDURE(),'')

  print 'ErrorLine...'+ CAST(ERROR_LINE() as varchar)

  print 'ErrorMessage...'+ IsNull(ERROR_MESSAGE(),'')

  END CATCH

  I get the following messages in the output.

  ErrorNumber...7303

  ErrorSeverity...16

  ErrorState...1

  ErrorProcedure...sp_testlinkedserver

  ErrorLine...1

  ErrorMessage...Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "shadow".

  I do not get any information from Oracle that tells me the invalid password. However, I know it exists because there is another line in my output that does tell me. I presume it is reported by the client, TOAD in my case.

  7412:sp_testlinkedserver:1: OLE DB provider "OraOLEDB.Oracle" for linked server "shadow" returned message "ORA-01017: invalid username/password; logon denied".

  This is the error message I want to report in my CATCH block. How do I get this? I saw something about IErrorInfo and ISQLServerErrorInfo, but no information on how to call this information. I really want that Oracle error message.