ʹÓÃSQL Serverµ¼Èë/µ¼³öExcel

ʹÓÃ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