Identify SQL Job 'Friendly Name'

Question

Identify SQL Job 'Friendly Name'

2Tall

SSChampion

Points: 14644
More actions
December 16, 2011 at 4:28 am

#148932

Hi. I am migrating servers and have transfered jobs using ssis. A number of reports have been scheduled by users ro run on server A (deliver via email).
I have recreated the reports folder on the new server (Server B).
I have updated some report folder paths (so where previously on Server A they referenced Folder\Dept\Company Name on Server B they now reference Folder\Dept\New Company Name)
Will transferred jobs for scheduled reports execute on the new server or will they need to be set-up again?
I would like to identify the reports friendly name i.e.
Job ID example:
0AF393F7-24CA-4729-9EB0-DA13AB31CB33 = 'Purchasing Outstanding'
Does anyone have a script to do this?
Kind Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Divine Flame SSCoach Points: 15943 More actions · Answer 1

2Tall (12/16/2011)
Hi. I am migrating servers and have transfered jobs using ssis. A number of reports have been scheduled by users ro run on server A (deliver via email).
I have recreated the reports folder on the new server (Server B).
I have updated some report folder paths (so where previously on Server A they referenced Folder\Dept\Company Name on Server B they now reference Folder\Dept\New Company Name)
Will transferred jobs for scheduled reports execute on the new server or will they need to be set-up again?

When you transfer the jobs to other server using SSIS, they are disabled by default. If you had changed that option to enable them (or you have enabled them manually) on new server then they will definitely run. Whether they will be successful or fail after execution that's another thing ;-).

If you have restored all the databases that are used to prepare the report, you have created the directories on the same paths & you have configured the users rights also as per the previous server, most likely jobs will run successfully.

2Tall (12/16/2011)
I would like to identify the reports friendly name i.e.
Job ID example:
0AF393F7-24CA-4729-9EB0-DA13AB31CB33 = 'Purchasing Outstanding'
Does anyone have a script to do this?

I didn't understand it clearly. If you want to know the name of the jobs by passing their JobId you can query MSDB.DBO.SysJobs.

SELECT [Name] FROM MSDB.DBO.SysJobs WHERE Job_Id = 'Your Job Id Here'

Sujeet Singh

2Tall SSChampion Points: 14644 More actions · Answer 2

Hi. I disbaled the jobs on the new server until i flick the switch.

The following code returns blanks for all jobs I tried

SELECT [Name] FROM MSDB.DBO.SysJobs WHERE Job_Id = '0AF393F7-24CA-4729-9EB0-DA13AB31CB33'

Result:

Name

Blank

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Divine Flame SSCoach Points: 15943 More actions · Answer 3

2Tall (12/16/2011)
The following code returns blanks for all jobs I tried
SELECT [Name] FROM MSDB.DBO.SysJobs WHERE Job_Id = '0AF393F7-24CA-4729-9EB0-DA13AB31CB33'
Result:
Name
Blank

May be you are providing wrong Job_Id. Check if this statement returns any result:

SELECT [Job_Id],[Name] FROM MSDB.DBO.SysJobs

From its result, you can verify the job names & their corresponding valid Job Ids.

Sujeet Singh

2Tall SSChampion Points: 14644 More actions · Answer 4

Hi, you were quite right however name '0AF393F7-24CA-4729-9EB0-DA13AB31CB33' means as little as Job_Id 🙂

I hoped to return the report Name that created the Job_Id.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 5

You can get all that information by querying the ReportServer database.

This is a very basic example.

On your old server run the following code

SELECT j.name AS JobName

, c.name as ReportName

FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs j

ON CONVErT(nvarchar(100),a.ScheduleID) = j.name

JOIN ReportServer.dbo.ReportSchedule rs

ON j.name = CONVErT(nvarchar(100),rs.ScheduleID)

JOIN ReportServer.dbo.Catalog c

ON rs.reportid = c.itemid