Scheduling an sp

Hi All,
I hope I can explain this well.
I have an sp that populates certain tables with historical data and I've
scheduled
a job (using EM) that runs this sp on the first day of each quarter.
My question is, within the sp, if anything goes wrong, I do a rollback and
issue
a Return(5). Will this be interpeted as a failure within the job? Somehow I
think not.
So, how do I indicate failure to the job that schedules the sp?
Dan
Dan,
Add a RAISERROR statement to your stored procedure.
HTH
Jerry
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I hope I can explain this well.
> I have an sp that populates certain tables with historical data and I've
> scheduled
> a job (using EM) that runs this sp on the first day of each quarter.
> My question is, within the sp, if anything goes wrong, I do a rollback and
> issue
> a Return(5). Will this be interpeted as a failure within the job? Somehow
> I think not.
> So, how do I indicate failure to the job that schedules the sp?
>
> Dan
>
>
|||Thanks Jerry
Dan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7haHMfxFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Dan,
> Add a RAISERROR statement to your stored procedure.
> HTH
> Jerry
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>

Scheduling an sp

Hi All,
I hope I can explain this well.
I have an sp that populates certain tables with historical data and I've
scheduled
a job (using EM) that runs this sp on the first day of each quarter.
My question is, within the sp, if anything goes wrong, I do a rollback and
issue
a Return(5). Will this be interpeted as a failure within the job? Somehow I
think not.
So, how do I indicate failure to the job that schedules the sp?
DanDan,
Add a RAISERROR statement to your stored procedure.
HTH
Jerry
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I hope I can explain this well.
> I have an sp that populates certain tables with historical data and I've
> scheduled
> a job (using EM) that runs this sp on the first day of each quarter.
> My question is, within the sp, if anything goes wrong, I do a rollback and
> issue
> a Return(5). Will this be interpeted as a failure within the job? Somehow
> I think not.
> So, how do I indicate failure to the job that schedules the sp?
>
> Dan
>
>|||Thanks Jerry
Dan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7haHMfxFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Dan,
> Add a RAISERROR statement to your stored procedure.
> HTH
> Jerry
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>

Scheduling an sp

Hi All,
I hope I can explain this well.
I have an sp that populates certain tables with historical data and I've
scheduled
a job (using EM) that runs this sp on the first day of each quarter.
My question is, within the sp, if anything goes wrong, I do a rollback and
issue
a Return(5). Will this be interpeted as a failure within the job? Somehow I
think not.
So, how do I indicate failure to the job that schedules the sp?
DanDan,
Add a RAISERROR statement to your stored procedure.
HTH
Jerry
"dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Hi All,
> I hope I can explain this well.
> I have an sp that populates certain tables with historical data and I've
> scheduled
> a job (using EM) that runs this sp on the first day of each quarter.
> My question is, within the sp, if anything goes wrong, I do a rollback and
> issue
> a Return(5). Will this be interpeted as a failure within the job? Somehow
> I think not.
> So, how do I indicate failure to the job that schedules the sp?
>
> Dan
>
>|||Thanks Jerry
Dan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u7haHMfxFHA.1148@.TK2MSFTNGP11.phx.gbl...
> Dan,
> Add a RAISERROR statement to your stored procedure.
> HTH
> Jerry
> "dan artuso" <dartuso@.NOSPAMpagepearls.com> wrote in message
> news:OjXqmJfxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>> Hi All,
>> I hope I can explain this well.
>> I have an sp that populates certain tables with historical data and I've
>> scheduled
>> a job (using EM) that runs this sp on the first day of each quarter.
>> My question is, within the sp, if anything goes wrong, I do a rollback
>> and issue
>> a Return(5). Will this be interpeted as a failure within the job? Somehow
>> I think not.
>> So, how do I indicate failure to the job that schedules the sp?
>>
>> Dan
>>
>sql

Scheduled DTS fails, manual works. Help!

Hey all. I've got a DTS package that's scheduled to run after business hours on the last day of the month. This package copies some tables from an offsite SQL Server, then runs through a series of SQL Statements and finally exports an excel file with the results.

My problem is that the DTS will run if I manually start it, but the scheduled job always fails. Of course, the error I get is that the job failed at step one, and I have no other info.

I'm not a heavy DBA (mor eon the client app side of things), so I'm unsure as to how I can dbug this. Any help would be greatly appreciated!It's likely that the account that is setup for SQL server to use against your off-site resource does not have the credentials required. When you run the package manually, it's actually running as you. When it's run as a job, it's run under the server's account. I would check to make sure credentials are setup properly for your server.|||I'd just change the credentials used by the job step that runs the DTS package so that it actually DOES run as you, making the conflict a non-issue!

Note that this neatly side-steps a much larger problem. User accounts should not be required to make scheduled jobs run. You ought to have a "service account" with sufficient privledges to perform the task. This is a cornerstone concept for enterprise job scheduling.

-PatP|||Thanks for the info, I'll check that out. I do have one other job that runs 100% locally, and that one fails as well. We do have a load of jobs that run various times during the day, btu these are the only two that fail. I've checked the schedules, and the databases being used i nthe DTS aren't being accessed in any way when these are supposed to be running.|||Your "other" job may or may not have any of the same symptoms as the DTS you originally posted regarding. Does the "other" job run when scheduled as a job but not when run locally? If not, then that is a seperate issue...