如何在維護計劃作業中更改或更新本地伺服器連接

兩天前，我們的客戶更改了我們的開發伺服器名稱之一

伺服器重命名後，由於伺服器名稱不匹配，我所有的維護作業和其他作業都失敗了。

我們使用的是sql server 2012版本和server 2008 OS

所以今天早上我將我的 Sql server 2012 名稱重命名為更新的給定名稱並製作了表格，程序更新

我試圖在維護工作中更新本地伺服器連接，但它是不可編輯的。然後我添加了新的伺服器連接，仍然沒有用我在執行作業時遇到錯誤。

在我嘗試在作業屬性選項中使用目標頁面後，也只選擇了目標伺服器並且禁用了多個目標伺服器。

下面的錯誤

以使用者身份執行：NT Service\SQLSERVERAGENT。適用於 64 位的 Microsoft (R) SQL Server 執行包實用程序版本 11.0.2100.60 版權所有 (C) Microsoft Corporation。版權所有。

開始時間：上午 12:01:28 錯誤：2013-12-16 00:01:43.98 程式碼：0xC00291EC 源：{410F7661-F71A-4B68-9584-BA422AB00F02} 執行 SQL 任務

描述：無法獲取連接“本地伺服器連接” . 連接可能未正確配置，或者您可能對此連接沒有正確的權限。結束錯誤

錯誤：2013-12-16 00:02:00.00

程式碼：0xC0024104

來源：Territory_Update

說明：任務上的 Execute 方法返回錯誤程式碼 0x80131904（與 SQL Server 建立連接時發生與網路相關或特定於實例的錯誤。未找到伺服器或無法訪問伺服器。驗證實例名稱是否正確且SQL Server 配置為允許遠端連接。（提供程序：命名管道提供程序，錯誤：40 - 無法打開與 SQL Server 的連接）。Execute 方法必須成功，並使用“out”參數指示結果。結束錯誤

錯誤：2013-12-16 00:02:15.00

程式碼：0xC0024104

來源：{4E2AF328-0B8D-4905-83BE-839FDDEFC09C}

說明：任務上的 Execute 方法返回錯誤程式碼 0x80131904（與 SQL Server 建立連接時發生與網路相關或特定於實例的錯誤。未找到伺服器或無法訪問伺服器。驗證實例名稱是否正確且SQL Server 配置為允許遠端連接。（提供程序：命名管道提供程序，錯誤：40 - 無法打開與 SQL Server 的連接）。Execute 方法必須成功，並使用“out”參數指示結果。

結束錯誤 DTExec：包執行返回 DTSER_FAILURE (1)。

開始時間：上午12:01:28 結束時間

：上午

12:02:15 經過時間：46.641 秒。

包執行失敗。

步驟失敗。

幫幫我，在此先感謝

維護計劃使用儲存在 MSDB 中的 SSIS 包。這些包使用連接字元串，在伺服器重命名後不會更改。

使用NancySon在How to: Rename a Computer that Hosts a Stand-Alone Instance of SQL Server的評論中發布的腳本（轉載如下），以獲得有關如何更改這些連接字元串的一些靈感。或者重新制定維護計劃。

維護計劃不會將其連接更改為新的伺服器名稱，因此它們可能會中斷。重命名後，您可能會發現無法刪除或重命名現有維護計劃，因此請在重命名伺服器之前刪除它們並在之後重新創建它們，或者執行以下腳本來修復它們：

   use msdb

   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'

   -- set the new server name to the current server name

   declare @newservername as varchar(max)
    set @newservername=@@servername

   declare @xml as varchar(max)
    declare @packagedata as varbinary(max)
    -- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor
    FOR
    SELECT    id
    FROM         sysssispackages
    WHERE     (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%server=''' + @oldservername + '%')

   OPEN PlansToFix


   declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid

   while (@@fetch_status<>-1)  -- for each plan

   begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) from sysssispackages where id= @planid  -- get the plan's xml converted to an xml string

   declare @planname varchar(max)
    select @planname=[name] from  sysssispackages where id= @planid  -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername  -- print out what change is happening

   set @xml=replace(@xml,'server=''' + @oldservername + '''','server=''' + @newservername +'''')  -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max))  -- convert the xml back to binary
    UPDATE    sysssispackages SET packagedata = @packagedata WHERE (id= @planid)  -- update the plan

   end
    fetch next from PlansToFix into @planid  -- get the next plan

   end

   close PlansToFix
    deallocate PlansToFix

  ----- This will also handle the packages that have a tag such as 
    ----- <DTS:Property DTS:Name="ConnectionString">Data Source=servername;Integrated Security=SSPI;Connect Timeout=30;</DTS:Property>



   DECLARE @oldservername as varchar(max)
    SET @oldservername='<server name>\<instance name>'-- set the new server name to the current server name
    declare @newservername as varchar(max)
    set @newservername = @@servername
    declare @xml as varchar(max)
    declare @packagedata as varbinary(max)-- get all the plans that have the old server name in their connection string
    DECLARE PlansToFix Cursor FOR 
    SELECT id
    FROM sysssispackages
    WHERE (CAST(CAST(packagedata AS varbinary(MAX)) AS varchar(MAX)) LIKE '%Data Source=' + @oldservername + '%')

   OPEN PlansToFix
    declare @planid uniqueidentifier
    fetch next from PlansToFix into @planid 
    while (@@fetch_status<>-1) -- for each plan 
    begin
    if (@@fetch_status<>-2)
    begin
    select @xml=cast(cast(packagedata as varbinary(max)) as varchar(max)) 
    from sysssispackages where id= @planid -- get the plan's xml converted to an xml string
    declare @planname varchar(max)select @planname=[name] from sysssispackages where id= @planid -- get the plan name
    print 'Changing ' + @planname + ' server from ' + @oldservername + ' to ' + @newservername -- print out what change is happening
    set @xml=replace(@xml,'Data Source=' + @oldservername,'Data Source=' + @newservername) -- replace the old server name with the new server name in the connection string
    select @packagedata=cast(@xml as varbinary(max)) -- convert the xml back to binary
    UPDATE sysssispackages SET packagedata = @packagedata WHERE (id= @planid) -- update the plan
    end
    fetch next from PlansToFix into @planid -- get the next plan
    end
    close PlansToFix
    deallocate PlansToFix

我使用了這個對 Server Fault 問題Renamed a SQL Server 的回答的程式碼，但是維護計劃仍然有舊名稱：

SELECT  x.*,
       LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
   SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
   FROM dbo.sysssispackages
   WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

並重命名：

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = 'package GUID'

引用自：https://dba.stackexchange.com/questions/55090