baskaran chellasamy

baskaran chellasamy

  • NA
  • 114
  • 150.6k

error when export excel file to sql server using c#

Jul 24 2013 2:07 AM
Hi friends

  I am using
      Visual studio 2010
      sql server 2008 (64 bit)
      ms office 2007 (32 bit)
  The following is my sql queries

ALTER procedure [dbo].[sp_excelforgeneralholidays](@filname nvarchar(max),@getdate datetime,@adminid int)
 as  
 begin
 
 declare @datavar nvarchar(max)
 declare @sql varchar(1000)
 --set @getdate=coalesce(@getdate,Getdate())
 set @datavar  = 'Excel 12.0;Database=' + @filname
 
 set nocount on
   --create table #temptable (Date date ,Day varchar(30),Reason varchar(100))
  set @sql ='insert into Generalholyday_details(Date,Day,Reason) SELECT * FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'','''+@datavar+';HDR=YES'',''SELECT Date,Day,Reason FROM [Sheet1$]'')'
  
  
   exec (@sql)
   update Generalholyday_details set createddatetime= @getdate where createddatetime is null
   update Generalholyday_details set adminid=@adminid where adminid is null
   end

GO

    the following error appears

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



I changed the following also, but still error occurs

exec sp_configure 'show advanced options', 1  reconfigure  GO  exec sp_configure 'Ad Hoc Distributed Queries', 1  reconfigure 





Answers (1)