How to modify all existing Distribute Software tasks to use UNC

book

Article ID: 181757

calendar_today

Updated On:

Products

Deployment Solution

Issue/Introduction

 

Resolution

Question
I am experiencing the issue described within KB article ID 44545 "Error 1226 received when executing a Distribute Software task" and I have a large number of these tasks already within my Deployment Server.  Most of these jobs use a relative path to files within the eXpress share and some use a UNC path "\\%DSSERVER%\eXpress".

How do I modify all these existing jobs to reference a UNC path and copy via UNC?

Answer

The script contained within this KB is potentially harzardous and requires a working knowledge of all jobs contained within your Deployment Server.

Backup your Database

Refer KB article ID 27573 "How to back up and restore the Deployment Server database".

Verify that existing relative source paths will not exceed 256 characters

The maximum field length for a source file location is 256 characters. This script will modify any relative paths to include either a default "\\%DSSERVER%\eXpress" or a server name and share name of your specification.

The attached SQL script verify_install_path_length.sql will enumerate all Copy File To tasks and return those (including the folder in which they reside) with a total source path greater than 256 characters.

/* Get all Distribute Software tasks with a total filename longer than 256 chars */
DECLARE @svrname varchar(32) ,@shrname varchar(32)

/* USER MODIFICATION BEGIN */
/* if you wish to use an alternative UNC Server name or a different share name
This is where you specify them
format is simply SERVERNAME and SHARENAME
Recommended values are %DSSERVER% and eXpress respectively */

 SET @svrname = '%DSSERVER%'
 SET @shrname = 'eXpress'

/* END USER MODIFICATION */

select  e.event_id, e.[name] 'Job Name', e.[description] 'Job Description', isnull(ef.[name],'Root Folder') 'within folder', ift.install_file 'existing Source Path (too long to update)'
from
install_task ift
left join task t
 on ift.event_id = t.event_id
 and ift.cond_seq = t.cond_seq
 and ift.task_seq = t.task_seq
left join event_condition ec
 on t.event_id = ec.event_id
 and t.cond_seq = ec.cond_seq
left join [event] e
 on e.event_id = ec.event_id
left outer join event_folder ef
 on e.folder_id = ef.folder_id
