Did a search of the SQL Teams forum and did not find an absolute answer to what I am suspecting, but can someone confirm whether or not error trapping is possible when querying data from a linked server? I created a Stored Procedure that does a nightly snapshot from another database system that is known to allow bad dates from time to time. I can't check every single table for every single date field as that would be more labor intensive than fixing the data when an error occurs. I keep receiving the following error, but for some reason the trap does not work. This same procedure works with a non-linked server (native table select).Any input would be greatly appreciated. Server: Msg 8114, Level 16, State 10, Procedure importMLS, Line 6Error converting data type DBTYPE_DBDATE to datetime.
CREATE PROCEDURE importMLS ASBEGIN TRAN DROP TABLE s8_mast; SELECT * INTO s8_mast FROM OPENQUERY(MLS_S8, 'SELECT * FROM s8_mast'); IF (@@ERROR <> 0) GOTO ERR_HANDLERCOMMIT TRANRETURN 0ERR_HANDLER: PRINT 'Unexpected error occurred!' ROLLBACK TRAN RETURN 1GO
SQL Server Stored Procedure and Linked Servers
Posted - 2006-03-20 : 20:12:58
