November 17, 2011 at 11:27 am
Does anyone have a working linked server to a folder of text files in 64 bit yet?
I know the Jet driver was supposedly replaced with the new ACE driver for Office 64 bit;
However, I don't seem to be able to use it to replace some previous functionalities...
With the Jet driver, you could set up a Linked server to a folder full of text files Microsoft BOL Linked Servers, which i had done lots of times previously, and posted lots of forum examples here to boot.
now, with my 64 bit 2008, after installing the 64 bit AccessDatabaseEngine_x64.exe drivers, i cannot do the same;
i'm getting this error:
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TxtSvr" reported an error. Provider caused a server fault in an external process.
Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "TxtSvr". The provider supports the interface, but returns a failure code when it is used.
for reference, here is the exact code that i'm using to create my linked server to the folder c:\Data:
--#################################################################################################
--Linked server Syntax for Folder Full Of Text Files
--#################################################################################################
--add a folder as a linked server to access all .txt and .csv files in the folder
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
SET @server = N'TxtSvr'
SET @srvproduct = N'OLE DB Provider for ACE'
SET @provider = N'Microsoft.ACE.OLEDB.12.0'
SET @datasrc = N'C:\Data'
set @provstr = 'Text'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,NULL,@provstr
--===== Create a linked server to the drive and path you desire.
--EXEC dbo.sp_AddLinkedServer TxtSvr,
-- 'MSDASQL',
-- 'Microsoft.ACE.OLEDB.12.0',
-- 'C:\',
-- NULL,
-- 'Text'
GO
--===== Set up login mappings.
EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL
GO
--===== List the tables in the linked server which is really a list of
-- file names in the directory. Note that the "#" sign in the
-- Table_Name is where the period in the filename actually goes.
EXEC dbo.sp_Tables_Ex TxtSvr
GO
--===== Query one of the files by using a four-part name.
SELECT *
FROM TxtSvr...[xmlmap#txt]
--===== Drop the text server
EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'
GO
Lowell
November 21, 2011 at 11:40 am
bumping my own thread; someone out there manage to get a folder of text files worrking in 64 bit?
Lowell
November 21, 2011 at 12:57 pm
I am using 64 bit SSRS to read a text file into a report using the ACE driver. Make sure you install the 64bit version of the driver.
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\\Server\Share\;Extended Properties="text;HDR=YES;FMT=Delimited"
June 26, 2013 at 7:15 pm
Did you ever get an answer on this?
June 27, 2013 at 5:06 am
i did get it working on my own;
If you've installed the 64 bit drivers, and also set these two properties on the drivers:
--Required settings for the provider to work correctly as a linked server
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
this code specifically let me see every csv/txt file and open them via a linked server:
--#################################################################################################
--Linked server Syntax for Folder Full Of Text Files
--#################################################################################################
--add a folder as a linked server to access all .txt and .csv files in the folder
DECLARE @server sysname,
@srvproduct nvarchar(256),
@provider nvarchar(256),
@datasrc nvarchar(100),
@location nvarchar(100),
@provstr nvarchar(100),
@catalog sysname,
@sql varchar(1000)
SET @server = N'TxtSvr'
SET @srvproduct = N'OLE DB Provider for ACE'
SET @provider = N'Microsoft.ACE.OLEDB.12.0'
SET @datasrc = N'C:\Data\'
SET @provstr ='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Data\;Extended Properties="text;HDR=YES;FMT=Delimited" '
set @provstr = 'Text'
EXEC sp_addlinkedserver @server,@srvproduct,@provider,@datasrc,@provstr,@provstr
--===== Create a linked server to the drive and path you desire.
--EXEC dbo.sp_AddLinkedServer TxtSvr,
-- 'MSDASQL',
-- 'Microsoft.ACE.OLEDB.12.0',
-- 'C:\',
-- NULL,
-- 'Text'
GO
--===== Set up login mappings.
EXEC dbo.sp_AddLinkedSrvLogin TxtSvr, FALSE, NULL, Admin, NULL
GO
--===== List the tables in the linked server which is really a list of
-- file names in the directory. Note that the "#" sign in the
-- Table_Name is where the period in the filename actually goes.
EXEC dbo.sp_Tables_Ex TxtSvr
GO
--===== Query one of the files by using a four-part name.
SELECT *
FROM TxtSvr...[LEgalName_NickName_List#txt]
--===== Drop the text server
-- EXEC dbo.sp_DropServer 'TxtSvr', 'DropLogins'
GO
Lowell
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply