Today I'm taking a break from Shylock woes and instead looking at an issue one of the team raised regarding inconsistent jobs on our deployment server. Specifically, we've got two versions of a script task on our deployment servers where only one should exist. This is a nice task it's one of those jobs which looks horrible to correct, but it's actually easy to correct with just a bit of SQL knowledge.
The embedded script task in question begins with the string 'REM Resolve Shortcuts' so I quickly put together a little SQL to see where the problem lay,
select count(*),script from script_task where script like 'REM Resolve Shortcuts%' group by script
This indeed confirms the problem. At some point, I've updated a script bit not updated the version number and not performed this update across ALL the jobs on the Deployment Server. This will only become and increasing problem with time, so needs to be fixed now.
First, I need to find one of the scripts which has been correctly updated and then update the version number. Re-running the above SQL now gives,
So now we have one known 'good' script task. Our mission now is update all the script tasks which are sitting at version 1.2 to this new correct one which is at version 1.3.
So, after running a eXpress backup, this can be accomplished with the following SQL,
--Let us see how bad the problem is again... select count(*),script from script_task where script like 'REM Resolve Shortcuts%' group by script DECLARE @NewScript varchar(6000) SET @NewScript=(select script from script_task where script like 'REM Resolve Shortcuts using SQLCMD.exe (v1.3)%') SELECT @NewScript Update script_task set script=@NewScript where script like 'REM Resolve Shortcuts using SQLCMD.exe (v1.2)%' --Check that all scripts have been updated correctly select count(*),script from script_task where script like 'REM Resolve Shortcuts%' group by script
So brilliant. All resolved which means, I guess, it's back to Shylock.... :-(