Snitz Forums 2000 - SQL Server Error Resuming in Scheduled Jobs

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Community Forums

Community Discussions (All other subjects)

SQL Server Error Resuming in Scheduled Jobs

New Topic

Topic Locked

Printer Friendly

Author

Topic

dayve
Forum Moderator

USA
5820 Posts

Posted - 16 March 2006 : 11:08:46

I have a scheduled job that does a nightly import (snapshot) of some data through a linked server. Occasionally I receive an error with one of the SQL statements in a step that fails the job immediately. I was wondering if there is a way to continue on to the next statement in my job? The following is an example of the statements in the job


SELECT * INTO s8_mast FROM 
OPENQUERY(MLS_S8, 'SELECT * FROM s8_mast');

SELECT * INTO s8_prop FROM 
OPENQUERY(MLS_S8, 'SELECT * FROM s8_prop');

SELECT * INTO s8owner FROM 
OPENQUERY(MLS_S8, 'SELECT * FROM s8owner');

SELECT * INTO s8pmthis FROM 
OPENQUERY(MLS_S8, 'SELECT * FROM s8pmthis');

SELECT * INTO s8_cvmas  FROM 
OPENQUERY(MLS_S8, 'SELECT * FROM s8_cvmas');

SELECT * INTO s8house  FROM 
OPENQUERY(MLS_S8, 'SELECT * FROM s8house');

If the first statement fails, I'd like it to continue to the next step. I've thought about putting these statements in individual steps, but this is just a small portion of the imports I am doing so I would end up with 50+ steps. While I already know I can do a DTS Import and edit the properties for RETRY, I'd like to keep it the way I have it right now. I tried setting ARITHABORT and ARITHIGNORE along with some other environment variables but it doesn't seem to work with DATES. The problem with this import is that sometimes one of the date fields is incorrect and I have no control over the error checking in the other database system except for being able to fix the bad date. I also can not individually check the date fields because there are too many fields and tables to be checking it in.

Anyway, I guess what I am looking for is something similar to ON ERROR RESUME in a SQL environment.

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 16 March 2006 : 15:37:15

My suggestion would be to put your import SELECT's into a Stored Procedure and execute the SP from the DTS Job, you can then check @@ERROR afterward each SELECT to check for error states and decide what to do using condition coding such as GOTO or RETURN to exit from the SP including writing out log information to indicate a particular step has failed or even sending you an email if any of them fail etc, it would certainly give you a bit more flexibility anyway.

SELECT * INTO s8_mast FROM
OPENQUERY(MLS_S8, 'SELECT * FROM s8_mast');

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
END

COMMIT TRAN
GO

SELECT * INTO s8_prop FROM
OPENQUERY(MLS_S8, 'SELECT * FROM s8_prop');

etc

Kiwihosting.Net - The Forum Hosting Specialists

dayve
Forum Moderator

USA
5820 Posts

Posted - 16 March 2006 : 20:06:17

Thanks...

Either I am doing something wrong, though the syntax check is fine, or Stored Procedures do not like Linked Servers. When I create a simple stored procedure with a linked server and execute it in Query Analyzer, it never returns a result, it just continues to show "Executing Query batch..." in the status bar.

belay my last... server problems. seems to be working now. this solution should work and I don't know why I didn't think of it earlier. thanks.

Edited by - dayve on 16 March 2006 20:26:39

dayve
Forum Moderator

USA
5820 Posts

Posted - 17 March 2006 : 18:20:13

I think SQL Server has an issue with error trapping linked server queries. The following stored procedure never hits the error handler when calling the SELECT statement. When I purposely put a bad date format in a date field I get the following:


Server: Msg 8114, Level 16, State 10, Procedure sp_MLS, Line 6
Error converting data type DBTYPE_DBDATE to datetime.

This is the stored procedure in it's most basic form that I am using:


CREATE PROCEDURE sp_MLS AS

BEGIN TRAN
  DROP TABLE s8_mast_test;

  SELECT * INTO s8_mast_test FROM  OPENQUERY(MLS_S8, 'SELECT * FROM s8_mast');

  IF (@@ERROR <> 0) GOTO ERR_HANDLER

COMMIT TRAN

RETURN 0

ERR_HANDLER:
 PRINT 'Unexpected error occurred!'
 ROLLBACK TRAN
 RETURN 1
GO

Gremlin
General Help Moderator

New Zealand
7528 Posts

Posted - 19 March 2006 : 20:40:16

Can't offer too many suggestions regarding the linked servers, not something I use too often. Might want to try posting for help at SQLTeam.com and see if someone has some ideas, there's some pretty clued up SQL Admins that frequent over there.

Kiwihosting.Net - The Forum Hosting Specialists

Topic

New Topic

Topic Locked

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.4 seconds.