使用SQL Server导入/导出Excel,包含部分错误信息处理方法;
操作手记,留此备查
/*
导入
*/
--错误信息如下时：
--Msg 15281, Level 16, State 1, Line 2
--SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
--请选择执行下面语句,修改默认配置,在进行导入操作
-- begin
sp_configure 'show advanced options',1
RECONFIGURE WITH override
go
sp_configure 'Ad Hoc Distributed Queries',1 -- 1:启用,默认0:禁用
RECONFIGURE WITH override
go
EXEC sp_configure;
go
-- end
-- 查询
select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=D:\ExcelSheet1.xls',sheet1$)
go
-- 查询并导入
select * into ExcelSheet from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=D:\ExcelSheet1.xls',sheet1$)
go
/*
导出
*/
--错误信息如下时：
--Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
--SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
--请选择执行下面语句,修改默认配置,在进行导出操作
-- begin
sp_configure 'show advanced options',1
RECONFIGURE WITH override
go
sp_configure 'xp_cmdshell',1 -- 1:启用,默认0:禁用
RECONFIGURE WITH override
go
EXEC sp_configure;
go
-- end
--添加到现在Excel文档
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 5.0;HDR=YES;DATABASE=D:\ExcelSheet.xls',sheet1$)
go
--带连接信息导出
EXEC master..xp_cmd
刚在看书，提到了sql server的模式匹配运算，接着想到了通配符的转义问题，因为太久没用，Google了搜索了一下才想起来，写几句话记录下。 关于通配符的转义，sql server里边提供了关键字escape来处理。但是escape本身不是什么转义符（刚才我就是在这里搞错了），而是将escape后面的符号定义为转义符。举个例： select ......
