Post-SP 3 security update and hotfixes

Question

Post-SP 3 security update and hotfixes

Darek Tomyn

SSCrazy

Points: 2551
More actions
May 14, 2004 at 7:59 am

#85664

For those people that have applied post-SP 3 hotfixes... i.e. any of the following:
8.00.859
http://support.microsoft.com/?id=821334
8.00.819
http://support.microsoft.com/?id=826161
8.00.818
http://support.microsoft.com/?id=821277 / http://www.microsoft.com/technet/security/bulletin/MS03-031.mspx
I have a question to ask... do you encounter issues with SQL Server Agent jobs that have more than 1 job step? I have attempted to apply each of these releases all with the same results. If I have a job with multiple job steps the job steps sometime appear in random order when I right-click the job and choose execute. That is, the popup window displays the job steps in random order. Luckily, when the job actually runs the job steps are ran in sequential order, but obviously this is leaving me feeling a little bit uneasy about these hotfixes and how well they were actually tested.
Thanks

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Darek Tomyn SSCrazy Points: 2551 More actions · Answer 1

I "kind of" tracked this issue down. All versions of SQL Server 2000 call msdb's "sp_help_jobstep" to return the results for the popup screen (and for SQL-DMO). When I run this stored procedure on a SQL Server 2000 SP 3 (and less) machine the results come back sorted by step_id. When I run it on SP 3+ it comes back sorted by step_name. You would then think that the stored procedure has been modified... however, this is not the case. As a matter of fact, Microsoft is going against their own recommendations and not including an order by in the stored procedure (and SQL-DMO is also not ordering the results). I then thought that maybe an index was changed on msdb (ex. clustered index changed to include step_name instead of step_id... as a result the default ordering would be "fairly close" to the clustered index)... again, this is not the case. So, the easy/bad answer to this is to add an "ORDER BY step_id" to the sp_help_jobstep stored procedure; however, for obvious reasons I am not too keen on updating a Microsoft supplied stored procedure. Another answer would be to modify all of my jobs that contain multiple steps and put a letter or number prefix on them. This would not be the end of the world, but it seems a little strange that I am experiencing this issue now.

Anyway, a test job to run to test this out on SP3 and SP3+ machines is the following:

BEGIN TRANSACTION

DECLARE @JobID BINARY(16)

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'[Uncategorized (Local)]') < 1

EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'

-- Delete the job with the same name (if it exists)

SELECT @JobID = job_id

FROM msdb.dbo.sysjobs

WHERE (name = N'Job with multiple steps')

IF (@JobID IS NOT NULL)

BEGIN

-- Check if the job is a multi-server job

IF (EXISTS (SELECT *

FROM msdb.dbo.sysjobservers

WHERE (job_id = @JobID) AND (server_id <> 0)))

BEGIN

-- There is, so abort the script

RAISERROR (N'Unable to import job ''Job with multiple steps'' since there is already a multi-server job with this name.', 16, 1)

GOTO QuitWithRollback

END

ELSE

-- Delete the [local] job

EXECUTE msdb.dbo.sp_delete_job @job_name = N'Job with multiple steps'

SELECT @JobID = NULL

END

BEGIN

-- Add the job

EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Job with multiple steps', @owner_login_name = N'sa', @description = N'No description available.', @category_name = N'[Uncategorized (Local)]', @enabled = 1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0