Hi guys this urgent reg int to varchar conversion
-
I am a fresher dba i got a task where i need to convert int value to varchar please help me with this
in the highlighted value else 0 instead i want 'not enabled' and 'not scheduled'
SELECT
distinct @@SERVERNAME AS ServerName,
CASE
WHEN J.Name IS NOT NULL THEN 1 -- job exists
ELSE 0 -- Job does not exist
END AS IsExisting,
CASE J.enabled
WHEN 1 Then 1 -- job is enabled
ELSE 0 -- instead of 0 i want to return 'not enabled' job does not exist error condition
END AS IsEnabled,
CASE
WHEN ss.enabled IS NOT NULL THEN 1 -- schedule is enabled
ELSE 0--instead of 0 i want to return 'not scheduled'
END AS IsScheduleEnabled ,
J.NAME, CASE WHEN MAX(H.RUN_DATE) IS NULL THEN 0 ELSE MAX(h.run_date)
END AS LASTRUNDATE
,getdate() as Date
FROM MSDB..sysjobs J
LEFT JOIN MSDB..sysjobhistory H ON J.job_id = H.job_id
LEFT JOIN MSDB..sysjobschedules sjs ON sjs.job_id = J.job_id
LEFT JOIN MSDB..sysschedules ss ON ss.schedule_id = sjs.schedule_id
LEFT JOIN msdb..sysjobsteps steps on steps.job_id = j.job_id
AND steps.step_id = 1 -- we are just checking for job existance here so it won't matter if there are multiple steps
where j.name like 'sys%' and (j.enabled=0 or ss.enabled is null or j.name is null or h.run_date is null)
GROUP BY J.NAME,J.enabled, SS.enabled,h.run_date
-
did you try :
, CASE WHEN ss.enabled IS NOT NULL THEN 'Schedule is enabled'
ELSE 'not scheduled'
END AS IsScheduleEnabled
Case doesn't put any limitations to it's then or else part(s), however these parts should return the same data type to avoid implicit conversion errors.
added the BOL ref: CASE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms181765.aspx
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
this was not working its returning conversion error unABLE CONVERT ERROR
-
t
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply