How to query sys tables in SQL SERVER 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to query sys tables in SQL SERVER 2005

Hello ..
Could anyone help….Please regarding how to access system tables in sql server 2005.
Have given the code in sql server 2000 to query msdb..sysjobhistory table. At present code I am searching error string in msdb..sysjobhistory table . if searching string is there i am rescheduling the job. code have given below: declare @Maxinstance_ID int
select @Maxinstance_ID = max(instance_ID) from msdb..sysjobhistory
where job_id=’74DB2196-C9ED-4BFF-9ECB-16279D00EDF4′ and run_status=0 and step_id=1 if (select charindex(‘RaiseError’,message) from msdb..sysjobhistory
where instance_id = ( @Maxinstance_ID ))>0
Begin
Declare @valTime int
/* @valTime variable takes value in ‘HHMMSS’ format and assigning it to @active_start_time parameter with three hours ahead */
set @valTime =convert(int,substring(replace (convert(varchar,dateadd(mi,3,getdate()),114),’:’,”),1,len(replace (convert(varchar,dateadd(mi,3,getdate()),114),’:’,”))-3))
/* If jobschedule like "FindatamartdailyupdateReShc" is already there for three hours schedule time then delete it to create next scheduler with same name */
–IF EXISTS(select b.* from msdb..sysjobs a,msdb..sysjobschedules b where a.job_id= b.job_id and a.name=’Findatamartdailyupdate’ and b.name=’FindatamartdailyupdateReShc’)
EXEC msdb..sp_delete_jobschedule @job_name = N’Findatamartdailyupdate’, @name = N’FindatamartdailyupdateReShc’
EXEC msdb..sp_add_jobschedule @job_name = ‘Findatamartdailyupdate’, @name = ‘FindatamartdailyupdateReShc’,@freq_type = 1,@active_start_time = @valTime
———- My query is how to query the msdb..sysjobhistory table in Sql server 2005. or which procedure we can use in SQL SRV 2005 to so that I can use above logic in SQL Server 2005. It will be greate help for me … Thanks in advance…
Why do you want to query system tables that is discouraged and removed in SQL 2005?
What is the requriement from sysjobshistory in thsi case? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
Hello Satya, As have given the code below which is in sql server 2000. in msdb..sysjobhistory.message column I look for error string in my work that is "RAISEERROR". If I find that string will reschedule my job. So in SQL server 2005 what alternate way to acces sysjobhistory? Thanks
www.sommarskog.se/error-handling-I.html
www.sommarskog.se/error-handling-II.html
Madhivanan Failing to plan is Planning to fail
SYSJOBHSITORY table still exists in 2005 and you use sp_help_jobhistory procedure if you want to move away from using system tables…
Mohammed U.
True and thats what I was intended to say once you come out why to choose this solution, as the Books online refers querying the sys.tables are discourage and depracated in SQL 2005. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing.
]]>