Author Archives: admin

Test function

ALTER function [dbo].[client_ashrae_get_committee_voting](@cst_key av_key)

RETURNS nvarchar(150)
as
begin
Declare @val nvarchar(30)
select top 1 @val = CASE when cmc_voting_flag= 1 THEN ‘Voting’
ELSE ‘Non-voting’
END

FROM mb_committee_x_customer cxc WITH ( NOLOCK )
INNER JOIN mb_committee Committee WITH ( NOLOCK )
ON Committee.cmt_key = cxc.cmc_cmt_key AND Committee.cmt_delete_flag = 0 AND cxc.cmc_delete_flag=0
INNER JOIN co_customer Customer WITH ( NOLOCK )
ON Customer.cst_key = cxc.cmc_cst_key
AND Customer.cst_delete_flag = 0
where cmc_cst_key = @cst_key

return @val
END

Change job owner for Agent jobs

create procedure dbo.sp_change_job_owner
@jobName varchar(500),
@ownername varchar(60)
AS

DECLARE @owner_sid varbinary(85)

Select @owner_sid= sid
FROM master.sys.syslogins(NOLOCK)
WHERE name like @ownername

UPDATE msdb.dbo.sysjobs set owner_sid = @owner_sid
where name like @jobName

UPDATE msdb.dbo.sysschedules SET msdb.dbo.sysschedules.owner_sid= @owner_sid
FROM msdb.dbo.sysjobs job
inner join msdb.dbo.sysjobschedules jobsch on job.job_id = jobsch.job_id
INNER JOIN msdb.dbo.sysschedules sch on sch.schedule_id = jobsch.schedule_id
WHERE job.name like @jobName