SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 distributed queries the provider is config single
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/21/2011 :  11:49:03  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 11/21/2011 :  12:50:32  Show Profile  Reply with Quote
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/

Go to Top of Page

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/21/2011 :  13:49:23  Show Profile  Reply with Quote
I did all what you mentioned but I still got the same error message.

Thanks for your help

sarah
Go to Top of Page

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/21/2011 :  16:00:44  Show Profile  Reply with Quote
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

Edited by - sarahmfr on 11/21/2011 21:44:52
Go to Top of Page

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/21/2011 :  17:08:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/22/2011 :  01:20:45  Show Profile  Reply with Quote
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/

Go to Top of Page

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/22/2011 :  02:06:44  Show Profile  Reply with Quote
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

Edited by - sarahmfr on 11/22/2011 02:08:55
Go to Top of Page

sarahmfr
Posting Yak Master

214 Posts

Posted - 11/22/2011 :  14:31:50  Show Profile  Reply with Quote
It worked thanks

sarah
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/22/2011 :  23:48:55  Show Profile  Reply with Quote
wc

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000