| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-06-24 : 06:22:52
|
| hello gurus.I have a weird problem, I think its to do with permissions, but not sure.If I run a DTS package that copies data from an access db to a sql db to runs successfully, howevre when I schedule the task I getthe error in the jobs history:The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permissions to view the data.how can I get around this problem ?thanksJamie |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-24 : 06:30:03
|
| Does the access db use a workgroup file?? If so, you'll need to add the SQLAgent account to the workgroup... |
 |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-24 : 07:04:43
|
| no, no workgroup file, just a standard mdb file.although somebody does have the database open at present.but surely that wouldn't affect the job not running. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-24 : 07:08:47
|
| for more info. the DTS calls an ODBC connection to the access db. within the ODBC I select a database and it is marked as readonly, but not exclusive.would the read only effect this ? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-24 : 07:22:58
|
| SQL needs exclusive full control of the Access db to do it's work, so yes, it would affect it... Try making a copy of the mdb and testing your package on the copy... If that works then you know what you need to do... |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-24 : 07:40:23
|
| I have made a copy, change the odbc to the copy, ra nthe job and it still fails.looks like it is failing when copying data from a query to a sql table.odd, that it works through dts, execute.any other Suggestions Rick ?thanks |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-24 : 07:54:35
|
| Do you have a logfile set up on your DTS package? If not set one up, this usually gives you more int he way of errors and messages... |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-24 : 08:17:23
|
| Oh and it could also be that you have an older version of mdac on your server than you do on your local machine... When you execute the dts manually, you are uysing your local drivers and machine settings, when you schedule it you are using the servers drivers and settings...I would check you have the same drivers and mdac version first... |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-24 : 08:34:38
|
| looking atthe DTS ( I didn't create it, done by a 3rd party), they are using a DSN also they are hard coding the database location, so if I copy the database I will need to change the DTS packages code as well.( not allowed to do this ).You said earlier that sql needs exclusive access to do its stuff, if I want to copy the data during working hours I can't ask the users to close the db, the only other option is to setup an xcopy of somehing to copy the mdb to another location then use this, and change the DTS code to this db.does that make sense ? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-24 : 09:08:17
|
| Yeah, that makes sense... What I meant was that if another user is using the data you are trying to copy, the dts may well fail...Have you checked the MDAC level? |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-25 : 04:30:01
|
| Mdac ? could that be an issue ? but it works from DTS level.Looks to me like permissions problems, Nobody is using the database and I still get the same error message when running the job.I have added the user that executes the job to the folder and the database so that user has permissions, but it still fails.to look at another way around this, is it possible to execute a job as a different user ? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-25 : 04:38:53
|
| thanks Rick, I will try this out and let you know what happens. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-25 : 04:44:57
|
| Rick the computer is At Mdac level 2.7so mdac is not an issue |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-25 : 05:23:47
|
| Is the server at the same level as your local pc? |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-25 : 05:27:55
|
| this is so strange, I have another job that accesses a different access database, this one was working yesterday, but now I get the :The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permissions to view the data.error when the job runs. nothing has been changed as far as I know... |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-25 : 05:47:54
|
| Are you sure a password hasn't changed?!?!? Or permissions to directories?!?This seems very strange, but I do have a nagging feeling at the back of my mind that this has happened to me before, just can't remember what the problem was... |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-25 : 05:52:18
|
| Also, have a look at...http://support.microsoft.com/default.aspx?scid=kb;EN-US;306269 |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-25 : 05:54:12
|
| Rick, you're right, permissions to the directories have changed.bloody network guys.looks like the account that runs sql jobs needs full control permissions through all of the folders,eg, if you have a db at D:\foldre1\folder2\folder3\database.mdb,each of the folder and the drive needs permissions.i hate permissions. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-25 : 05:56:44
|
Got there in the end, I hate network guys...  The reason it fails is because it also needs access to the system.mdw... |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-06-25 : 06:02:01
|
| I'm still have problems on the first database I mentioned, correct permissions but still fails.you mention system.mdw file, the databse doesn't use one of those. |
|
|
|
Next Page
|
DTS runs successfully, as a job it fails