Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
maxsql
Starting Member
4 Posts |
 Posted - 2007-11-02 : 06:38:10
|
| I have translated my question and re-posted it with also the title translated in English. Here it is the post:Hi everybody,Do you know how to do to write a query in SQL Server 2005 to access data in a table of an Access file?From SQL Server I created a linked server called ‘nomi’ with the stored procedure “sp_addlinkedserver”:EXEC sp_addlinkedserver@server = 'nomi', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = '\\server_name\folder_1\access_name.mdb' The access file (access_name) is situated in the same server where SQL Server resides.I then executed the following script with the OPENQUERY instruction:select * from OPENQUERY (nomi,'select * from names') go "names" is the table (the only one) contained in the Access file. SQL Server gives me the following error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "nomi" reported an error. The provider did not give any information about the error. Msg 7321, Level 16, State 2, Line 1 An error occurred while preparing the query "select * from names" for execution against OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "nomi". Is there anybody who knows the correct procedure?Many thanks for your help.Max |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2007-11-02 : 11:46:52
|
| Normally, you don't need to use a stored procedure to add a linked server. You could just go in management studio and add it there.When you said "access data in a table of an Access file", I assum you want to import data into sql server. You could use SSIS for that. Linked server will serve the porpose of leaving the data in Access and query them with the sql server data. |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-02 : 22:38:52
|
| If access file is on sql server's local disk, you don't need unc name. Other thing to try is creating linked server with odbc provider. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-11-03 : 20:54:52
|
| I just posted the answer to this at the link below.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91978 |
|
|
|
|
|
|
|
|
Query from SQL Server 2005 to an Access table