Catch an OPENQUERY error

  • 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,

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

    Master..xp_logevent 55000, '…'

  • 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,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply