Post reply

Powershell script executes from job only for some servers

  • March 31, 2011 at 3:09 pm

    #248065

    Currently have a Powershell script that is residing in a Windows 2008 server which has SQL Server 2008. This script accesses 12 Windows 2003 servers which have SQL Server 2005 and it retrives all the analysis objects that reside on each server.

    This script is embedded in an SSIS package. When I run the package from BIDS it gets all the information for all 12 servers. When I deploy this package and run it from a job it gets accurate information for 4 servers but does not retrieve anything for the other 8.

    To debug it when I ran the powershell script through sql job it executed for the 4 servers mentioned before but the output was blank for the other 8.

    Would somebody please guide me on what the issue might be or how else to debug it.

    Note: Seems to get server information but when it has to loop through the databases to retrive the names it is not retrieving anything.

    if ($ServerName -eq "XXXX")

    {

    "FOUND SERVER"|out-file -filepath $fileName -append

    }

    "Here 2"|out-file -filepath $fileName -append

    $server.ConnectionString|out-file -filepath $fileName -append

    $server.version|out-file -filepath $fileName -append

    $DBS = $server.Databases

    foreach ($DB in $DBS){

    "Here 3"+$DB.Name|out-file -filepath $fileName -append

    } #DB

  • April 4, 2011 at 6:47 am

    #1306949

    I suspect that your SQL Agent service account does not have enough permissions on the 8 servers where you get no data. Try actually logging on to the server as the Agent account and just run one of the PS scripts against one of the remote servers that you have having trouble with. You may get an error message.

  • April 5, 2011 at 9:03 am

    #1307505

    Craig,

    After reading your reply, checked on all servers to see what login the SQL Server Agent Service was using and the servers which were not getting back any data were on a different Login.

    You hit it on the nail. Thank you!!!!

    All this while I was thinking it was a powershell timing out issue.

  • April 5, 2011 at 9:06 am

    #1307509

    Thank You Craig...that worked.

Viewing 4 posts - 1 through 3 (of 3 total)

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