 | |
Re: Linked SQL Server to Access
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Linked SQL Server to Access -
02-22-2005
, 03:58 PM
|
Hi, I now this question has been posted a few times, but I did not find an answer. I have a linked server on my SQL server to an access database via a mapped drive on a domain. (2 computers, one with SQL server and the other has the access database) When I log in to query analyzer using windows authentication I run a stored procedure to query data from the access database via a linked server, everything works fine. When I log in as SA, I get this error"It is already opened exclusively by another user, or you need permission to view its data" The same is with the DTS package, if I right click the package and execute the DTS, it works. But if I create a job to call the DTS it gives me the same error, even if I say that my domain username is the password. I even ran DTS via command prompt and that too works. I know it's some kind of permission level, but I don't know what to do. I changed my c drive (the one with the access database) security to everyone. I changed the access database security to everyone too. Any one knows how to fix this? |
#2
| |||
| |||
|
|
HI, You are correct, I did not have my proxy set. After I changed both my proxy and EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 I still get the same error. What else should I try? "oj" wrote: Your sqlagent proxy is probably not set with proper access. http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp Also, be aware that SP3 has added a new flag. You might have to set it to your environment. e.g. --restrict to admin only. 1=yes, 0=no EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 go -- -oj "André" <Andr (AT) discussions (DOT) microsoft.com> wrote in message news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35 (AT) microsoft (DOT) com... Hi, I now this question has been posted a few times, but I did not find an answer. I have a linked server on my SQL server to an access database via a mapped drive on a domain. (2 computers, one with SQL server and the other has the access database) When I log in to query analyzer using windows authentication I run a stored procedure to query data from the access database via a linked server, everything works fine. When I log in as SA, I get this error"It is already opened exclusively by another user, or you need permission to view its data" The same is with the DTS package, if I right click the package and execute the DTS, it works. But if I create a job to call the DTS it gives me the same error, even if I say that my domain username is the password. I even ran DTS via command prompt and that too works. I know it's some kind of permission level, but I don't know what to do. I changed my c drive (the one with the access database) security to everyone. I changed the access database security to everyone too. Any one knows how to fix this? |
#3
| |||
| |||
|
|
Hi, I rebooted my server and I still get the same results. I did check to make sure that my proxy is still set. and it is. Thank you, Andre "oj" wrote: Sorry for the late reply...Anyway, you might need to restart the service for this to take effect. -- -oj "André" <Andr (AT) discussions (DOT) microsoft.com> wrote in message news:8151940E-EF12-4AA3-AB40-1B267952EA1D (AT) microsoft (DOT) com... HI, You are correct, I did not have my proxy set. After I changed both my proxy and EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 I still get the same error. What else should I try? "oj" wrote: Your sqlagent proxy is probably not set with proper access. http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp Also, be aware that SP3 has added a new flag. You might have to set it to your environment. e.g. --restrict to admin only. 1=yes, 0=no EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 go -- -oj "André" <Andr (AT) discussions (DOT) microsoft.com> wrote in message news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35 (AT) microsoft (DOT) com... Hi, I now this question has been posted a few times, but I did not find an answer. I have a linked server on my SQL server to an access database via a mapped drive on a domain. (2 computers, one with SQL server and the other has the access database) When I log in to query analyzer using windows authentication I run a stored procedure to query data from the access database via a linked server, everything works fine. When I log in as SA, I get this error"It is already opened exclusively by another user, or you need permission to view its data" The same is with the DTS package, if I right click the package and execute the DTS, it works. But if I create a job to call the DTS it gives me the same error, even if I say that my domain username is the password. I even ran DTS via command prompt and that too works. I know it's some kind of permission level, but I don't know what to do. I changed my c drive (the one with the access database) security to everyone. I changed the access database security to everyone too. Any one knows how to fix this? |
#4
| |||
| |||
|
|
This is the error i get in query analyzer Server: Msg 7399, Level 16, State 1, Procedure UpdateSQL, Line 3 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file '\\mypath'. It is already opened exclusively by another user, or you need permission to view its data.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. "André" wrote: I tried this on another Sql server and I get the same results. Do I have to be an Admin on the network for this to work? Andre "André" wrote: I still get an error  "oj" wrote: Andre, How's about dropping and recreating the link like so: --Linked server to Access on Shared drive EXEC sp_addlinkedserver @server = 'myAccess', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = '\\ghost\ghost\oj\Access.mdb' GO --Everyone connects via Admin Access db user EXEC sp_addlinkedsrvlogin 'myAccess', 'false', NULL, 'Admin', NULL go -- -oj "André" <Andr (AT) discussions (DOT) microsoft.com> wrote in message news:0B8EF739-AFA3-4C60-91CD-E3E1AEA2D62D (AT) microsoft (DOT) com... Hi, I rebooted my server and I still get the same results. I did check to make sure that my proxy is still set. and it is. Thank you, Andre "oj" wrote: Sorry for the late reply...Anyway, you might need to restart the service for this to take effect. -- -oj "André" <Andr (AT) discussions (DOT) microsoft.com> wrote in message news:8151940E-EF12-4AA3-AB40-1B267952EA1D (AT) microsoft (DOT) com... HI, You are correct, I did not have my proxy set. After I changed both my proxy and EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 I still get the same error. What else should I try? "oj" wrote: Your sqlagent proxy is probably not set with proper access. http://msdn.microsoft.com/library/de...aa-sz_8sdm.asp Also, be aware that SP3 has added a new flag. You might have to set it to your environment. e.g. --restrict to admin only. 1=yes, 0=no EXECUTE msdb..sp_set_sqlagent_properties @sysadmin_only = 0 go -- -oj "André" <Andr (AT) discussions (DOT) microsoft.com> wrote in message news:A056532D-4BA3-4E23-ACFE-F54ED06B3B35 (AT) microsoft (DOT) com... Hi, I now this question has been posted a few times, but I did not find an answer. I have a linked server on my SQL server to an access database via a mapped drive on a domain. (2 computers, one with SQL server and the other has the access database) When I log in to query analyzer using windows authentication I run a stored procedure to query data from the access database via a linked server, everything works fine. When I log in as SA, I get this error"It is already opened exclusively by another user, or you need permission to view its data" The same is with the DTS package, if I right click the package and execute the DTS, it works. But if I create a job to call the DTS it gives me the same error, even if I say that my domain username is the password. I even ran DTS via command prompt and that too works. I know it's some kind of permission level, but I don't know what to do. I changed my c drive (the one with the access database) security to everyone. I changed the access database security to everyone too. Any one knows how to fix this? |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.