EXCEL数据导入SQL-优快云博客

本文链接：https://blog.youkuaiyun.com/xiaruilin/article/details/7821381

本文介绍了解决SQLServer在尝试从Excel文件导入数据时遇到的“AdHocDistributedQueries”组件被阻止的问题。提供了详细的步骤来临时启用并随后禁用此功能，确保数据导入过程顺利完成。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL Server 阻止了对组件 \'Ad Hoc Distributed Queries\' 的访问

在Sql Server中查询一下Excel文件的时候出现问题：
XLS文件：

insert into 表名(字段...) SELECT 字段... FROM OPENROWSET( 'MICROSOFT.JET.OLEDB.4.0','Excel 8.0;IMEX=1;HDR=YES;DATABASE=D:\a.xls',[sheet1$])

--前提该表未创建，aa表必须没有情况下才成功

XLSX文件：SELECT * INTO aa FROM OPENDATASOURCE('Microsoft.Ace.OLEDB.12.0',
'Data Source=D:/测试.xlsx;Extended Properties=Excel 12.0')...[sheet1$]

结果提示：
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。
查询相关资料，找到解决方法：

启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

使用完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

IMEX=1;代表数字与中文混合（不然中文会乱码或者NULL） 0代表数字

注意：导入的XLS文件的第一行数据比较含中文混合体，不然不支持IMEX=1的效果

//----根据系统需要使用这几段批处理命令打开相应功能
           //--启用Ad Hoc Distributed Queries：
           Stopwatch sw=new Stopwatch();
           sw.Start();

           OpenFileDialog ofd = new OpenFileDialog();
           ofd.Filter = "表格文件|*.xls";
           if (ofd.ShowDialog() == DialogResult.OK)
           {

               string path = ofd.FileName.TrimEnd();
               //导入xls文件前，必须开启安全选项才可以
               string OpenAdHoc = "exec sp_configure \"show advanced options\",1 ;reconfigure;exec sp_configure \"Ad Hoc Distributed Queries\",1; reconfigure";

               //有错？？？？？？？
               string insertdata = string.Format(@"insert into jc_peifang select * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source=""{0}"";User ID=Admin;Password=;Extended properties=Excel 5.0')...[sheet1$]", path);

//导入xls文件后，关闭安全选项
string ColsedAdHoc = @"exec sp_configure ""Ad Hoc Distributed Queries"",0; reconfigure ;exec sp_configure ""show advanced options"",0 ;reconfigure";

               SqlHelper.ExecuteNonQuery(OpenAdHoc,CommandType.Text);
               //数据导入时，xls文件必须要关闭，不然导入会失败
               int rt = SqlHelper.ExecuteNonQuery(insertdata,CommandType.Text);

               if (rt > 0)
               {
                   MessageBox.Show("成功导入" + rt + "条记录!");
                   SqlHelper.ExecuteNonQuery(ColsedAdHoc,CommandType.Text);
                   button2_Click(this, e);
                   button4_Click(this, e);

               }
               else
               {
                   MessageBox.Show("导入失败!");
               }
               sw.Stop();
               MessageBox.Show(sw.Elapsed.ToString());