Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DTS and MS-Access

Status
Not open for further replies.
Fubear

Fubear

IS-IT--Management
Sep 11, 2002
299
GB
I am trying to export some data from SQL Server to a table in MS-Access. I have setup a DTS object that pulls the data out in a query, drops and rebuilds the table in access, then copies the data over.

Running the DTS package manually, everything works.

When I schedual the package, the scheduled event fails. Looking at the error log, all the actions fail with an "Unspecified Error", ecpect for one:

---
Error: -2147467259 (80004005); Provider Error: -534709256 (E020FBF8)

Error string: The Microsoft Jet database engine cannot open the file '\\UNC\Path\To\File\Database.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
---

The ServerAgent is set to use the SA login, and I checked that it definately has access to the file on the network. I also checed the no-one was using the file at the time.

The fact that it works when I run the package from DTS>Local Packages manually, and not when i schedule it for a weekly run is really confusing me.

Is there any known problem wusing SQL Server 7 with Access 2000? I can only think that it is opening the databse to connect, then trying to establish a second connectionwhile the database is still opened (and locked) from the first attempt.
 
Sort by date Sort by votes
Help!
Still stuck with this.

The DTS wont run manually when other users are using the databse, which is opened before I start and closed after I finish.

Currently I cannot update the access databse at all, and I'm just waiting for the call from the team using it that thir data isnt avaliable.

I still cant understand why it works manually when i run it from enterprise manager, but not as a scheduled event :eek:(
 
Upvote 0 Downvote
DTS is a very temperamental thing, as I have discovered myself. Is SQL server sitting on a clustered server? I have experienced a problem before when a DTS package would not execute when the server was running on one node of the server but was fine when running on the other node. If this is the case, look at your server setup.
Also, try running the DTS package from the repository rather than as a local package, this again is something I have seen happen and is usually down to configuration of SQL server

JC
 
Upvote 0 Downvote
Thanks for the help, but still no luck getting this to work.

I tried recreating the package in the repository, and it still didnt work when scheduled.

I'm no SQL server, or database expert, so I dont know what a clustered server is or how it works. We have several SQL server instances running on several servers, but their data/tables are not linked in any way.


 
Upvote 0 Downvote
have you looked into changing the database connection from SQL to your access database? the default connection is ODBC I think, try changing your DTS to OLEDB when you create the DTS.

just a thought.
 
Upvote 0 Downvote
Not entirely sure what you mean...

Looking at the connection types, it is currently set to MS-Access. The OLEDB options are 'OLE DB For SQL Server', 'OLE DB for Oracle', etc

These options ask for server names and logins, not pathnames for files.
 
Upvote 0 Downvote
Check the sql agent account and its access on MS_Access DB subdirectory
 
Upvote 0 Downvote
Sabnac - could I get a little more detail on this? I am not sure what I should be looking for.

I'm not a network admin, and dont know the SA login/pass, I only have sysadmin rights on the SQL server.

The server can definately see the file, as I get the "cannot get exclusive access..." rather than "file not found..." error.

As an update - I CAN run the DTS script manually and it will update the table without errors even when the other users are in it. I can do it manually once a week, but that means it makes it a little awkward to go on holiday or leave the company if I cant schedule it.
 
Upvote 0 Downvote
Your sql server agent servcice run under a certain system account. This account try to reach the specified file and have not the rights to do it. Check the account and ask your system manger to give access to the directory where the Access DB is located.

 
Upvote 0 Downvote
WE have already checked this.
the DBA logged in with the SA account and was able to access the file fine :eek:(
 
Upvote 0 Downvote
If the sql server agent account have read/write access to the directory i dont see the problem sorry.

Same problem for me has been solved by giving rights to the directory.
 
Upvote 0 Downvote
I have had a few words with the Database Admin, and the server is setup to use the SQL Server 'SA' account for the server agent.

The machine itself is logged in with the generic admin password. So in theory it should be able to see everything on the network.

For security reasons, we dont want to run the sever with a network admin account running as the esrver agent.

I have tried copying the file to a public directory everyone can access, and I have tried changing the path from N:\folder\filename to \\Server\Share\folder\filename

No luck on each of htem, exactly the same probelm.
 
Upvote 0 Downvote
Is there a system database (Workgroup) associated with the Access database that you need to log into in the connection string? That would be the 'or you need permissions part'. The error message does not seem to be pointing towards a problem with your SQL Server connection but the Access connection.

------------------------------------
Error string: The Microsoft Jet database engine cannot open the file '\\UNC\Path\To\File\Database.mdb'. It is already opened exclusively by another user, or you need permission to view its data.



---------------------
scking@arinc.com
---------------------
 
Upvote 0 Downvote
No, there is no Access Workgroup defined for the databse, the admin login with blank password is all that is needed to get into the system.
 
Upvote 0 Downvote
Hello

I have the same problem with the DTS package. I must import Access DB into my SQL Server. When I run the package manual there is no problem, but when I schedule the DTS job, I always got an error.

I found a document on the site of Microsoft. Perhaps this will help your problem. It is a bug in SQL Server 7.


I tried everything that is on the site, but no luck for me.

I installed ADO 2.5 (before it was 2.1), installed SP4 for SQL Server (before it was SP2)

If you have a solution, please let me know

Thanks

Karel

karel.denaeghel@barco.com
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bethabernathy
  • Locked
  • Question
Front End Access 365 Database dsn Connection Fails when attempting to link to 2016 SQL server
Replies
1
Views
175
dhookom
dhookom
DougNaf
  • Locked
  • Question
SQL 2016 - DB attachment and error 3415
Replies
1
Views
152
fredericofonseca
fredericofonseca
JoPaBC
  • Locked
  • Question
Delphi/Borland program and SQL Express 2017
Replies
6
Views
273
JoPaBC
JoPaBC
JoPaBC
  • Locked
  • Question
Schedule and automate backups of SQL Express databases in SQL Server Express 1
Replies
5
Views
281
JoPaBC
JoPaBC
duartevds
  • Locked
  • Question
Access SQL Server remotely from Another City 1
Replies
1
Views
87
gbaughma
gbaughma

Part and Inventory Search

Sponsor

Back
Top