July 8, 2008 at 8:06 am
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,
July 9, 2008 at 10:05 am
Did you try something like below? It is a user-defined logevent when the number is greater than 50000.
Master..xp_logevent 55000, '…'
July 9, 2008 at 2:05 pm
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