我的意思是当表or033100中某个字段发生变化时,把inserted表里面,也就是发生变化的行提取出来,导出为txt格式!EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
这两条命令是启用bcp的,由于打开这两个选项有安全性问题,所以在执行完毕之后,需要再次关闭
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
这两条命令是启用bcp的,由于打开这两个选项有安全性问题,所以在执行完毕之后,需要再次关闭
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
declare @date varchar(8),@filename varchar(30),@sql varchar(200)
set @filename='c:\PL'+convert(char(8),getdate(),112)+'.txt'
set @sql='bcp "Select * from #inserted" queryout'+' '+@filename+' '+'-c -t \t -SCTCNL066 -Uscca -Pscca'
这里的局部临时表有生命周期和作用域的问题,bcp时访问不到的。
用实表,或者全局临时表来处理。 但是会有并发问题。
那试试在trigger中调用存储过程,应该可以实现你的需求.
CREATE PROCEDURE USP_ExportPL
@inserted TABLE
(
OrderNo VARCHAR(12),PlNo VARCHAR(12),CustomerCode VARCHAR(8),DelAddr varchar(36),DelTerm VARCHAR(6),
ContactName VARCHAR(25),ContactNo INT(16),ConfirmedDelDate DATETIME,WH VARCHAR(12),StockCode VARCHAR(16),
Linenumber INT(3),Description VARCHAR(25),OrderQty DECIMAL(10)
)
ASMsg 156, Level 15, State 1, Procedure USP_ExportPL, Line 2
Incorrect syntax near the keyword 'TABLE'.
DROP TRIGGER UTR_ExportPL
Go
CREATE TRIGGER UTR_ExportPL ON sdbc2..OR03t100
AFTER UPDATE AS
IF UPDATE(or03013)
IF (SELECT 1 FROM tempdb..sysobjects WHERE NAME='##insert' ) IS NOT NULL
DROP TABLE ##INSERTSELECT OR01001,OR03051,OR01004,SL01002,C.OR04003,OR01013,OR01018,OR01072,OR03001,OR03019,OR03046,OR03005,OR03002,OR03006,
OR03011 into ##insert FROM inserted
LEFT JOIN OR01t100 ON OR03001=OR01001
LEFT JOIN SL01t100 ON OR01004=SL01001
LEFT JOIN (
SELECT OR04001,OR04003 FROM OR04t100 RIGHT OUTER JOIN or01t100 ON OR04001=OR01001 WHERE OR04003 IS NOT NULL
AND or04001=(SELECT TOP (1) or03001 FROM inserted)
UNION ALL
SELECT A.OR01001,B.SL14004 FROM (
SELECT OR01001,OR01004 FROM OR04t100 RIGHT OUTER JOIN or01t100 ON OR04001=OR01001 WHERE OR04003 IS NULL
AND or01001=(SELECT TOP (1) or03001 FROM inserted)
) A
LEFT JOIN (SELECT * FROM SL14t100 WHERE SL14002='00') B ON A.OR01004=B.SL14001
) as C
ON OR01001=C.OR04001EXEC USP_ExportPL
DROP TABLE ##insertIF OBJECT_ID ('USP_ExportPL', 'P') IS NOT NULL
DROP procedure USP_ExportPL
Go
CREATE PROCEDURE USP_ExportPL
AS
declare @date varchar(8),@filename varchar(30),@sql varchar(2000)
EXEC sp_configure 'show advanced options',1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell',1
RECONFIGURE
set @filename='c:\PL'+convert(char(8),getdate(),112)+'.txt'
OR01001,OR03051,OR01004,SL01002,C.OR04003,OR01013,OR01018,OR01072,OR03001,OR03019,OR03046,OR03005,OR03002,OR03006,OR03011 FROM ##insert LEFT JOIN OR013100 ON OR03001=OR01001 LEFT JOIN SL013100 ON OR01004=SL01001 LEFT JOIN(SELECT OR04001,OR04003 FROM OR043100 RIGHT OUTER JOIN or013100 ON OR04001=OR01001 WHERE OR04003 IS NOT NULL AND or04001=(SELECT TOP (1) or03001 FROM ##insert) UNION ALL SELECT A.OR01001,B.SL14004 FROM (SELECT OR01001,OR01004 FROM OR043100 RIGHT OUTER JOIN or013100 ON OR04001=OR01001 WHERE OR04003 IS NULL AND or01001=(SELECT TOP (1) or03001 FROM ##insert)) A LEFT JOIN (SELECT * FROM SL143100 WHERE SL14002='+'''00'''+') B ON A.OR01004=B.SL14001) as C ON OR01001=C.OR04001" queryout'+' '+@filename+' '+'-c -t \t -S CTCNL066 -U scca -P scca'
SET @sql='bcp "select * from ##insert" queryout'+' '+@filename+' '+'-c -t \t -SCTCNL066 -Uscca -Pscca'
EXEC master..xp_cmdshell @sql
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
这两个结合起来能够实现我的要求,但是现在还有一个问题就是EXEC master..xp_cmdshell @sql
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
这个直接放在sp里面会报错
如何解决?因为涉及到安全性问题,在打开以后,必须马上关闭