Enterprise SQL Job Activity Reporting

Question

Enterprise SQL Job Activity Reporting

Craig Benson

SSC Veteran

Points: 251
More actions
December 5, 2009 at 12:43 pm

#218317

Comments posted to this topic are about the item Enterprise SQL Job Activity Reporting

Viewing 15 posts - 1 through 14 (of 14 total)

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

dave-dj SSChampion Points: 11297 More actions · Answer 1

Hi,

This is a great idea and I've just done something similar, but more focused on producing server documentation of jobs, backup devices etc etc.

What I'd like to suggest is a slightly different alternative to acheive the same result.

I would create an SSRS report with a data source for each of your severs.

Create a report a separate report for each server to view the job history of jobs on the server.

Then using SSRS create a report which includes a sub report, point to each of the server reports.

This would remove the need for SSIS and storing data in SQL (unless of course you need a bigger than you keep for your job history..

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

Craig Benson SSC Veteran Points: 251 More actions · Answer 2

I see great minds think alike! I actually set out using a pure SSRS solution, but the report generation took too long because I have so many servers, so I opted to off-load the data gathering portion using a SSIS solution and a single table that gets truncated before each run.

Nevertheless, for those out there that don't have too many servers, using a pure SSRS solution is definitely the way to go!

Duude Valued Member Points: 50 More actions · Answer 3

Hi!

Would you extend the article with the "howto" of the "For Each Loop Container".

Thanks!!

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

I think it would be less maintenance to host the SSIS package on a single server, have it poll all the other servers, and then base the report on the compiled data.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Craig Benson SSC Veteran Points: 251 More actions · Answer 5

I didn’t actually use the For Each Loop container, as I don’t connect to all my SQL Servers using Windows credentials.

But, if you do, here’s how you would use one:

1. In your SSIS package, you would need a VB script task that would itself have a For Each loop routine to iterate through all the SQL Servers on your network. All this routine would do is build a string list of all the SQL Server names.

2. Save the string of server names to a SSIS variable.

3. Create a For Each Loop container in the SSIS package and inside the loop iterate through the SSIS server name variable and update another SSIS variable that contains the connection string that is used by your OLE DB Source connection manager.

4. Then just follow the article steps to run the SQL Script to save the output to the designated OLE destination database.

Hope this helps!

Andy Leonard SSChampion Points: 10134 More actions · Answer 6

Andy Warren (12/7/2009)
I think it would be less maintenance to host the SSIS package on a single server, have it poll all the other servers, and then base the report on the compiled data.

Good point Andy,

I've built solutions like this as well, and I find architecture drives the distribution of the workload. My current environment, for example, has firewalls between Application Lifecycle tiers (Dev, Systems Integration, Production, etc.). In that case, I've opted to deploy a version of the tool to each tier individually - but if I wanted, I could pump CSV files around the enterprise and aggregate the information.

Great article Craig!

:{> Andy

Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics

Duude Valued Member Points: 50 More actions · Answer 7

Thank you Craig.

The only problem that I see in your good script, is to enable 'Ad Hoc Distributed Queries'.

Craig Benson SSC Veteran Points: 251 More actions · Answer 8

Yes, there is a small security concern with having it continuously enabled. Optionally, you could enable it before the script and disable it after. Or, you could create a linked server before the script and drop it after. The cat, as they say, can be skinned a few different ways! 8^)

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

this is pretty good

i just finished debugging a backup report system that uses SSRS to display the data and monitoring jobs is next on the list

http://www.sqlservercentral.com/articles/SQL+Server+2005/61887/

i found this article that i think i was going to base my system on, but i was going to customize it a lot. going to look at yours in more detail and probably take parts of each one.

One shortcoming I see is that in the report there is no server column and there is no filtering for failed jobs that i would like to see first instead of looking through a lot of data.

iBar SSCertifiable Points: 5872 More actions · Answer 10

Hello,

Getting following error if i create an OLE DB soruce to remote DB

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 2, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [2].

If i remove the OPENROWSET it works fine.

Is there any alternate to OPENROWSET, which will eventually remove the problem of "Ad-Hoc Remote Queries"

OR

How to fix the above error ?

Cheers

dave-dj SSChampion Points: 11297 More actions · Answer 11

Check that you have

a) remote connections enable for named pipes in is enabled in SQL Server Surface Area Manager > Surface Area Configuration for Services and Connections

b) OPENROWSET is enabled in SQL Server Surface Area Manager > Surface Area Configuration & Features

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

smthembu Hall of Fame Points: 3463 More actions · Answer 12

Great article Craig, just to acknowledge your work i also modified the reports to include a graph to get a quick glance before scanning the report to check for failed jobs.

Tx once more:-)

It's better to fail while trying, rather than fail without trying!!!

D-SQL SSCrazy Points: 2265 More actions · Answer 13

This post is pretty old but I have a question i have the report all set up but Im having issues getting one of my reports to show up one server. I added one more server to the report but the in tables if I select still cant get the job activity. This is a instance sql server thats the only difference from the other 9 servers.

I have 9 working and set up exaclty the same.

Any help would be appreciated

D-SQL SSCrazy Points: 2265 More actions · Answer 14

This is a great. I have a question now that I have this all set up I have added a SQL instance can you point me in the right direction in the SQL to use the instance ion the server?

Thanks