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