Author Archives: admin

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

SQL Server Replication — Adding new articles to existing publishcation

  1. Add one or more articles through one of the following methods:

  2. After adding an article to a publication, you must create a new snapshot for the publication (and all partitions if it is a merge publication with parameterized filters). The Distribution Agent or Merge Agent then copies the schema and data for the new article to the Subscriber (it does not reinitialize the entire publication).
  3. After the snapshot is created, synchronize the subscription to copy the schema and data for the new article.

sql server – The Distributor has not been installed correctly

–unable to turn on publish for db and showing error for the distributor has not been installed correclty

 
exec sp_replicationdboption N’TEst’, N’publish’,’true’
select @@SERVERNAME

sp_addserver @server = ‘servername’ ,
@local = ‘local’
, @duplicate_ok = ‘duplicate_OK’

sp_helpserver

sp_adddistributor ‘servername’

sp_adddistributiondb N’distribution’;
sp_adddistpublisher @publisher = ‘servername’, @distribution_db = N’distribution’;

SSRS How to change rows data into columns

drag matrix into design pane select report data
, drag month_name into rows
, drag physical_year into columns
, drag member_cnt into data.
Preview. When happy build and deploy

 

matrix view in SSRS

orignial data rows view

11 2017-2018 a-Jul
11 2017-2018 b-Aug
12 2017-2018 c-Sept
13 2017-2018 d-Oct
14 2017-2018 e-Nov
15 2017-2018 f-Dec
16 2017-2018 g-Jan
17 2017-2018 h-Feb
18 2017-2018 i-Mar
19 2017-2018 j-Apr
20 2017-2018 k-May
21 2017-2018 l-Jun
11 2016-2017 a-Jul
11 2016-2017 b-Aug
12 2016-2017 c-Sept
13 2016-2017 d-Oct
14 2016-2017 e-Nov
15 2016-2017 f-Dec
16 2016-2017 g-Jan
17 2016-2017 h-Feb
18 2016-2017 i-Mar
19 2016-2017 j-Apr
20 2016-2017 k-May
21 2016-2017 l-Jun
11 2015-2016 a-Jul
11 2015-2016 b-Aug
12 2015-2016 c-Sept
13 2015-2016 d-Oct
14 2015-2016 e-Nov
15 2015-2016 f-Dec
16 2015-2016 g-Jan
17 2015-2016 h-Feb
18 2015-2016 i-Mar
19 2015-2016 j-Apr
20 2015-2016 k-May
21 2015-2016 l-Jun