Functioning DTS fails when scheduled as a job 2

[tbellomo]

Hi everyone,

I know that MANY people have had similar problems in the area of a DTS package executing fine, but failing when scheduled. I've read all the threads I could find that dealt with this type of error, but I couldn't find anything that solved my problem.

The DTS is a simple ActiveX script. The script opens a mdb and runs a macro. I got source for it off of techrepublic. Everything is dandy when executed; otherwise one of two things happens:

1. If the mdb is on the network (using UNC ref) the first step hangs on "Executing Step 1".

2. If the mdb is local, it fails immediately.

I've tried tweaking SQL Server Agent permissions and the like, but maybe I'm not doing it correctly. I've tried Windows Authentication, 'sa', and even another sql server account that I granted total access to. I can't seem to get anything to work. The SQL Server is the local box, so whether I'm executing or scheduling, it's running on the same box.

The message I get when it fails is: "Microsoft Access can't open the database because it is missing, or opened exclusively by another user".

Please, if some one can help me, I would really appreciate it. I'm a novice, but I'm willing to do some reading to resolve this. BTW, I've checked out the article on msdn about problems with scheduling DTS's. Is it me, or are they incredibly vague:
"Change your SQL Server Agent permissions" -- hmm, maybe I'm not doing it correctly; why not walk me through the process?

Thanks,
Tim

 

[mrdenny]

What account are the SQL Server Agent and MS SQL Server services running under?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tbellomo]

Currently, I have them set to run under Windows authentication -- but I've also tried "sa" -- to no avail.

Thanks,
Tim

 

[mrdenny]

no, not the jobs. The services them selves. In Enterprise Manager, right click on the server, select properties. On the security tab, at the bottom in the Service Startup Account what is selected?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tbellomo]

That area is all grayed out on both property windows.

Thanks,
TIm

 

[mrdenny]

ok, try taking a look in the service applet in the Administrative Tools Folder off the Start Menu. You are looking for the MSSQLSERVER and SQLSERVERAGENT services. See what there settings are set for at startup.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tbellomo]

Both are set to "log on as: Local System Account".

 

[mrdenny]

ok, that's why it failed when trying to get to the database on the other machine. And I would assume that, that is the reason it failed when connecting to Local. Create a new user account on the domain, give it rights to the folders that it needs. Use Enterprise Manager and change the startup account for both the services, and it should be able to use your access database.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tbellomo]

OK -- I figured that it would just use the account that I logged onto Windows 2000 with -- my account -- which has access to all of those directories.

I just manually typed in my information.

The job "succeeded" -- unforunately, it (the macro) didn't do everything it was supposed to... BUT I know the DTS/job ran the macro -- SO THANK YOU SO MUCH!

-Timo

 

[mrdenny]

no problem. When the Agent runs a dts package it uses the account that the SQL Agent is running under. When you run a package manually you run it under your account. It is a common mistake that people make thinking that when you run the package on your machine, it runs under the Servers permissions.

I'm glad it worked.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[tbellomo]

I knew that was the cause, but I hadn't found any threads going into the detail that you did to fix it.

THanks Again,
Tim

 

[yksvaan]

I didn't quite get it from previous messages, but did you change the startup account of services (MSSQLSERVER and) SQLSERVERAGENT to your own account name. If so, remember when(if) your password changes the next time, those services won't start, you have to go and type your new password in the services log on information. This is why mrdenny gave the excellent advice that "Create a new user account on the domain.." , which should have service startup sort of security policy different from workstation users. Of course if you are using Administrator, it doesn't matter...

Cheers

 

[tbellomo]

Yes, I'm aware of that... I simply don't have the authority to do so though.

Thanks for the heads up,
Tim

 

[mrdenny]

glad I could help.

Denny

--Anything is possible. All it takes is a little research. (Me)