修正SQLSERVER package连接

最新推荐文章于 2021-04-13 06:26:24 发布

aynbxl8574

最新推荐文章于 2021-04-13 06:26:24 发布

阅读量202

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/CoreXin/p/8308444.html

版权

本文详细介绍了如何使用T-SQL脚本批量修改SQL Server中DTS包的连接管理器属性，通过XML命名空间和XQuery进行精确的节点定位与属性替换，实现对多个DTS包的自动化更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

drop table #temp_mt;
 --WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS)
 SELECT ROW_NUMBER() OVER ( ORDER BY id ) AS idx ,
        id ,
        name ,
        packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
 INTO   #temp_mt
 FROM   dbo.sysdtspackages90


DECLARE @idx INT,@id UNIQUEIDENTIFIER,@xml XML,@maxIdx INT;
SELECT @maxIdx = MAX(idx) FROM #temp_mt;
SELECT @idx = 1;
WHILE @idx <= @maxIdx
BEGIN    
    SELECT @xml = packageXML,@id = id FROM #temp_mt WHERE idx = @idx;            
    --SELECT @xml,@id;
    
    SET @xml.modify('declare namespace DTS="www.microsoft.com/SqlServer/Dts"; 
                        replace value of (/DTS:Executable/DTS:ConnectionManager/DTS:ObjectData/DTS:ConnectionManager/DTS:Property/text())[2]
                        with 
                        "server=''.'';Trusted_Connection=true;Application Name=''Microsoft SQL Server Management Studio'';Pooling=false;Packet Size=4096;multipleactiveresultsets=false;"');    
--    SELECT @xml,@id;
    update dbo.sysdtspackages90 
    set packagedata = CAST(@xml as VARBINARY(MAX))
    WHERE id = @id;
    SET @idx = @idx+1;
END