| Author |
Topic |
|
jrockfl
Posting Yak Master
223 Posts |
 Posted - 2004-07-15 : 14:33:40
|
| I have created a DTS that works fine if I execute it manually under Data Transformation Services.However, it does not work when I create a schedule for it. I have created other scheduled DTS packages to different databases and they work. SQL Server Agent is running.This Access DB is on another server, I created a ODBC connection and I used guest as the username and blank password. (Instructions say to use this when creating an ODBC connection. I also referenced a MDA file.Any suggestions?[Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file is missing or opened exclusively by another user. Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 1901 (76D) Error string: [Microsoft][ODBC Microsoft Access Driver] Cannot start your application. The workgroup information file i... Process Exit Code 1. The step failed. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-15 : 14:35:59
|
| When a job runs, it runs from the perspective of the database server. Did you setup the DSN on the database server or on your machine? Can the database server get to everything using the SQLSERVERAGENT service account? I would suggest logging into the database server with the SQLSERVERAGENT service account and running the package manually from there. That would simulate what happens when a job runs as scheduled.Tara |
 |
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-15 : 14:43:07
|
| The MS Access DB is on the call center server. My workstation has SQL Server and our remote web server has SQL Server. I created the DSN on my workstation, that connects to the call center server. The DTS package on my computer, exports the data from the call center to the remote web server. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-15 : 14:46:50
|
quote:
I created the DSN on my workstation
So is your workstation also the database server? Which server is the job scheduled on? That's the server that must have the DSN and everything else on it.Tara |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-15 : 14:52:19
|
| Yes, the job is schedulted on my workstation. Yes, my computer is the DB server.If i run throught the DTS wizard, I can access the tables in the Access DB. If I run my job under the SQL Server agent, it fails. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-15 : 14:54:20
|
| Ok, then the problem is most likely with the SQLSERVERAGENT service account. You are probably using Local System Account. You'll need to switch it to an account that has acces to the remote devices. It is recommended that you use a domain account that has local admin privileges and is not an account that is used by a specific person. I recommend creating an account called sqladmin.To see the service, go to Control Panel, Admin Tools, then services. Double click on SQLSERVERAGENT service. Go to second tab. You should also change the MSSQLSERVER service account to use the same account.You will need to restart the services in order for the change to take effect.Tara |
|
|
|
jrockfl
Posting Yak Master
223 Posts |
Posted - 2004-07-15 : 15:30:50
|
| I checked the services, and both are running under a domain account, not a local system account.When I created the DSN, I had to supply the username guest and blank password and attach a system database file or it wouldnt work. Could that have something to do with this? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-15 : 15:58:45
|
| You need to log into your machine using the account setup with the services. Run the DTS package manually from that account. And yes if the package requires you to type in information, then it won't work as a job.Tara |
|
|
|
jmholden11
Starting Member
6 Posts |
Posted - 2004-10-11 : 13:55:24
|
| I'm running into almost this exact same problem, though I am running a SQL database locally. I've exhausted all user rights possibilities. The DTS jobs simply will not run automatically, but run fine when executed manually. This was not a problem until SQL service pack 3a was installed. Any ideas?-Jason |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-12 : 11:14:45
|
| Did you looked the SQL service startup accounts?Tara |
|
|
|
jmholden11
Starting Member
6 Posts |
Posted - 2004-10-12 : 14:15:42
|
| All of the accounts look fine, and it was an upgrade to Windows 2000 Service Pack 4, not the SQL upgrade, that caused the error to start occuring. When the job runs automatically, I get a "Can't find file specified" error; when I execute the job myself it works fine... if not an account privilege issue, what could a Windows 2000 service pack have done to my DTS job to make it start behaving this way? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-12 : 15:04:26
|
| The accounts that run the SQL Services...Does that account have access to the file referenced in the DTS package? Check the ACL. Is it trying to get to a network resource?The way to troubleshoot this is to logon to your machine using the account that the SQL services use. Then manually run the DTS package. You'll then be running it just like it would if scheduled.Tara |
|
|
|
jmholden11
Starting Member
6 Posts |
Posted - 2004-10-14 : 12:54:06
|
| Tara,Thanks for all your help so far. I'm running SQL under the administrator account, the same account that I use to run the job manually. Is the a SQL service user or batch job user that I might not know about that is trying to run the job?-Holden |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-14 : 13:09:46
|
| So you checked the service in Admin Tools in Control Panel? I just want to make sure we are talking apples and apples.Tara |
|
|
|
hgorijal
Constraint Violating Yak Guru
277 Posts |
Posted - 2004-10-15 : 02:17:39
|
| you probably checked this already, but have you ruled out the possibility that the Access file is opened/used by an other user/application during the time the DTS package is scheduled to run?Hemanth GorijalaBI Architect / DBA...Exchange a Dollar, we still have ONE each.Exchange an Idea, we have TWO each. |
|
|
|
jmholden11
Starting Member
6 Posts |
Posted - 2004-10-16 : 14:29:37
|
| Actually, the MSSQLSERVER service is logged on as a local user... but the SQLSERVERAGENT is logged on as the administrator. Doesn't the SQLSERVERAGENT supercede the MSSQLSERVER? Wouldn't it be running the job?Thanks Hermanth; it was a pervious poster that was using Access. I'm running SQL Server, and the file is generated for this DTS package. It isn't ever used by other users or apps. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-18 : 12:09:44
|
| Your SQL services should be using a domain account that has local admin privileges. This user should also have permissions to whatever network resources that it needs on other servers.Tara |
|
|
|
jmholden11
Starting Member
6 Posts |
Posted - 2004-10-19 : 15:12:57
|
| SQLSERVERAGENT is a domain account with local Admin rights, but MSSQLSERVER is using a local account... but the Agent runs the job, correct? |
|
|
|
K_Mueller
Starting Member
3 Posts |
Posted - 2004-10-27 : 05:49:07
|
| Hallo,if you start the job direct, it run with your account!if you start the job over the agent, it run with the agent account!But the Agent runs as windows service, and a service does not support permissions over mapped drives!If you use the workgroup information file in the ODBC connection, you must use a UNC path like "\\Server\your.mdw" not a mapped drive letter like "M:\your.mdw"!!!Klaus from Germany |
|
|
|
jmholden11
Starting Member
6 Posts |
Posted - 2004-10-27 : 13:21:19
|
| All the files being moved are local though, there's no interaction with any mapped drives. This all started with Service Pack 4 to Windows 2000... has anyone seen issues with this service pack? Thanks for your advice! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-10-27 : 13:24:57
|
| Have you tried rolling back to sp3? How about contacting MS?Tara |
|
|
|
Next Page
|
DTS and Access DB