OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode

Instead of wasting time working with driver issues I end up using a format file for the openrowset command.

Details of the syntax for this file are located here:

 http://msdn.microsoft.com/en-us/library/ms191479(v=sql.120).aspx 

I created a file with just string datatype(dataf.fmt)

14.0

6

1  SQLCHAR  0  255 ","      1  domain               ""

2  SQLCHAR  0  255 ","      2  dn                        ""

3  SQLCHAR  0  255 ","      3  NetbiosName  ""

4  SQLCHAR  0  255 ","      4  firstn                   ""

5  SQLCHAR  0  255 ","      5  lastn                   ""

6  SQLCHAR  0  255 "\r\n" 6  fulln                   ""

 

I was then able to query my sampledata.csv file by using:

 

select  a.*  from openrowset(bulk 'c:\temp\SampleDataShort.csv', FORMATFILE= 'c:\temp\dataf.fmt') as a

 

If you have questions send them to mark att net4geeks dott com. 

 

Before going with using a format file I tried:

 

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1;
GO
RECONFIGURE;
GO

EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.JET.OLEDB.4.0′, N’AllowInProcess’, 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N’Microsoft.JET.OLEDB.4.0′, N’DynamicParameters’, 1
GO

Then I would run a query to access the the csvfile:

Select * from OpenRowSet('Microsoft.JET.OLEDB.4.0','Text;Database=C:\temp\;','select * from [SampleData.csv])

This would result in an error:

OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode

This is apparently the result of a 64 bit Server / 32 bit driver conflict.

So I decided to try a 64 bit driver:

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft ACE OLE DB 12.0 Provider', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft ACE OLE DB 12.0 Provider', N'DynamicParameters', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft ACE OLE DB 12.0 Provider', N'AllowInProcess', 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft ACE OLE DB 12.0 Provider', N'DynamicParameters', 1

GO

Now when I run:

SELECT * FROM OPENROWSET('Microsoft ACE OLE DB 12.0 Provider','Text;Database=C:\temp\;','SELECT * FROM [SampeData.csv]')

I get a different error:

The OLE DB provider "Microsoft ACE OLE DB 12.0 Provider" has not been registered.