MSSql中启用xp_cmdshell调用第三方程序以及遇到中文路径时报错的解决方案

最新推荐文章于 2026-09-02 12:11:43 发布

原创最新推荐文章于 2026-09-02 12:11:43 发布 · 2k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

数据库

本文介绍如何在SQL Server中使用触发器配合xp_cmdshell功能，在数据表插入新记录时自动生成TXT文件，详细步骤包括启用及禁用xp_cmdshell、编写触发器等。

需求环境：数据表增加一条数据时在对应的目录下生成一个文本文件记录数据库的记录提供给第三方程序使用。

使用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