OPENROWSET

Last Post 18 Apr 2003 10:33 AM by claudia. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Roshec
New Member
New Member

--
08 Apr 2002 02:55 PM
To anyone who can answer this...please do!!!

Ok, here's what happening

1. SQL Server on WIN2K machine
2. I'm managing the SQL Server from my work pc (Win XP)
3. There's an Access DB in one of the directories on the WIN2K machine (where SQL Server sits)
4. I'm trying, through SQL Query Analyzer, to get some data from that Access DB I keep getting an error message (see below) when I'm login using in the query analyzer using Windows Authentication.

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: Unspecified error]

5. When I login using the account "sa" as user account and "admin" as password via SQL authentication, 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 '\\Idtsqldev\D_IDTSQLDEV\idt_fa\37356000.MDB'. It is already opened exclusively by another user, or you need permission to view its data.]

6. When I login using an account I created in SQL Server ("Test" as user, "test" as password), I get this

Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

7. This is my query using OPENROWSET
Select * from OpenRowset('Microsoft.Jet.OLEDB.4.0','\\Idtsqldev\D_IDTSQLDEV\idt_fa\37356000.MDB';'Admin';'',Product)

8. I'm using OPENROWSET because this Access DB is created at runtime and I need to combine data from this access db with data from SQL Server database, and I need to do it in one SQL Statement, otherwise it'll be really slow.

Thanks in advance!!!






claudia
New Member
New Member

--
18 Apr 2003 10:33 AM
BOL says: To create a linked server to access an Access database

Execute sp_addlinkedserver to create the linked server, specifying Microsoft.Jet.OLEDB.4.0 as provider_name, and the full path name of the Access .mdb database file as data_source. The .mdb database file must reside on the server. data_source is evaluated on the server, not the client, and the path must be valid on the server.

IS this saying that the .mdb has to reside on the server where sql instance is running? (Need a confirmation. To me, it's very reasonable for data souces to be on another machine.)

Claudia
You are not authorized to post a reply.

Acceptable Use Policy