存储过程分自定义存储、系统存储、扩展存储
三种存储中扩展存储过程陌生点,什么是扩展存储过程呢?扩展存储过程提供SQL Server 到外部程序的接口,以便进行各种维护活动。也就是在SQLServer中执行SQL系统之外的命令功能。
例如我要在SQL中执行操作系统的xcopy命令,把本地备份的数据库拷贝到远程共享服务器上,如下命令:
EXEC XP_CMDSHELL 'XCOPY E:\Source \\Target
结果提示以下错误(说xp_cmdshell扩展存储过程出于安全考虑被关闭,需要用sp_configure开启)
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.
下面就将开启的过程说下:
-- 允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
-- 重新配置
RECONFIGURE
GO
-- 启用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--重新配置
RECONFIGURE
GO
转载于:https://blog.51cto.com/mage20081021/1536932