Scheduled Job Not Working

I have created a scheduled job in the enterprise manager, which checks around 20 different date columns and replaces any non-dates with NULL using the following syntax: -

update policy set [exp] =null where isdate (substring([exp],4,2)+'-'+left([exp],2)+'-'+'200'+right ([exp],1 ))=0
go
update policy set [eff] =null where isdate (substring([eff],4,2)+'-'+left([eff],2)+'-'+'200'+right ([eff],1 ))=0
go
update policy set [written] =null where isdate (substring([written],4,2)+'-'+left([written],2)+'-'+'200'+right ([written],1 ))=0
go

etc......

When I start the job, if fails within 2 seconds. If I copy the syntax in to the query analyzer it works fine.

All help appreciated.

A couple of questions

1 what is the error

2 who is the owner of the Job (and does he have permissions to the DB)

3 is the correct DB seleted in the step?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

to view the errro message , right click on the job, select view job history, click show step details (top right)

Click on the stepid and the error will be displayed in the bottom part

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi there,

The error message is: The job failed. The Job was invoked by User KEELAN_WESTALL\James. The last step to run was step 1 (UpdateDates).

The user is James (me)

I have security to the server/database/tables etc.

The correct DB is selected.

|||

The error message is: -

Executed as user: sa. The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256) The data type int is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary. [SQLSTATE 22018] (Error 256). The step failed.

|||

Convert to varchar first, take a look at this

select substring(1952005,1,2) -- will fail

select substring(convert(varchar(8),1952005),1,2) -- correct

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

As a test I ran the job just changing one column using the following syntax: -

update policy set [exp] =null where isdate (convert (varchar(8), (substring([eff],4,2)+'/'+left([eff],2)+'/'+'200'+right ([eff],1))))=0
go

It worked perfectly.

Thanks very much Denis.