SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Craig Benson / Enterprise SQL Job Activity Reporting / Latest Posts

RE: Enterprise SQL Job Activity Reporting

D-SQL — Wed, 01 Feb 2012 10:05:15 GMT

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

RE: Enterprise SQL Job Activity Reporting

D-SQL — Mon, 09 Jan 2012 17:33:01 GMT

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

RE: Enterprise SQL Job Activity Reporting

smthembu — Wed, 25 May 2011 23:33:35 GMT

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:-)

RE: Enterprise SQL Job Activity Reporting

dave-dj — Mon, 01 Feb 2010 09:42:30 GMT

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 Connectionsb) OPENROWSET is enabled in SQL Server Surface Area Manager > Surface Area Configuration & Features

RE: Enterprise SQL Job Activity Reporting

iBar — Mon, 01 Feb 2010 08:19:39 GMT

Hello,Getting following error if i create an OLE DB soruce to remote DBOLE 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 0Named 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"ORHow to fix the above error ?Cheers

RE: Enterprise SQL Job Activity Reporting

alen teplitsky — Wed, 09 Dec 2009 06:55:51 GMT

this is pretty goodi just finished debugging a backup report system that uses SSRS to display the data and monitoring jobs is next on the listhttp://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.

RE: Enterprise SQL Job Activity Reporting

Craig Benson — Mon, 07 Dec 2009 06:57:03 GMT

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^)

RE: Enterprise SQL Job Activity Reporting

Duude — Mon, 07 Dec 2009 06:30:09 GMT

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

RE: Enterprise SQL Job Activity Reporting

Andy Leonard — Mon, 07 Dec 2009 06:24:41 GMT

[quote][b]Andy Warren (12/7/2009)[/b][hr]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.[/quote]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

RE: Enterprise SQL Job Activity Reporting

Craig Benson — Mon, 07 Dec 2009 06:24:02 GMT

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!

RE: Enterprise SQL Job Activity Reporting

Andy Warren — Mon, 07 Dec 2009 06:07:18 GMT

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.

RE: Enterprise SQL Job Activity Reporting

Duude — Mon, 07 Dec 2009 05:34:21 GMT

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

RE: Enterprise SQL Job Activity Reporting

Craig Benson — Mon, 07 Dec 2009 05:21:10 GMT

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 [i]definitely[/i] the way to go!

RE: Enterprise SQL Job Activity Reporting

dave-dj — Mon, 07 Dec 2009 05:09:14 GMT

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..

Enterprise SQL Job Activity Reporting

Craig Benson — Sat, 05 Dec 2009 12:43:57 GMT

Comments posted to this topic are about the item [B]Enterprise SQL Job Activity Reporting[/B]