How to get output of SP_HELP_JOB into a script?

  • Michael L John

    One Orange Chip

    Points: 25717

    This is SQL 7/2000 thread.  Is that the version you actually have in your environment?

    This script appears that it will work. 
    I do not know how many servers you have in your environment, but it looks like you are using linked servers to run this.  I would not be able to test this because of that fact. 

    I have 120 servers across all my environments. Creating a linked server is not practical here, and, there are other things to consider such as the security.
    I'm guessing that a single account being used is a sysadmin on every server.  That would violate the various rules we have in place, and if that account is compromised, then all of your servers are exposed. 

    It's not clear what you need to do.  If you simply want to monitor servers and find out if a job is running, there are free tools that do this. although they probably do not run on SQL 7/2000
    If you are actually using a higher version of SQL this may be a good choice
    https://www.idera.com/productssolutions/freetools
    If you want to monitor jobs and perform some action based upon some logic, then you may be able to leverage DBATools PowerShell scripts
    https://docs.dbatools.io/#Get-DbaRunningJob

    Again, I suggested creating a new thread in the appropriate forum.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • prakashanand2223

    SSC Rookie

    Points: 48

    Michael L John - Monday, September 24, 2018 7:35 AM

    This is SQL 7/2000 thread.  Is that the version you actually have in your environment?

    This script appears that it will work. 
    I do not know how many servers you have in your environment, but it looks like you are using linked servers to run this.  I would not be able to test this because of that fact. 

    I have 120 servers across all my environments. Creating a linked server is not practical here, and, there are other things to consider such as the security.
    I'm guessing that a single account being used is a sysadmin on every server.  That would violate the various rules we have in place, and if that account is compromised, then all of your servers are exposed. 

    It's not clear what you need to do.  If you simply want to monitor servers and find out if a job is running, there are free tools that do this. although they probably do not run on SQL 7/2000
    If you are actually using a higher version of SQL this may be a good choice
    https://www.idera.com/productssolutions/freetools
    If you want to monitor jobs and perform some action based upon some logic, then you may be able to leverage DBATools PowerShell scripts
    https://docs.dbatools.io/#Get-DbaRunningJob

    Again, I suggested creating a new thread in the appropriate forum.

    If you different servers which are not connected then you can deploy this script on those individual servers and make a sql job.
    Schedule that job so that it will run and mail you the result of the same.

    Thank You,
    Prakash Anand

  • Michael L John

    One Orange Chip

    Points: 25717

    prakashanand2223 - Monday, September 24, 2018 10:23 AM

    Michael L John - Monday, September 24, 2018 7:35 AM

    This is SQL 7/2000 thread.  Is that the version you actually have in your environment?

    This script appears that it will work. 
    I do not know how many servers you have in your environment, but it looks like you are using linked servers to run this.  I would not be able to test this because of that fact. 

    I have 120 servers across all my environments. Creating a linked server is not practical here, and, there are other things to consider such as the security.
    I'm guessing that a single account being used is a sysadmin on every server.  That would violate the various rules we have in place, and if that account is compromised, then all of your servers are exposed. 

    It's not clear what you need to do.  If you simply want to monitor servers and find out if a job is running, there are free tools that do this. although they probably do not run on SQL 7/2000
    If you are actually using a higher version of SQL this may be a good choice
    https://www.idera.com/productssolutions/freetools
    If you want to monitor jobs and perform some action based upon some logic, then you may be able to leverage DBATools PowerShell scripts
    https://docs.dbatools.io/#Get-DbaRunningJob

    Again, I suggested creating a new thread in the appropriate forum.

    If you different servers which are not connected then you can deploy this script on those individual servers and make a sql job.
    Schedule that job so that it will run and mail you the result of the same.

    Thank You,
    Prakash Anand

    Are you telling me this because that is what you do, or as a suggestion for me to do on my systems?
    If it's the former, then why do you need all of this extra code if it is running on each server? If it's the latter, then I'm not sure that I see the need to do something like this.  There is nothing configured on any of the 120 servers we have that needs to know if a job is running.
    When an issue arises, we simply look at the server or run a script from the central management server. 

    Again, what are you trying to accomplish?  What is the issue you are trying to solve?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • prakashanand2223

    SSC Rookie

    Points: 48

    Michael L John - Monday, September 24, 2018 10:36 AM

    prakashanand2223 - Monday, September 24, 2018 10:23 AM

    Michael L John - Monday, September 24, 2018 7:35 AM

    This is SQL 7/2000 thread.  Is that the version you actually have in your environment?

    This script appears that it will work. 
    I do not know how many servers you have in your environment, but it looks like you are using linked servers to run this.  I would not be able to test this because of that fact. 

    I have 120 servers across all my environments. Creating a linked server is not practical here, and, there are other things to consider such as the security.
    I'm guessing that a single account being used is a sysadmin on every server.  That would violate the various rules we have in place, and if that account is compromised, then all of your servers are exposed. 

    It's not clear what you need to do.  If you simply want to monitor servers and find out if a job is running, there are free tools that do this. although they probably do not run on SQL 7/2000
    If you are actually using a higher version of SQL this may be a good choice
    https://www.idera.com/productssolutions/freetools
    If you want to monitor jobs and perform some action based upon some logic, then you may be able to leverage DBATools PowerShell scripts
    https://docs.dbatools.io/#Get-DbaRunningJob

    Again, I suggested creating a new thread in the appropriate forum.

    If you different servers which are not connected then you can deploy this script on those individual servers and make a sql job.
    Schedule that job so that it will run and mail you the result of the same.

    Thank You,
    Prakash Anand

    Are you telling me this because that is what you do, or as a suggestion for me to do on my systems?
    If it's the former, then why do you need all of this extra code if it is running on each server? If it's the latter, then I'm not sure that I see the need to do something like this.  There is nothing configured on any of the 120 servers we have that needs to know if a job is running.
    When an issue arises, we simply look at the server or run a script from the central management server. 

    Again, what are you trying to accomplish?  What is the issue you are trying to solve?

    I have put all this code since I have one server which is connected with each and every servers. I have deployed this script on parent server and it's going to fetch data from each and every servers connected.
    In your case since you servers are not connected then you can deploy this script by doing modifications as per your requirement .

    Thank You,
    Prakash Anand

Viewing 4 posts - 16 through 19 (of 19 total)

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