The following post is about Rank(),Dense_Rank() and Row_Number() T-SQL Commands.
declare @t table(FranchiseName varchar(200),AssociatedCity varchar(20),Sport varchar(200))
Insert into @t values
;with CTE AS
(select *,Row_number() Over(Order by AssociatedCity) as [RowNumber] from @t T)
select * from CTE
;with CTE2 AS
(select *,RANK() Over(Order by AssociatedCity) as [Rank] from @t T)
select * from CTE2
;with CTE3 AS
(select *,DENSE_RANK() Over(Order by AssociatedCity) as [DenseRank] from @t T)
select * from CTE3
I was installing a new version of a vendor application and one of the requirements was to change the existing sql agent job owners to a different user account.
There were several jobs and I needed to find a way to script them out and do it all at once and all these jobs had the same naming convention, so,it was possible for me to do wild character search. Below is how I did,
'EXEC msdb.dbo.sp_update_job @job_id=N'''+cast(Job_ID as varchar(100))+''',
@owner_login_name=N''NewJobOwner''' from msdb.dbo.sysjobs A
INNER JOIN master.dbo.syslogins B on A.owner_sid=B.sid
where A.enabled = 1 and A.name like '%--NAMEoftheJOBS--%' and
Copy the output of this into SSMS and Execute.
Alternatively, I could use loop or cursor and update one by one but I liked the above method.