locked
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed RRS feed

  • Question

  • User-1251655565 posted

    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.

    What is this Error means?

    i need to convert excel sheet 2003 to database sql

    i use sql server 2008,visual studion 2008

    any suggestions ?

    Friday, November 4, 2011 6:53 AM

Answers

  • User-718146471 posted

    Did you do what he said below on the SQL 2008? If not, I would go try this on that and give it another go.

     

    sp_configure 'Ad Hoc Distributed Queries',1
     GO
     RECONFIGURE;
     go
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=d:\temp\ActionResult.xls','select * from [sheet1$]')
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2011 8:14 AM

All replies

  • User-718146471 posted

    I think the reason you're getting that error is because of the nature of MS Excel it only allows a single connection. All connections to excel from what I have found are made in exclusive mode which means only one connection. Its possible that running distributed queries may open more than one connection. What I do in these cases is export data from the excel to flat file, then import that flat file (CSV or tab delimited) to the sql.

    Friday, November 4, 2011 7:44 AM
  • User1049502825 posted

    Hello,

    please configure ad hoc distrubuted quiries to true instead of false at SQL SERVER configuration otherwise call following both SQL statement to set it.

     

    sp_configure 'Ad Hoc Distributed Queries',1
     GO
     RECONFIGURE;
     go
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=d:\temp\ActionResult.xls','select * from [sheet1$]')
    

     

    MARK AS ANSWER IF THIS WORKS....

    Friday, November 4, 2011 8:08 AM
  • User-1251655565 posted

    http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx

    i applied this example in two ways

    the first is in VS2005,SQL server 2005, windows xp and it's succesful and done

    the second way VS2008, sql server 2008, windows 7 then i got this 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.
    also i enabled ad hoc queries and distributed queries
    any help please ?
    Asp.net c#

    Friday, November 4, 2011 8:11 AM
  • User-718146471 posted

    Did you do what he said below on the SQL 2008? If not, I would go try this on that and give it another go.

     

    sp_configure 'Ad Hoc Distributed Queries',1
     GO
     RECONFIGURE;
     go
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=d:\temp\ActionResult.xls','select * from [sheet1$]')
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 4, 2011 8:14 AM
  • User1049502825 posted

    Hello,

    please configure ad hoc distrubuted quiries to true instead of false at SQL SERVER configuration otherwise call following both SQL statement to set it.

     

    i already test this with SQL2008, Visul studion 2008 & 2010 and Windows 7...its works perfectly.....

     

    first use this to configure....

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

     

    then use this to get your data:

    select * from  OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=d:\temp\ActionResult.xls','select * from [sheet1$]')

     

    still not works then please send me private message with your xls....so i will try and give you proper solution..

    Friday, November 4, 2011 8:19 AM
  • User1049502825 posted

    Hello,

     

    Please use following code to run XLS Import....

    MARK AS ANSWER IF THIS WORKS...

    CREATE PROCEDURE GetData --'D:\TEMP\NEW.XLS'
    (
    	@filepath varchar(300)
    )  
    as
    begin
    exec sp_configure 'Ad Hoc Distributed Queries',1 
     
    RECONFIGURE; 
     
    DECLARE @SQL VARCHAR(1000)
    SET @SQL = 'SELECT * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;HDR=YES;Database=' + @filepath  + ''',''select * from [11103112$]'')'
    exec(@SQL)
    end
    
                //using store procedure
                SqlConnection conn = new SqlConnection(@"Server=PCT152\SQLEXPRESS;Persist Security Info=True;Password=tatva;User ID=sa;Database=TestvISHAL;Connect Timeout=200;");
                conn.Open();
                SqlCommand cmd = new SqlCommand("GetData", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@filepath", "d:\\temp\\new.xls");
    
                SqlDataAdapter da = new SqlDataAdapter();
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(dt);
    
    
    OR
    
                //using text
                string sql = @"select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=d:\\temp\\new.xls','select * from [11103112$]')";
                SqlConnection conn1 = new SqlConnection("Server=PCT152\\SQLEXPRESS;Persist Security Info=True;Password=tatva;User ID=sa;Database=TestKaushik;Connect Timeout=200; pooling='true';Max Pool Size=200");
                SqlCommand cmd1 = new SqlCommand(sql, conn1);
                cmd1.CommandType = System.Data.CommandType.Text;
                conn1.Open();
                SqlDataAdapter da1 = new SqlDataAdapter();
                da1.SelectCommand = cmd1;
                DataTable dt1 = new DataTable();
                da1.Fill(dt1);
    Monday, November 7, 2011 4:54 AM