April 19, 2012 at 9:47 am
Just figured this out today... Wanted to share...
I've gotten this to work in our new SQL 2012 RTM 64-bit servers:
This retrives data from DBase, Advantage DB, Sybase .dbf files directly using the Microsoft ACE OLEDB 12.0 64-bit drivers.
Use MyDatabase
GO
sp_configure 'show advanced options', 1
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1;
--Must use all CAPS in the query parameter section, except the file name.
SELECT COLUMN1,
COLUMN2
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','dBASE IV;Database=\\RemotServerName\ShareName\Folder1\SubFolder1\','SELECT COLUMN1, COLUMN2 FROM DBASEFIL.dbf WHERE COLUMN3 = ''C''')
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0;
GO
sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure
GO
sp_configure 'show advanced options', 0
reconfigure
GO
April 19, 2012 at 9:53 am
Cool. Submit it as an article (check the Write for us link to the left). That'll make it easier to find than a forum post, if someone needs the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 1, 2012 at 10:42 am
That's pretty cool.... if you write an article about it... try to cover linked servers to this stuff to... Not 100% sure you can do that.... since each file is separate... but it would be neat if you could.
January 3, 2013 at 7:47 am
This additionally can cause issues:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply