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 : 04:32:50
|
| Ciao a tutti,Sapete come fare per eseguire una query da SQL Server 2005 che acceda ad una tabella in un file Access?Da SQL Server ho creato un linked server chiamato ‘nomi’ con la stored procedure sp_addlinkedserver:EXEC sp_addlinkedserver@server = 'nomi', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet', @datasrc = '\\nome_server\cartella_1\nome_access.mdb' Il file access (nome_access) si trova sullo stesso server su cui risiede SQL server. Ho poi eseguito il seguente script usando l’istruzione OPENQUERY. select * from OPENQUERY (nomi,'select * from names') go "names" è la tabella (l’unica) contenuta nel file Access. SQL mi dà il seguente errore: 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". C'è qualcuno che sa com’è la procedura corretta?Grazie mille per l'aiuto. Max |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
maxsql
Starting Member
4 Posts |
Posted - 2007-11-02 : 06:18:45
|
quote:
Originally posted by madhivanan
Can you post question in English?For Openquery error, seehttp://sqlteam.com/forums/topic.asp?TOPIC_ID=49926MadhivananFailing to plan is Planning to fail
Yes, I have translated my question, here it is in English: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 |
 |
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2007-11-03 : 16:21:16
|
| [code]EXEC sp_addlinkedserver @server = N'nomi', @provider = N'Microsoft.Jet.OLEDB.4.0', @srvproduct = N'OLE DB Provider for Jet', @datasrc = N'\\server_name\folder_1\access_name.mdb' --Make sure the service that is running sql has access to this locationGO-- Set up login mapping using current user's security contextEXEC sp_addlinkedsrvlogin @rmtsrvname = N'nomi', @useself = N'TRUE', @locallogin = NULL, @rmtuser = N'Admin', @rmtpassword = NULLGO-- List the tables on the linked serverEXEC sp_tables_ex N'nomi'GO-- Select all the rows from the 'names' tableSELECT * FROM nomi...names[/code] |
|
|
|
|
|
|
|
|
Query da SQL Server 2005 ad una tabella Access