where 1=1
 and ift.aclient_to_download = 0
 and substring (ift.install_file, 1, 2) = '.\'
 and len(ift.install_file) > (256 -len('\\' + isnull(@svrname,'%DSSERVER%') + '\' + isnull(@shrname,'eXpress') )-1)
 order by len(ift.install_file), e.[name] desc
/* End Query */

If you wish to use a different UNC path other than "\\%DSSERVER%\eXpress" then you will need to modify the server name and share name within the User Modification Section.

** Each job that is returned will need to be manually remediated prior to proceeding **

Import the attached job

Modify the Copy File To task > Advanced > "Copy directly from file source" option to specify a valid username and password.
Save the Job.

Run the attached SQL Script in Microsoft* SQL Server Management Studio

The script Update_install_tasks_to_use_UNC.sql will:

  • Find the username and password from the imported job;
  • Change all relative source paths to a UNC path with the specified server and share name;
  • Output a report for all tasks that were modified;
  • Output a report for all tasks that contained a relative source path that was too long to update.

There is a section within the script where you can modify the server name and the share name. Do not include any backslashes "\" within these fields.

/*  Update Distribute Software tasks  */
-- Declare variables for use within the query
DECLARE @event int, @cond_seq int, @task_seq int
,@locfile varchar(256), @jobname varchar(128), @repl_usrname varchar(128), @repl_usrpass varchar(128)
,@svrname varchar(32) ,@shrname varchar(32)

/* USER MODIFICATION BEGIN */
/* if you wish to use an alternative UNC Server name or a different share name
This is where you specify them
format is simply SERVERNAME and SHARENAME
Recommended values are %DSSERVER% and eXpress respectively */


 SET @svrname = '%DSSERVER%'
 SET @shrname = 'eXpress'


/* END USER MODIFICATION */

-- Get the username hash from the sample job
set @jobname = 'TEMPORARY CFT'
SET @repl_usrname =
 (
 select cft.[username]
 from
 copyfile_task cft
 left join task t
  on cft.event_id = t.event_id
  and cft.cond_seq = t.cond_seq
  and cft.task_seq = t.task_seq
 left join event_condition ec
  on t.event_id = ec.event_id
  and t.cond_seq = ec.cond_seq
 left join [event] e
  on e.event_id = ec.event_id
 left outer join event_folder ef
  on e.folder_id = ef.folder_id
 Where 1=1
  and e.[name] = @jobname
 )

-- Get the password hash from the sample job
SET @repl_usrpass =
 (
 select cft.[password]
 from copyfile_task cft
 left join task t
  on cft.event_id = t.event_id
  and cft.cond_seq = t.cond_seq
  and cft.task_seq = t.task_seq
 left join event_condition ec
  on t.event_id = ec.event_id
  and t.cond_seq = ec.cond_seq
 left join [event] e
  on e.event_id = ec.event_id
 left outer join event_folder ef
  on e.folder_id = ef.folder_id
 Where 1=1
  and e.[name] = @jobname
 )

-- Only run the change process if a uesrname is returned
if @repl_usrname IS NOT NULL or @repl_usrname <> ''
BEGIN
 -- Create Table of Results for changed items
 if object_id(N'tempdb..#changeditems',N'U') IS NOT NULL
 Begin
  drop table #changeditems
 End
 CREATE TABLE #changeditems (event_id  INT, cond_seq INT, task_seq INT, [Job_Name] varchar(128) ,[description] varchar(254) ,Folder_Name varchar(64) ,install_file varchar(256))

 DECLARE c1 CURSOR FOR

/* Create the data set of Copy File To tasks that will be update
 Modify all relative source paths to be UNC paths
 Exclude any Copy File To tasks that will exceed the maximum source path length
 when converted to a UNC path */

 select ift1.event_id ,ift1.cond_seq ,ift1.task_seq , '\\' + isnull(@svrname,'%DSSERVER%') + '\' + isnull(@shrname,'eXpress') + substring(ift1.install_file,2, len(ift1.install_file))
 from
  install_task ift1
 where 1=1
  and substring(ift1.install_file, 1, 2) ='.\'
  and ift1.aclient_to_download = 0
  and len(ift1.install_file) < (256 -len('\\' + isnull(@svrname,'%DSSERVER%') + '\' + isnull(@shrname,'eXpress')))
 UNION ALL
 select ift2.event_id ,ift2.cond_seq ,ift2.task_seq ,ift2.install_file
 from
  install_task ift2
 where 1 = 1
  and ift2.aclient_to_download = 0
  and substring (ift2.install_file, 1, 2) = '\\'
 order by event_id

 OPEN c1
  FETCH NEXT FROM c1 INTO @event ,@cond_seq ,@task_seq ,@locfile

  WHILE @@FETCH_STATUS = 0
   BEGIN

    /* Update each Copy File To task to use a UNC source path and set it to
    copy via UNC rather than the DS Engine. */

    update install_task
    set aclient_to_download = 1 ,[username] = @repl_usrname ,[password] = @repl_usrpass ,install_file = @locfile
    -- comment out the line below to maintain the existing execution credentials for the software distribution task
    ,[exec_username] = @repl_usrname ,[exec_password] = @repl_usrpass
    where 1=1
     and event_id = @event
     and cond_seq = @cond_seq
     and task_seq = @task_seq

    -- Populate the temporary Results table to show all items changed
    Insert into #changeditems
    select  ift.event_id ,ift.cond_seq ,ift.task_seq ,e.[name] ,e.[description] ,isnull(ef.[name],'Root Folder') , @locfile
    from
    install_task ift
    left join task t
     on ift.event_id = t.event_id
     and ift.cond_seq = t.cond_seq
     and ift.task_seq = t.task_seq
    left join event_condition ec
     on t.event_id = ec.event_id
     and t.cond_seq = ec.cond_seq
    left join [event] e
     on e.event_id = ec.event_id
    left outer join event_folder ef
     on e.folder_id = ef.folder_id
    where 1=1
     and ift.event_id = @event
     and ift.cond_seq = @cond_seq
     and ift.task_seq = @task_seq

    FETCH NEXT FROM c1 INTO @event, @cond_seq , @task_seq , @locfile
   END

 CLOSE c1
 DEALLOCATE c1
 
 /* Display modified Copy File To tasks and associated job */
 select event_id, cond_seq ,task_seq ,Job_Name as 'Job Name' ,Description 'Job Description' ,Folder_Name as 'within folder' ,install_file as 'Update Source Location' from #changeditems

 /* Display all Copy File To tasks that were NOT modified and probably should have been */
 select  e.event_id, e.[name] 'Job Name', e.[description] 'Job Description', isnull(ef.[name],'Root Folder') 'within folder' , ift.install_file 'existing Source Path (too long to update)'
 from
 install_task ift
 left join task t
  on ift.event_id = t.event_id
  and ift.cond_seq = t.cond_seq
  and ift.task_seq = t.task_seq
 left join event_condition ec
  on t.event_id = ec.event_id
  and t.cond_seq = ec.cond_seq
 left join [event] e
  on e.event_id = ec.event_id
 left outer join event_folder ef
  on e.folder_id = ef.folder_id
 where 1=1
  and ift.aclient_to_download = 0
  and substring (ift.install_file, 1, 2) = '.\'
  and len(ift.install_file) > (256 -len('\\' + isnull(@svrname,'%DSSERVER%') + '\' + isnull(@shrname,'eXpress') )-1)
  order by len(ift.install_file), e.[name] desc
 -- Clean up temporary tables
 drop table #changeditems
END
ELSE
BEGIN
 print 'Username/Password has not been set.  Please update job TEMPORARY CFT to use "Copy directly from file source"'
END

/* End Update Distribute Software Tasks */

Attachments

verify_install_path_length.zip get_app
Update_install_tasks_to_use_UNC.zip get_app
TemporaryCFT.zip get_app
NAB_Update_install_tasks_to_use_UNC.zip get_app