Scheduled job executing Stored procedures fails

23 Mar 2006 10:39 AM

news.esat.net

Hi,

I have 4 seperate stored procedures that if I run manually from Query
Analyzer work fine. Each one picks rows from a number of joined tables
depending on a date criteria. Each SP is exactly the same except for the
date ranges.

When they are run manually they do what I expect and I end up with 4 tables
each populated with a 1/2 dozen records.

However when I create a job in Enterprise Manager and add four steps into it
(one for each job) I end up with it creating four empty tables even though
two minutes ago I ran them manually and got what I expect and if I run them
manually after the scheduled job ran I get the correct results.

Each step says something like "Exec MoneyLaundering_21Days" and that's it.
The correct database is chosen in the top of the scheduled job dialogue box.

I've even tried scheduling a new job and just putting one SP into it but I
get the same result. I'm kinda at my wits end here so any assistance would
be much appreciated.

Regards,

Matt Lemon.

23 Mar 2006 12:49 PM

Andrew J. Kelly

Do you have the sp wrapped in a transaction that you are not committing?

--
Andrew J. Kelly SQL MVP

Show quote

"news.esat.net" <matt.le***@williamfry.ie> wrote in message
news:dvttn9$cj4$1@reader01.news.esat.net...
> Hi,
>
> I have 4 seperate stored procedures that if I run manually from Query
> Analyzer work fine. Each one picks rows from a number of joined tables
> depending on a date criteria. Each SP is exactly the same except for the
> date ranges.
>
> When they are run manually they do what I expect and I end up with 4
> tables each populated with a 1/2 dozen records.
>
> However when I create a job in Enterprise Manager and add four steps into
> it (one for each job) I end up with it creating four empty tables even
> though two minutes ago I ran them manually and got what I expect and if I
> run them manually after the scheduled job ran I get the correct results.
>
> Each step says something like "Exec MoneyLaundering_21Days" and that's it.
> The correct database is chosen in the top of the scheduled job dialogue
> box.
>
> I've even tried scheduling a new job and just putting one SP into it but I
> get the same result. I'm kinda at my wits end here so any assistance
> would be much appreciated.
>
> Regards,
>
> Matt Lemon.
>

23 Mar 2006 12:52 PM

Rogas69

Did you look at error log? Have you tried profiler?
I assume, your procedures create tables AND populate them with some records
right?
Maybe the identity under which job is running doesn't have appropriate
credentials to populate tables?
Peter