distributed queries the provider is config single

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

distributed queries the provider is config single

Author

Topic

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-21 : 11:49:03

Please I need help:
I got the following error message on using ssis
my sql server is 64 bit
I already set adhocremotequeriesenabled to true and xpcmdshellenabled to true
"SELECT * INTO dbo.Temp_Grades_1 FROM OPENROWSET('M..." failed with the following 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.
Appreciate your help

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 12:50:32

try enabling ad-hoc distributed queries to on


sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

if it still doesnt work, try running package in 32 bit mode by setting Run64BitRuntime property to false in Project->Properties->Debugging tab

this should solve it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-21 : 13:49:23

I did all what you mentioned but I still got the same error message.

Thanks for your help

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-21 : 16:00:44

Please Please I need help.
YOUR Previous step by step worked on my computer at home but at work NO I Have sql 64 bit and
On adding a sql task
SELECT * INTO temp_grades_959 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\importallgrades\Grades\grades_1.xls'
, [Sheet1$])
I get the following error message
[Execute SQL Task] Error: Executing the query "SELECT * INTO temp_grades_959 FROM OPENROWSET('Mic..." failed with the following 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
My server is sql 64 bit but the office potion is 32 we do not have office installed on the server
I tried changing the package to run 32 bit by seting run64runtime to false
I tried configuring adhocremotequiredenabled =true xpcomdshellenabled=ture
tried installing AccessDatabaseEngine.exe
(but it did not help) I tried to give the temp directory access I tried all the items in the following link
The thing is I have on the server two folders for programfiles and programfiles (x386) the one x86 has a small folder office11 and officexx
but the programfiles has no office folder
I tried all the items in this link
http://www.excel-sql-server.com/excel-import-to-sql-server-using-distributed-queries.htm
one thing is that on trying to install

AccessDatabaseEngine.exe_64 and tried to import an excel spreadsheet 2007 it gave me an error that xx is not registered on this system.
Thanks

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-21 : 17:08:34

Another strange thing I was even trying to add excel as a linked server I did not find niehter micorosoft jet 4.0 ole db provider nor microsoft 12 access database engine old db

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:20:45

quote:
Originally posted by sarahmfr

Another strange thing I was even trying to add excel as a linked server I did not find niehter micorosoft jet 4.0 ole db provider nor microsoft 12 access database engine old db

sarah

then i think you've to get latest providers installed on your office machine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-22 : 02:06:44

do you mean
http://support.microsoft.com/kb/239114/
but this article says
For computers that are running Windows Server 2008
If you are running Windows Server 2008, you have a later version of Jet 4.0 than the version that is included with Jet 4.0 SP8.
so which one to install

sarah

sarahmfr
Posting Yak Master

214 Posts

Posted - 2011-11-22 : 14:31:50

It worked thanks

sarah

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 23:48:55

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources