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.
Network share migration
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.
Subscribing will provide email updates when this Article is updated. Login is required.