使用c＃将excel数据插入sql server时出错 [英] Error while insert excel data to sql server using c#

问题描述

嗨朋友



我正在使用

Visual Studio 2010

sql server 2008（64位）

ms office 2007（32位）

以下是我的sql查询

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 

推荐答案

''）'


exec（@sql）
update Generalholyday_details set createddatetime = @getdate其中createddatetime为null
update Generalholyday_details set adminid = @adminid，其中adminid为null
结束

GO

'')' 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 

尝试将ad-hoc分布式quires配置为true ...

Try configuring ad-hoc distributed quires to true...

sp_configure 'Ad Hoc Distributed Queries',1

并运行查询....



链接：

HTTP：//博客。 sqlauthority.com/2010/11/03/sql-server-fix -error-MS-喷射OLEDB-4-0-不能待使用换分布式查询-因为最提供商被使用的对运行中的公寓模式/ < a href =http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-因为 - 提供者是用于在公寓中运行模式/target =_ blanktitle =新窗口>

http://forums.asp.net/t/1736766.aspx/1



版本2：

由于Microsoft.Jet.OLEBD.4.0不支持64位SQL Server。如果我们想操纵excel文件，请使用 Microsoft.ACE.OLEDB.12.0

链接：

http：// www.maxxxie.net/2012/07/01/the-joys-of-importing-excel-data-into-sql-2008-r2/

http://www.sqlteam.com/forums /topic.asp?TOPIC_ID=168190

http://stackoverflow.com/questions/15434748/how-to-find-out-which-ole-provider-available-for-sql-server-2008

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/33436d82-085c-43e4-b991-a2d0d701c8fc/linking-to-csv -files-on-64-bit-sql-server

and run the Query....

Links:
http://blog.sqlauthority.com/2010/11/03/sql-server-fix-error-ms-jet-oledb-4-0-cannot-be-used-for-distributed-queries-because-the-provider-is-used-to-run-in-apartment-mode/
http://forums.asp.net/t/1736766.aspx/1

Version 2:
Since Microsoft.Jet.OLEBD.4.0 doesn’t support 64-bit SQL Server. If we want to manipulate excel file, please use Microsoft.ACE.OLEDB.12.0
Links:
http://www.maxxxie.net/2012/07/01/the-joys-of-importing-excel-data-into-sql-2008-r2/
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=168190
http://stackoverflow.com/questions/15434748/how-to-find-out-which-ole-provider-available-for-sql-server-2008
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/33436d82-085c-43e4-b991-a2d0d701c8fc/linking-to-csv-files-on-64-bit-sql-server

这篇关于使用c＃将excel数据插入sql server时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！