How to create a Linked Server from Sql Server to .dbf tables using Advantage ODBC or OLEDB?
views:
24answers:
1
+4
A:
You can use the OLE DB or ODBC driver. I have typically used the OLE DB driver.
I found it easiest to use the GUI to create it first, then have it generate the SQL commands.
But...
Here are some commands for one I was using to test recently.
EXEC master.dbo.sp_addlinkedserver @server = N'DBF_TEST', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'c:\ads\dbftest', @provstr=N'servertype=ads_remote_server;tabletype=ads_cdx;'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DBF_TEST',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
Then to select you have to use the OPENQUERY syntax
SELECT * FROM OPENQUERY(dbf_test, 'SELECT * FROM table1')
Edgar
2010-08-23 15:45:52
Wow, thanks so much.Am I correct that the .dbf must exist on the same physical machine?
WoundedEgo
2010-08-24 12:10:10
I am assuming you are asking if the DBF must be on the same machine as the Advantage Server. The answer no, however I recommend you keep the data on the same machine as the Advantage server.If you want the data on a different machine or NAS this page can help (I believe it was first supported in 8.x)http://devzone.advantagedatabase.com/dz/WebHelp/Advantage10/index.html?master_network_attached_storage_nas_devices.htm
Edgar
2010-08-24 20:47:33