January 30, 2007 at 8:47 am
Hi,
I'm trying to connect a msaccess file located in another PC box to get some user data, I would like to make the connection using Linked Server.
The database is secured, so I've followed the recomendations regading that:
http://msdn2.microsoft.com/en-us/library/aa238103(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/aa226395(SQL.80).aspx
But when I configure the linked server I get some errors.
As seen in the sql help, the sql string to get the linked server is:
EXEC sp_addlinkedserver
@server = 'MyServer',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = '\\192.168.4.185\MyShared\casj.mdb'
I've tested using the .mdb file in a local folder to. But the the Access Workgroup Information file is not helping me with the login.
I've found the following method that works in the Query Analyzer:
Select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=C:\casj.mdb;Jet OLEDB: Database Password=test')...Table1
...if the AD HOC queries were permitted (I don't know exactly what this means).
but, how can Add it to my linked servers.
Can you help me to troublesshot it ?
Thanks,
CS
January 30, 2007 at 9:53 am
Someone please correct me if I am wrong, but after trying to do what you stated, I was told that the Access database has to be on to server in order to link to it.
Russel Loski, MCSE Business Intelligence, Data Platform
February 1, 2007 at 10:06 am
Can you tell me what the error is you get when you run sp_addlinkedserver? Your syntax looks correct.
Paul
- Paul
http://paulpaivasql.blogspot.com/
February 1, 2007 at 12:08 pm
The linked server is added ok, but the when I try to get the data the error is:
----------------
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyServer" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyServer" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyServer".
----------------
As I mentioned, the access .mdb is secured, so I've followed the recomendation posted at:
http://msdn2.microsoft.com/en-us/library/aa238103(SQL.80).aspx
http://msdn2.microsoft.com/en-us/library/aa226395(SQL.80).aspx
but, I'm still having problems with this linked server.
CS
February 1, 2007 at 12:13 pm
Can you please show a sample of the calling code which generates that error.
- Paul
http://paulpaivasql.blogspot.com/
February 1, 2007 at 12:32 pm
Select * from MyServer...table1
GO
-----------------------
The problem I think is in the "Microsoft.Jet.4.0.OLE DB Provider".
When I test the connection using a .udl file it works fine, but I don't know how to tell SQLServer that have to use the same connection string.
The string used by the .uld is:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Data Source=testcasj
"testcasj" is an ODBC created for the test which works ok.
Q: How do I proper set up my linked server to use the "Microsoft OLE DB Provider for ODBC" instead of the "Microsoft.Jet.4.0.OLE DB Provider"
CS
February 1, 2007 at 1:27 pm
When you say the databases is secured, do you mean that it has a password? If so, try this, where password is "yaya". I believe the provider you are using (Jet) is already correct.
EXEC
sp_addlinkedserver
= 'TestLinkAccess',
@Provider
= 'Microsoft.Jet.OLEDB.4.0',
@SrvProduct
= 'OLE DB Provider for Jet',
@DataSrc
= 'C:\Paul\db1.mdb',
@ProvStr
= ';pwd=yaya;'
- Paul
http://paulpaivasql.blogspot.com/
February 1, 2007 at 1:45 pm
Thats ok, the db has a password.I've tested your script with the correct password, but the error is the following:
--------------
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess" returned message "Not a valid account name or password.".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess".
---------------
Can you send me the db1.mdb database that you have used for your test ?
Thanks a lot,
CS
February 1, 2007 at 1:53 pm
If it is the database password, you need a different element try:
Database Password=MyDbPassword;
rather than pwd=MyDbPassword
Russel Loski, MCSE Business Intelligence, Data Platform
February 1, 2007 at 1:58 pm
Here is an issue:
Your script worked ok in SQL2000 but not in SQL2005. But I need it to work in my SQL2005 becuase I've to migrate from 2000.
CS
February 1, 2007 at 2:00 pm
Here's all my code. I'm using SQL 2005, developer version.
EXEC sp_addlinkedserver
@server = 'TestLinkAccess',
@Provider
= 'Microsoft.Jet.OLEDB.4.0',
@SrvProduct
= 'OLE DB Provider for Jet',
@DataSrc
= 'C:\Paul\db1.mdb',
@ProvStr
= ';pwd=yaya;'
SELECT
*
FROM
TestLinkAccess...Table1
- Paul
http://paulpaivasql.blogspot.com/
February 1, 2007 at 2:25 pm
Could it be the Jet driver ? or I'm with a stupid think !
What about the registry entries for the Jet Engine ?
The message regarding the "Workgroup information file" can be my key, but I don't know how to hadle it.
CS
February 1, 2007 at 2:37 pm
So did the db I sent work for you? If not, then you have a tough situation.
Next I would suggest re-applying sp1 of SQL 2005 - it has been known to be problematic on the first install and has cause lots of issues.
After that, I suggest modifying the environment (remove security, change file location to a different machine, change machine from which you call the code, change SQL 2005 machine) until it works, then add them back one-at-a-time to try and gain insight into what your particular environmental problem is.
Good luck.
Paul
- Paul
http://paulpaivasql.blogspot.com/
February 1, 2007 at 6:32 pm
After several tries I've found the solution for the local access .mdb file:
1.Use the provided script by Paul:
EXEC sp_addlinkedserver
@server = 'TestLinkAccess',
@Provider = 'Microsoft.Jet.OLEDB.4.0',
@SrvProduct = 'OLE DB Provider for Jet',
@DataSrc = 'c:\casj.mdb',
@ProvStr = ';pwd=test;'
2.Configure a Workgroup Information file (MyWIF.mdw) with an user and password different than the Admin. Here is where MSAccess is needed.
3.Change the registry key:
HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\SystemDB to point MyWIF.mdw
4. Use the sp_addlinkedsrvlogin with that user and password defined in the Workgroup Information file:
exec sp_addlinkedsrvlogin
TestLinkAccess ,
false,
null,
'MyUser',
'MyPwd'
5. Test the of the data:
Select * from TestLinkAccess...table1
-----------
But, if the file is in the network, the
@DataSrc = 'c:\casj.mdb'
must be changed to
@DataSrc = '\\Server\Shared\casj.mdb',
Then, I've this error:
----
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess" returned message "Disk or network error.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TestLinkAccess".
----
And again, this remote .mdb works fine in SQL2000 but not in SQL2005
What can be done ?
Thanks,
CS
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply