I have built several Linked Servers from my SQLServer 7 database to some Access 97 MDB files sitting on a Novell file server. When I am physically sitting at the SQLServer I can access/modify data through the links just fine. I have successfully built views, stored procs and jobs referencing these links and they all work perfectly ... As long as I am sitting at the SQLServer. So if I execute 'Select * from RT1...Emp' I get data back.
Now my problem. If I connect to the server from Query Analyser on a different machine I can not execute and anything that references a linked server. If I execute 'Select * from RT1...Emp' I get.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error
[OLE/DB Provider returned message: DIsk or Network error.]
I want to be able access various views and stored procs via a VB app so. Eventually I will be moving all of the Access data into the SQLServer but for now I must access the data via links.
I have setup a linked server to a Sybase database using the ODBC driver (which in turn uses the Sybase OLE DB provider). Logged onto the box as the SQL Service account everything is good. Using Enterprise Manager from any machine to the SQL Server box using an account that is a local administrator on the SQL Server box everything is good. However, if I attempt to make a connection via Enterprise Manager with an account that does not have local administrator rights on the server I get an error. I get the same error if I attempt an OPENQUERY against a table in the Linked server database. The error I receive is as follows:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [SYBASE][ODBC Sybase driver]Allocation of a Sybase Open Client Context failed. Sybase normally generates a SYBINIT.ERR file contianing more specific reasons for failing.] OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
The drives are secured so that only local administrators have rights to them.
I found in the Microsoft KB the follownig article: http://support.microsoft.com/?kbid=814398
In this article it is suggesting that I need to open up the server from this lockdown. So, just to test this out without having to restart SQL Server I first allowed for full access to the C drive to "Everyone" and everything was good.
My question then is, has anybody else ran into this sort of thing? It doesn't seem right that I have to open up some folder on the database server to everyone in order to get a linked server going.
I am having trouble linking to a Sybase server using the ODBC OLEDB provider, with either the sp_addlinkedserver stored proc, or the "linked servers" wizard in Enterprise manager.
The SQL I am executing is:
/* add server */ exec sp_addlinkedserver @Server = 'Linked Server Name', @srvproduct = 'Sybase System 11', @Provider = 'MSDASQL', @provstr = 'Driver={INTERSOLV 3.11 32-BIT Sybase};SRVR=servername;DB=targetdatabase;UID=bob; PWD=bob;'
(Note that this does not use a DSN - I have tried it both ways, but with no joy).
The SQL executes without any errors or warnings. However, when I try to use the linked server connection (eg., visually list the tables available by double clicking the linked server in the Enterprise Manager, or Select * From a table in the Query Analyser) it give the following error:
"Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error." (That is - generic error)
It just seems that I am not putting the connect string components in the right places.
Does anyone have any suggestions on how to set up a linked server to Sybase in this this way?
I'm trying to connect to an Oracle DB from my SQL 2000 server. I have the oracle client installed and I am able to use SQLPlus to connect to the oracle service and run queries. I set up the linked server just like I always do, but now when I attempt to access one of the tables on the linked server I get "Error 7399: OLE DB provider 'MSDAORA' reported an error. I am able to use the OLE DB provider for ODBC and connect with ODBC to oracle, but not with the MSDAORA OLEDB provider.
Windows 2000 Advanced Server, Service Pack 2; SQL 2000 Server
select * from openquery (DMserver, 'SELECT DISTINCT [Gender] FROM TM_Cluster')
(I referred to 'Executing predictions from the SQL Server relational engine' article on this web site)
But I am getting following error :
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSOLAP" for linked server "DMserver" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSOLAP" for linked server "DMserver".
I am trying to import an Excel 2007 file into SQL Server 2005 Express using linked servers. My linked server was created without error as follows:
Linked server: XLWorkbook Provider: Microsoft Office 12.0 Access Database Engine OLE DB Provider Product Name: Excel Data source: D:spreadsheet.xlsx (D: is on the same PC as the SQL Server) Provider string: Excel 12.0
However, when I tried to import using
select * from OPENQUERY(XLWorkbook, 'Select * From [Sheet1$]')
I get:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLWorkbook" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "XLWorkbook".
I had previously used the same exact query for Excel 2003 on Windows XP and it worked fine without me having to do anything else like doling out access rights.
What do I have to do to get this to work on Windows Vista (and Office 2007)?
We recently translated the backend db from Access(97) to SQL Server.We are still using Access frontends. I have an update query in theAccess front end that uses a lookup table to populate fields. Thecommon fields between the table and the lookup table are the primarykey (LocID) and date & time fields. The query is:UPDATE tblPT_Offsets INNER JOIN tblPT ON tblPT_Offsets.LocID =tblPT.LocID SET tblPT.Offset_ft = [tblPT_Offsets].[Offset_ft],tblPT.Salinity = [tblPT_Offsets].[Salinity]WHERE (((tblPT.Offset_ft) Is Null) AND ((tblPT.Salinity) Is Null) AND((Format([Date]+[Time],"mm/dd/yy hh:nn")) Between [StartDate] And[EndDate]));This worked fine in Access and seemed to work fine after switching toAccess, but on closer look, there is exactly a 2 day error beingintroduced. A quick search of the newsgroups brings up lots of Accessto SQL date problems, but a 2 day offset seems rather strange? Anyideas??I know the field names Date and Time are inappropriate, but legacyissues are a pain in the butt to resolve!! Could this be a problem?David
I'v finally managed to get a New Linked Server setup on SQL 2005 Express SP2 using the 'Microsoft OLE DB Provider for ODBC Drivers' MSDASQL. I have a Database setup on the SQL 2005 Express Database Engine which contains a query that OPENQUERY's the linked server. The query executes OK.
The problems in when I link an Access 2003 SP2 .adp file to the SQL 2005 Database Engine database. All objects, including the query containing the OPENQUERY (to the linked server) appear. However, when I run the same query within Access I get the following error:
"The OLE DB Provider for Linked Server "name" reported an error." "The Provider reported an unexpected catastropic failure"
So the query works running within SQL 2005 Management Studio, but the same query doesn't work running within an Access 2003 SP2 ADP. I've closed down SQL 2005 Management Studio, but the same error is displayed.
I didn't know if the problem lies in Access 2003 SP2 or SQL 2005 Express SP2.
Any takers?? (I haven't logged this Thread in an Access forum)
Hallo i'm trying to connect a VFP DBC as a linked server on MSSQL2000sp3a, using following parameters:
Product name: VFP
Data source: \networkpath odbc
Provider: VFPOLEDB.1
No security context I'm trying the same operation both from a W2000sp4 server (+ MDAC 2.8sp1 ?) and a WXPPsp2, against two identical copies of the DBC that resides on two different path (one on the final path and one on a test path). From WXPPsp2 everything runs ok, from W2000 i'm always getting this error: Error 7399: OLE/DB Provider 'VFPOLEDB' IUnknown::QueryINterface returned 0x80070005: Access Denied (already reported in this thread, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1832362&SiteID=1 but it didn't helped me)
The error happens independently from the user the query runs under (basicly AD account, both domain admin and normal user). MSSQL runs under a domain account which have full permissions (for test purpose) on the directories where DBC reside.
I did no further modifications on the WXP machine in order to have the linked server running, simply it ran smoothly from the beginning.
Clearly the difference rely on the different OS... but what else?
i ran component checker to determine the MDAC version but some components version numbers slightly differs from the expected ones (eg. resulting version is 2.0.1064 versus 2.0.1022 expected in MDAC 2.8) so i suppose to have MDAC2.8 sp1 on the server. thanks in advance
Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "VFPOLEDB" for linked server "tele" reported an error. Access denied. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "VFPOLEDB" for linked server "tele".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2" returned message "The Microsoft Jet database engine could not find the object 'prospect_divu'. Make sure the object exists and that you spell its name and the path name correctly.". Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset "DBSCHEMA_COLUMNS" for OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "tele2". The provider supports the interface, but returns a failure code when it is used.
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success. Any help is appreciated.
I have two sql server 2000 with mixed mode authetication. I stand at one server and setup a linked server to the other using the same user id and password. However when I click to the Tables icon of linked server in Enterprise manager, there is an error message: Error 17 SQL Server does not exist or access denied. And It does not show any table. I register remote sql server in Enterprise manager fine. Could any one help me ? Thanks.
I am using SQL 2000 (Standard Edition, SP4) and have created a linked server to access a Access MDB file. When I run a simple query against that server, I get the error:
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: System resource exceeded.] OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' ICommandText::Execute returned 0x80004005: ].
I am able to connect to this same Access MDB file from another SQL server (SQL 2000 Enterprise Edition, SP4). I have compared the two servers and they both have the same version of MDAC (2.8) and the same version of the Jet OLE DB Provider.
Any idea why this would work from one server and not from the other. Also, I was able to perform the same queries from the first server a week back.
My link server was working just fine until friday evening. It stopped worked over the week end. with and error Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error .
---my oracle 10g client is working just fine --TNS names looks fine ---i recreated the link but i am still getting the same error.
I need your help because a lot of jobs are using that link on Monday it is going to be crazzzy.
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I'm trying to set up a linked table from SQL Server 2005 to MS Access. The .mdb file resides on a network share and I'm unable to connect to it. If i copy the database to a folder on the server that SQL is installed, it works fine. Any help would be greatly appreciated. Below is the error message that I'm receiving.
TITLE: Microsoft SQL Server Management Studio ------------------------------
"The linked server has been created but failed a connection test. Do you want to keep the linked server?"
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER". OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "LINKSERVER" returned message "The Microsoft Jet database engine cannot open the file '\serversharedatabase.mdb'. It is already opened exclusively by another user, or you need permission to view its data.". (Microsoft SQL Server, Error: 7303)
I have access97 front end , with linked tables to sql server 2005. when i change a table ID column (type int) to primary key so it is updatable, then try open the table i get odbc error with no msg, if i alter table again to remove primary key option table opens fine but is not updateable. any ideas would be a great help
OS: Windows XP (SP2) App: MS Access 2003 (SP2) DB: MS SQL 2000 (SP4, 2040)
User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:
"Unexpected Error from External Database Driver (22)."
A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).
Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.
I have SQL Server 7.0 running on a NT server. I need to get data from an access database on another NT server on the network. Can I have it mapped to a logical drive and create a linked server and get data to be used in my stored procs. Please help !! Thanks
I am running SQL Server 7.0 and am trying to set up a linked server to an access database so I can use a SQL7 stored procedure to update the access database.
I create the linked server as: sp_addlinkedserver @server = N'LinkedServer', @srvproduct = N' ', @provider = N'Microsoft.Jet.OLEDB.4.0', @datasrc = N'MyServerest2.mdb', @catalog = N'' GO
I then try setting up the security in the security tabs to impersonate a user I have set up in the access database.
I also have NT security on the directory of the access database to limit the users who can access the directory.
My problem is that when I try to run the sql: "select * from LinkedServer...IMS" (IMS is the tablename) I get the following error: Server: Msg 7399, Level 16, State 1, Line 1 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 'ASPECT04accpactestest2.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
If I add "Everyone" tpo the security level on the NT directory it works fine, so I obviously have a security problem.
Can anyone tell me what user the sql is running under.
I am new to SQL server. I have a pbm setting up a linked server to MS Access using ODBC. My MS Access database is in the Netware server. I have mapped the NT server to this Netware server using a binary account. Then I have setup the ODBC connection. I tested the ODBC its ok. But when I try to view to tables in the Linked server it gives me an error "Disk or Network Error". I am breaking my head for the past few days. If you have any solution. Pls reply. Thanks in advance.
After converting to SQL Server 2005 and VS2005 I am having a problem connecting to Linked Servers for Access databases. I have searched and read everything I can find, but the solution is still not evident. I have a lot of data integration and data conversion procedures to run and I am stuck at this point not understanding exactly what the problem is and how to resolve it. I believe it is somehow related to "double hop" and permissions with .NET. Many of the discussions are related to using .ASP but that is not involved in this situation.
The Linked Server is defined using the UNC path. The machines are not in a domain. They are in the same LAN segment. Connections will be made without using a security context is specified. A sample Linked Server definition:
EXEC master.dbo.sp_addlinkedserver @server = N'ACCESS EDUCATION', @srvproduct=N'OLE DB Provider for Jet', @provider=N'Microsoft.Jet.OLEDB.4.0', @datasrc=N'\Tacir2k3InfrastructureDatabasesJuly 2005 DatabasesEducationEduDb2005_TR_Db.mdb'
The error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "ACCESS EDUCATION" returned message "The Microsoft Jet database engine cannot open the file '\Tacir2k3InfrastructureDatabasesJuly 2005 DatabasesEducationEduDb2005_TR_Db.mdb'. It is already opened exclusively by another user, or you need permission to view its data.".
Machine 1 (Developer Workstation): XP SP2 with latest security patches; SS Management Studio, VS2005
Machine 2 (SQL Server server): Windows 2003 Server with SS2005. The Linked Servers are defined on an SS2005 instance on this machine.
Machine 3 (File server): Windows 2003 Server that contains the Access DB files
When I run SS Management Studio on Machine 2 (the database server), I have no problem accessing the Linked Server pointing to an Access DB file on Machine 3 and can compile or run SPs referencing the Linked Server.
When I run SS Management Studio on Machine 1 to execute an SP that references the Linked Servers, I get the above error.
When I use Machine 1 to compile an SP that references the Linked Server, I get the above error.
I tried granting Everyone Full Control to the folder that contains all the Access database subfolders but I get the same error when running from Machine 1.
I am trying to configure a linked server to a local Access database with no security and only one table.
I set it up and tested on a test machine first and all works fine. I then did the same on our production box and receive the error below.
Server: Msg 7303, Level 16, State 2, Line 1 Could not initialize data source object of OLE DB provider 'Microsoft.Jet.OLEDB.4.0'. [OLE/DB provider returned message: Not a valid account name or password.]
I then set it up on another production box and it worked for a while but now I get a different error.
Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.]
Is there a server level setting that could be different on these machines that could be causing this?
I have an Access database (name "Shipping") linked to SQL Server. The Access database is not secured. My access file is located on the same machine as the instance of SQL Server, in a shared folder that can be accessed by any user. The file permissions are set to full control for Everyone. I linked Access with the following procedure:
EXEC sp_addlinkedserver @server = 'Shipping', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = 'C:ShippingShipping BackEnd.mdb'
then modified the linked server login mapping with:
Now, since I have domain administrator permissions, I can run a stored procedure in SQL Server that does a Select in the Access database without any problem. But when a normal user tries to run the procedure, he gets an error #7399. If I give this user domain administrator rights, he's able to run the procedure. There is something I don't get, with normal user permission, this user can open the Access database through the shared folder...
Hi, I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE). On the local server, I can do select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local
select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked
but not
select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]
How can I access the INFORMATION_SCHEMA on the linked server ?
I'm trying to create a linked server to an Access database that resides on a separate machine.
On my PC, I can create a link to the Access db, and view, update, add and delete data. If I create the same linked server on our production Server, I can again view, update, add and delete data. All's well so far.
If I now go back to my own PC (used for developing) I cannot access the linked server on the production machine.
The Access database is stored on a separate PC, so the I'm linking to a remote db. As I said this works fine if I'm sat in front of the PC that the linked server is created on - but not if I use a client PC to connect.
I create the linked server using the following command: exec sp_addlinkedserver @server = 'AccLinkedServer', @provider = 'Microsoft.jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = '\DatabaseServerAccessDatabase.mdb'
If I run the SQL statement:
SELECT * FROM AccLinkedServer...AnyTableYouLike
I get this error: Server: Msg 7399, Level 16, State 1, Line 1 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 '\DatabaseServerAccessDatabase.mdb'. 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: ].
I've searched in the MS knowledge base, and this forum and followed all of the advice that was available, but it still won't work.
Anyone got any other advice, before I go totally insane with this.
There seems to be a lot of confusion around the groups about linkingto an Access mdb with the SQL Server Jet OLE DB provider and I haventbeen able to find a straight forward solution. Basically, I have anAccess MDB (A2K) on one server and a SQL Server DB (2005 std ed.) onanother - Both on the same network. I'm trying to create a linkedserver object in the SQL server to view data in the mdb. I've set itup and it works - but only from the server machine itself. If you tryto connect the the linked server from any other computer on thenetwork you get the usual access denied / file in use error.The fact that I can use the linked server from the server box itselfbut not from another pc on the network makes me think that it may be apermissions problem. But I am logging in with full Admin rights andstill no joy. Also there is no workgroup security on the mdb, so thatsnot the problem. I've used the surface editor to remove anyrestrictions that may cause problems, OLE DB connect, OPENROWSET etc.but still no joy.I've tried mapping the mdb's location on the server so I could use astandard filepath, rather than a //Server-Name/... path. Again, worksfrom the server, but not from any client PCs, so no joy there either.In frustration, I copied the mdb to the same server and viola - fullaccess to the linked server from anywhere. But this is no good, I needthe mdb file to stay where it is. An mdb full of linked tables wontwork... they don't show up in the linked server.So that's it - out of ideas! Am I just going to have to accept thatlinked server objects are limited just to mdb files on the same servermachine, or is there something I'm missing??? Firewalls, servicelogins, server settings.... something one of you gurus out there knowabout that might be the key to making my headache go away!!!Any input gratefully recieved!!!
I've set up a linked server to an Access database and read from the linked database in a stored procedure. No problems when I execute the stored procedure from management studio and the Access database resides on a local drive.
However, when I move the database to a network share drive and run the proc through a scheduled job using SQL Server Agent I get the following error --
"Executed as user: C-SGROUPactprod. Access to the remote server is denied because the current security context is not trusted. [SQLSTATE 42000] (Error 15274)."
I've spent days trying to resolve this problem and tried many different setting to fix the problem, but no luck yet.
Under the Security setting for the linked server I have "Be made using this security context" selected with a valid id/password. I am able to login to the server where the database resides using the id and password and successfully open the database.
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
No matter how I configure my link going thru EM I get a 7399 or 7303 error. First I tried to link to an Access 2000 db on a Windows 98 station. Next I installed Access 2000 on the test server with SS7, SP 2 (Windows NT 4, SP 5) and still the same errors. Is it possible to set up Access 2000 as a linked server on SS7? Can you give me the brainless man's walk-thru so I can get over this hump?
Hello, We are currently live with a CRM solution (Siebel) that uses SQL Server 6.5 as the back end. All is fine and dandy, except I have some reservations about security. Quite simply, it is possible for anyone to open up MS Access and link to any of the SQL Server database tables via the ODBC DSN used by the Siebel front end. This DSN is necessary for Siebel to function. I am bit worried that someone (out of incompetence or spite) might do just that and cause some serious damage. Its probably technically beyond the large proportion of our users (especially those that could make mistakes!), but I can't get the nagging fear out of my head. Does anyone know of anyway to combat this problem? I have scoured the web, including this site, and cant seem to get any information on this. Thanks and Regards Dike
