Scheduler strucks at half way - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Scheduler strucks at half way

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-03 : 12:18:29

This is what my dts package do.
1.Initialise Dynamic Variables
2.ActiveX Script probes outlook inbox, extracts the filenameformat matched file, attach a macro to that excel file, save it in a folder.
3.Dump contents of excel to staging table
4.Extract data from staging table to other tables.
5.Truncate Staging Table.

I execute this package manually it runs in 3 mins max..No issues.
I put the same package in scheduler, the scheduler strucks, its not giving any error nor it does fail.Even after 10 - 15 mins, It simply says executing job.

I connect with DB Server using Windows Authentication

What could be wrong here?. Since I probe outlook in activex script and save it hdd, I think there is something to do with rights issue.
But I'm not sure either.

Any pointers?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-03 : 18:12:40

Go to the database server. Log into it using the MSSQLSERVER account. Now execute the DTS package via Enterprise Manager. I suspect it will be stuck there as well as that'll mimick what the job is doing when SQLServerAgent kicks it off.

The problem is most likely due to Outlook and the MSSQLSERVER account. When you run the package from your client machine, it'll be using your account and your Outlook. When run from the job scheduler, it uses the MSSQLSERVER account.

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-03 : 23:00:56

Tara,

When I say I execute it manually I do it from the server, not from my client machine. The Outlook is running in the server.
Manual execution, this is what I do:
Go to EM->Data Transformation Services -> Local Packages -> Select the dts -> Right Click and Execute. All this is in server. It works. Put same on scheduler, it strucks.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-03 : 23:45:24

What do you mean "put same on scheduler"? Are you using a SQL Server job to schedule the execution of the package? What happens when you execute the job manually, as opposed to the schedule starting it? What permissions is the job running under? What account are you loggin into the server as? Is it the same login as the job?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-04 : 01:09:05

Derrick,

Thats what I dont know. I log in to the server using Windows Authentication. And scheduled that job. I dont know or understand what the login does the job uses. How to find it?
When I execute manually it runs without any issues. Only on schedule it strucks.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 12:40:40

When the scheduler kicks off the job as opposed to you running it manually, it will use the account that the MSSQLSERVER service uses. Go to Control Panel..Admin Tools..Services..Double click on MSSQLSERVER, logon tab. What's it say for the login? If System Account, there's your problem.

So to test this as if you were the scheduler, log into the server using the MSSQLSERVER account and not your own Windows account.

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-04 : 14:39:29

I checked the logins in the sql server there were 3 logins 2 were using windows authentication and the other one is sa login.
So, can I assign the windows login to run the scheduler or it should be only a valid sqlserver login?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 14:58:39

So did you even check the MSSQLSERVER service?

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-04 : 16:45:36

Yes, I did checked the MSSSQLSERVER Service and SQLSERVERAGENT Service, both were running on a local system account, I reconfigured them to run under the windows login I used,restarted the services,but still the scheduler strucks...
may be you could also be able to help on this one too.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49210

I still dont understand how does the servers.txt file will look like as per that post.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-04 : 16:51:15

So now log into the database server using the windows login you use for the service. Execute the DTS package via DTS designer. Does it work there? I think it won't work there as that is mimicking what the scheduler is doing. So when you execute the package via DTS designer while logged into the db server using the MSSQLSERVER service account, what do you see?

Tara

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-04 : 17:12:03

Tara,

I tried what you said. First I scheduled the job finally this time it did failed on step1 of my dts package (thats almost after 10 mins). Then executed the same package from dts designer this time it executed the entire process was over in less than 2 mins. The event log file just says that the sql server agent failed on executing the process at step 1. I also found around 4-5 entries in the security audit log, couldnt interepret with it much all are of event id 565. For me it still looks like some kind of access rights.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-05 : 02:40:49

When the scheduler strucks or fails, In the event log I see this message.

Rejected safe mode action:Microsoft Office Outlook

Since my activex script probes outlook this error is coming, atleast thats what I think. I tried running the scheduler in all possible logins only missed one is Administrator login of that Windows 2003 server box. :( But so far nothing has worked.

Any body can tell me how to avoid this?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-05 : 08:23:29

Tara,

This is one more log I found out.

DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnFinish: DTSStep_DTSDynamicPropertiesTask_1 DTSRun OnStart: DTSStep_DTSActiveScriptTask_3 DTSRun OnError: DTSStep_DTSActiveScriptTask_3, Error = -2147220482 (800403FE) Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: ActiveX component can't create object:
'Outlook.Application'
Error on Line 24
Error source: Microsoft Data Transformation Services (DTS) Package
Help file: sqldts80.hlp
Help context: 1100
Error Detail Records:
Error: -2147220482 (800403FE);
Provider Error: 0 (0)
Error string: Error Code: 0
Error Source= Microsoft VBScript runtime error
Error Description: ActiveX component can't create object: 'Outlook.Application' Error on Line 24
Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help conte... Process Exit Code 1. The step failed.

Anything strikes? For me again it looks like an access privelege issue.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-06 : 12:33:05

Any body? If any one has come across this and have a solution?

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-05-06 : 12:36:23

Call Microsoft: 800-936-3100

Tara

robroy7
Starting Member

1 Post

Posted - 2005-05-13 : 11:53:55

What i have noticed after a lot of banging my head against the wall is, that if you go into SQL Server Agent-> Jobs. Select your package -> Properties and set the owner to be sa, it executes correctly most of the time.

Cheers

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-05-13 : 12:21:59

Have you tried using dtsrun utility in your step to run your package containing activex scripts? as in
exec xp_cmdshell 'dtsrun /Sservername /Usqluser /Ppassword /Npackage1'

..use the appropriate user,password in /U and /P parameters.

karuna
Aged Yak Warrior

582 Posts

Posted - 2005-05-17 : 01:37:54

quote:
Originally posted by robroy7

What i have noticed after a lot of banging my head against the wall is, that if you go into SQL Server Agent-> Jobs. Select your package -> Properties and set the owner to be sa, it executes correctly most of the time.

I tried this but it wasnt working...

quote:

Have you tried using dtsrun utility in your step to run your package containing activex scripts? as in
exec xp_cmdshell 'dtsrun /Sservername /Usqluser /Ppassword /Npackage1'

..use the appropriate user,password in /U and /P parameters.

Ok...This did work I tried with a different option. I created a batch file with the following text
dtsrun /Sservername /E /NPackageName
put it on a windows scheduler, then it worked.

Still what I dont understand is why its not running under the sql scheduler...

I tried all the options making job owner sa / windows authenticated user etc., But nothing worked....It keeps me puzzled...
One day when I find a solution I'll post it here.

Karunakaran
___________
It's better to be loved and lost, than ever to be loved...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources