Query works in anayser, fails in job scheduler

[schumway]

Hi,

I have this query that works in query analyser and fails with error 137 when called from sqlagent. Any suggestions?

tks in advance.

This is what I am trying to do...
declare @daysAgo int
declare @runInterval int
declare @currentInterval_h int
declare @currentInterval_m int
set @daysAgo = 3
set @runInterval = 20
set @currentInterval_h = datepart(hh, getdate())
set @currentInterval_m = datepart(mi, getdate())
EXECUTE ('
delete from table
where day(start_time) = day(getdate()- ' + @daysAgo + ')
and datepart(hh, start_time) = ' + @currentInterval_h + '
and
datepart(mi, start_time) <= ' + @currentInterval_m + '
and
datepart(mi, start_time) >= ( '+ @currentInterval_m + '-' + @runInterval + ')
')

 

[aalmeida]

The error 137 will show up when you try to use undeclared variable.
It may not be at the part of code you are showing but elsewhere in the code you can try:

Verify that any variables used in a SQL script are declared before being used elsewhere in the script.

Rewrite the procedure so that it does not reference variables in the EXECUTE statement that were declared outside of it.
AL Almeida
NT/DB Admin
&quot;May all those that come behind us, find us faithfull&quot;