Catch an OPENQUERY error

• rightfield

  SSCrazy

  Points: 2418

  More actions

  July 8, 2008 at 8:06 am

  #189224

  Hi experts,

  From SQL 2000 I query a Pervasive database through a Linked Server using

  INSERT INTO pv_orders

  SELECT

  cast(Item as integer) as Item,

  ...get a bunch of data,

  cast(OrderDate as datetime) as OrderDate

  FROM OPENQUERY(pervasive2008,'SELECT * FROM orders')

  Sometimes OrderDate has a bad date in it (June 31st for example). I thought I'd follow the above with

  If (@@ERROR <> 0)

  BEGIN

  EXEC dbo.z_ErrorLog 'pervasive2008 orders table error'

  END

  I am testing from Query Analyzer and just running the insert and error check. I don't get the error written. If I just highlight the EXEC line the error log is written to.

  QUESTION: How can I catch the error that occurs because of the bad date. This is the error.

  Server: Msg 7399, Level 16, State 1, Line 1

  OLE DB provider 'MSDASQL' reported an error.

  [OLE/DB provider returned message: [Pervasive][ODBC Client Interface]Invalid date, time or timestamp value.]

  OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005: ].

  Thanks very much for your help and guidance.

  Warm regards,

• SQL ORACLE

  One Orange Chip

  Points: 27807

  More actions

  July 9, 2008 at 10:05 am

  #840325

  Did you try something like below? It is a user-defined logevent when the number is greater than 50000.

  Master..xp_logevent 55000, '…'

• rightfield

  SSCrazy

  Points: 2418

  More actions

  July 9, 2008 at 2:05 pm

  #840495

  Thank you for the suggestion, however, nothing (i.e. the rest of the procedure) is processed after the OPENQUERY error so I would not get to the xp_logevent. I had even tried taking the the IF (@@ERROR <> 0) out and just printing to the log but no dice. The whole procedure terminates. The procedure is run from a scheduled job and sends me a generic email that the job failed and that the procedure in question was the last to run but I could really use an extra message in my log to tell me where exactly. I link to many Pervasive databases any one of which could contain the bad date.

  Warm regards,