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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy