obscure errors querying linked server from workstation

[josephwalter]

I have a linked server set up on SQL Server that is pointed at an Access 2000 database. I've successfully created a view in one of my SQL Server databases that queries certain data from this linked server.

I can run the view from the server machine with no problems, but when I try to run it on my workstation (using Enterprise Manager), I get the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message: Unspecified error]

I have another (possibly related) problem when working at my workstation. When I expand Security > Linked Servers > myLinkedServerName and click on Tables, the get the following error:

Error 7399: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.

These error messages are not giving me any direction! Please help.

 

[klgrube]

Hi!

I'm having precisely the same problem! The issue seems to be that the SQL Server Service account doesn't have rights on the networked drive. Additionally the service account has to be in the local admin group on its own server, and have full rights to the C: drive and to the folder specified by the TMP windows variable. This is in addition to needing rights on the networked drive.

If I come up with any other solutions, I'll be glad to let you know. Please let me know if you're able to do this.

Thanks!
Karen Grube
klgrube@aol.com
kgrube@ffres.com

 

[josephwalter]

I would think that the SQL Server Service account has permissions because I have no problems using the linked servers while I'm on the server (logged in as the server admin). It's when I'm trying to access anything from my workstation (connecting with my personal login) that I get the errors.

 

[tlbroadbent]

Have you read the entry in SQL BOL for "Error 7399?"

http://msdn.microsoft.com/library/en-us/trblsql/tr_reslsyserr_2_6jzt.asp

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.

 

[josephwalter]

tlbroadbent -

The Access database is an unsecured database, and the server logins are mapped to Admin with no password.

 

[josephwalter]

Below is an alternative solution for making my Access data available on SQL Server (through linked servers), since using the Jet Provider returned error 7399.

After setting up an ODBC connection on the database server (pointed at my Access file) I created a Linked Server using the ODBC Provider - specifying the ODBC name as my Data Source. Under security, I mapped all accounts to Admin with no password.

This works well with Access 2000 databases. Unfortunately, my actual data source is an Access 97 database that could not be converted to 2000.

Distributed queries resulted in Error 7356 "... supplied inconsistent metadata for a column...". Therefore, I created an Access 2000 file and linked all the tables from my 97 data source. Then I pointed the ODBC connection at this Access 2000 database.

Distributed queries are successfull, although I cannot view the list of tables under the Linked Server branch in Enterprise Manager.

Summary:

Access 97 > Access 2000 (linked tables) > SQL Server Linked Server (using ODBC provider)

I hope sharing my solution helps others. If anyone has questions or suggestions regarding the above, please feel free to share. Thank you.