Stored Procedure Working When Run Manually But Fail On DTS Automated Run

Question

Post reply

Stored Procedure Working When Run Manually But Fail On DTS Automated Run

yap.eujin

SSC Rookie

Points: 48
More actions
May 2, 2010 at 10:08 pm

#222087

Hi Gurus,
I have been facing this problem when I run this tsql statement it runs fine and load's everything from my staging into production however when I have scheduled it for a monthly periodic run it doesn't seem to get executed. I have not had any luck trying to debug why this happens. Can anyone shed some light on this issue. Thanks heaps.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Shanalise Hall of Fame Points: 3188 More actions · Answer 1

Hi,

Can you give us a sample of the tsql statement.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 2

Sounds like a permissions issue. That would be the main difference. But without details on exactly what error you're getting, it's hard to say beyond that.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

yap.eujin SSC Rookie Points: 48 More actions · Answer 3

Thanks for your response. I'm in the admin user group and I've created numerous ETL tasks before. What other information can I provide to give more clarity?

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 4

Check the login that the scheduling service is running under. Check the owner of the job that is being run and check the security settings on the script itself. It's usually one of these that isn't correct.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Are you getting any error messages?

If so what are the errors?

Do you have logging enabled within the package?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

yap.eujin SSC Rookie Points: 48 More actions · Answer 6

I have checked for logging and there is none. The job that kicks off the DTS is also set as owner SA.

There isn't any error when the DTS runs but just that the data doesn't get loaded =/ I was thinking that it was the order of my commit statement.

Grant Fritchey SSC Guru Points: 398916 More actions · Answer 7

I don't see any reason why the commit would not fire. Maybe using TRY/CATCH instead of the old style error handling will allow you to control the flow better. It still sounds like a security issue to me.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

I would enable logging from within the package. That will give you more detail as to what is actually happening with the package when executed as a job.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

yap.eujin SSC Rookie Points: 48 More actions · Answer 9

Thanks alot for your help guys. I'll give it a try with using catch and enabling logging to see what I come up with. Will keep you posted. Cheers!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

Thanks - looking forward to hearing back on the results.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events