I found a problem in the latest version of the SQL Server Management Pack (version 09.0.1399.0700) that I imagine many others have wrestled with as well. Easy fix though, so I wanted to post it out here for everyone.
The problem is with the script SQL Server 2005 Long Running Agent Jobs. If this script finds a long running job, you will most likely get an alert with a message similar to the following:
The SQL Server management pack script "SQL Server 2005 Long Running Agent Jobs" is unable to successfully connect to the SQL Server instance "MSSQLSERVER". The error message returned is "Invalid column name 'originating_server'."
If you're interested in the background, this script is based on the same script for SQL 2000, but one critical modification was missed in the conversion. The script retrieves a list of running jobs from the sysjobs system table in msdb. In SQL 2000, the name of the server is in the originating_server field, but in SQL 2005 this was changed to originating_server_id. We need to join with sys.servers to retrieve the server name.
The script can be fixed with a change to a single line. According to my count, line 2265 should be the following:
strQuery = "select originating_server, name from sysjobs where job_id = " & strGUID
Replace this line with the following line that includes a new query. I shouldn't have to state the obvious, but make sure you backup the original script first.
strQuery = "SELECT sys.servers.name as originating_server,sysjobs.name FROM sys.servers JOIN sysjobs ON sysjobs.originating_server_id = sys.servers.server_id WHERE sysjobs.job_id = " & strGUID
That should be the only change you need to make to get this script working properly. I expect that we will see it fixed in the next revision of the management pack, but this quick fix should get you by until then.
