Need to change the location for a copy file task to execute from a new share location

Products

• IT Management Suite
• Deployment Solution
• Task Server

Issue/Introduction

A network share is specified for a Copy File task; how can we update all Copy File tasks via SQL to use a new network share.

Environment

SMP 7.x \ 8.x

Cause

Network share migration

Resolution

The following SQL query will show a list of all CopyFile tasks XML:

select top 1 CAST(ivd.State as XML)
from ItemVersionData ivd
cross apply
                (
                                select top 1 iv.VersionGuid 
                                from ItemVersions iv
                                where iv.VersionGuid = ivd.VersionGuid
                                order by iv.Version desc
                ) ivi
join ItemVersions iv
                on ivd.VersionGuid = iv.VersionGuid
join ItemClass ic
                on iv.ItemGuid = ic.Guid
                and ic.ClassGuid = '288ee2f1-ce82-42d7-8169-0b0d3a36962f'

Using that query you can see a list of each and every task, and what is in the task XML. Using that information you can look for specific patterns that you want to search for, and then replace. The next query will actually update the database to modify those tasks:

update ivdo
set State = REPLACE(cast(State as nvarchar(max)), 'OLD_UNC_LOCATION', 'NEW_UNC_LOCATION')
from ItemVersions ivo
join ItemVersionData ivdo
                on ivo.VersionGuid = ivdo.VersionGuid
join 
                (
                                select max(ivi.Version) MaxVersion, ivi.ItemGuid
                                from ItemVersions ivi
                                join ItemClass ici
                                                on ivi.ItemGuid = ici.Guid
                                                and ici.ClassGuid = '288ee2f1-ce82-42d7-8169-0b0d3a36962f'                                                      
                                group by ivi.ItemGuid                    
                ) b
                on b.ItemGuid = ivo.ItemGuid
                and b.MaxVersion = ivo.Version

Ensure there is a current database backup before running this other SQL update command in case they update something badly, or something else goes wrong.

The task data is cached in the IIS task (this helps speed up displaying task information in the console). The cache isn’t invalidated by changing the database, and running NS scheduled tasks doesn’t automatically update the cache. The easiest way to refresh/reset the IIS cache is to just run an ‘iisreset’ command on the Notification Server. If the tasks are scheduled and run against a client machine, they should use the correct new data that is in the database, but the console will display incorrect data until the cache is flagged to be cleared and refreshed.

Conversely, the queries above can be updated/modified to work with other task types other than the "Copy File" task. The first query is the classGuid for the "Copy File" task type, but that GUID can be changed to that of another task type to update those other task types, if desired.

If you right click on any task in the Console and right-click select "View XML" the first line will have the line "item guid=", followed immediately by the "classGuid=" . You can find out any task type by taking that 2nd GUID (classGUID) found in the XML on the top line, and then substituting that in the first SQL query to find and update that type of task. This way it can be used for more than one type of task.