需求环境:数据表增加一条数据时在对应的目录下生成一个文本文件记录数据库的记录提供给第三方程序使用。
使用xp_cmdshell 默认情况下没有被打开
1.打开启用xp_cmdshell
--开启xp_cmdshell部分
--------------------------------------------------
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
--通过xp_cmdshell执行shell命令的部分
--------------------------------------------------
Exec xp_cmdshell 'bcp '
GO
--关闭xp_cmdshell部分
-----------------------------------------------------
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
2.使用xp_cmdshell 生成txt 文件
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[table_trg]
ON [dbo].[table]
AFTER INSERT
AS
DECLARE @content nvarchar(1024),@id nvarchar(1024)
DECLARE @lReturn int
DECLARE @cmd nvarchar(1024),@var nvarchar(1024)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select top 1 @content= name,@id= id from [table] order by id desc
-- Insert statements for trigger here
SET @var = @content
SET @cmd = N'echo ' + N'"' + @var + N'"' + N'>d:/workspace/' + @nid + '.txt'
EXEC tpm..xp_cmdshell @cmd
END