SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Linked server sql to access database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 03/24/2013 :  14:37:03  Show Profile  Reply with Quote
I have two server

Server A -- Sql server 2008

server B MS Access

I define linked server Sql server to Ms Access.using this command.

EXEC sp_addlinkedserver
@server = N'test4',
@provider = N'Microsoft.ACE.OLEDB.12.0',
@srvproduct = N'Access',
@datasrc = N'\\192.168.1.9\TESTSERVER\ACCESS.mdb',
@provstr = N';PWD=ABC'

after execute this command

SELECT * FROM test4...EMPLOYEES
this commandd execute succesfuly.

after create sql server agent job
and job step command
SELECT * FROM test4...EMPLOYEES
and execute job but given error
error is :

executed as user deneme\egemen cannot initialize the datasource object of oledb provider "Microsoft.ACE.OLEDB.12.0" for linked server test4[sql state 42000] error [7303]
test4 returned message:the microsoft access database engine cannont open or write \\192.168.1.9\TESTSERVER\ACCESS.mdb. it is already opened exclusive by another user you need permission view or write data

how can i solve this problem tahnk you


James K
Flowing Fount of Yak Knowledge

3562 Posts

Posted - 03/24/2013 :  14:52:37  Show Profile  Reply with Quote
Does anyone have the access file opened exclusively?

If that is not the problem, check if the login account used to run the SQL Server agent has access to that folder and the mdb file. You can find the login account from SQL Server Configuration manager (in SQL Server Program group) or run
wmic service where "name Like '%Agent%'" get Name , StartName
Go to Top of Page

egemen_ates
Yak Posting Veteran

Turkey
76 Posts

Posted - 03/24/2013 :  15:19:01  Show Profile  Reply with Quote
I solve problem i open configuration manager change sql server and sql server agent user

This operation maybe create some problem is it possible ?

quote:
Originally posted by James K

Does anyone have the access file opened exclusively?

If that is not the problem, check if the login account used to run the SQL Server agent has access to that folder and the mdb file. You can find the login account from SQL Server Configuration manager (in SQL Server Program group) or run
wmic service where "name Like '%Agent%'" get Name , StartName


Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000