Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Connecting to remote server using powershell subtype Expand / Collapse
Author
Message
Posted Monday, August 18, 2014 1:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:08 PM
Points: 25, Visits: 166
Hi All,

Super newbie to powershell and I am on Day 1 of learning..i am trying to configure a SQL Server Agent job with a Powershell step type that will execute this piece of ps code:

Enter-PSSession -ComputerName XXX
Invoke-Command -ComputerName XXX -ScriptBlock {DIR C:\TEMP}

If I run it in PowerGUI, it works and I get the listing.
If I open a cmd prompt on the server, run powershell and execute the .ps1 file it works.

The error I am getting is:
Executed as user: DOMAIN\USER. A job step received an error at line 1 in a PowerShell script. The corresponding line is 'Enter-PSSession -ComputerName XXX. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Enter-PSSession' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.

What is wrong with my simple 2-line code within the job step? Thanks in advance.

My eventual goal is to replace the scriptblock command 'DIR C:\TEMP' with the path to a .bat file on the server..
Post #1604654
Posted Monday, August 18, 2014 2:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:45 AM
Points: 5,813, Visits: 3,734
Perhaps the credentials under which SQL Agent runs does not have the appropriate permissions to create a remote PowerShell session.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
Post #1604687
Posted Tuesday, August 19, 2014 4:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:15 AM
Points: 2,111, Visits: 3,706
Are you using SQL Server 2008 or 2008 R2?
Powershell steps on 2008/R2 only run at Powershell 1.0 or 2.0 (and only a subset at that!), Enter-PSSession may not be available to it.

If you've got Powershell 3.0+ installed on the server, a workaround is to create a Command Line step in SQL Agent & execute the ps1 script via Powershell.exe, as you mention you've already done successfully.

On the other hand, if you're using Invoke-Command then Enter-PSSession might not even be needed.

Edit:
Enter-PSSession is used for interactive commands, New-PSSession and Remove-PSSession could be more appropriate for scripts, but still might not be required.
http://blogs.technet.com/b/heyscriptingguy/archive/2009/10/29/hey-scripting-guy-october-29-2009.aspx

Cheers
Gaz
Post #1604827
Posted Tuesday, August 19, 2014 7:14 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 1:08 PM
Points: 25, Visits: 166
Gazareth (8/19/2014)
Are you using SQL Server 2008 or 2008 R2?
Powershell steps on 2008/R2 only run at Powershell 1.0 or 2.0 (and only a subset at that!), Enter-PSSession may not be available to it.

If you've got Powershell 3.0+ installed on the server, a workaround is to create a Command Line step in SQL Agent & execute the ps1 script via Powershell.exe, as you mention you've already done successfully.

On the other hand, if you're using Invoke-Command then Enter-PSSession might not even be needed.

Edit:
Enter-PSSession is used for interactive commands, New-PSSession and Remove-PSSession could be more appropriate for scripts, but still might not be required.
http://blogs.technet.com/b/heyscriptingguy/archive/2009/10/29/hey-scripting-guy-october-29-2009.aspx

Cheers
Gaz


Thanks, the central server I have the Agent job on is SQL 2008 R2 but the target servers are a mix of SQL 2005 and SQL 2008 Express. I will try the above, as I was thinking last night maybe i'm overthinking this.

Now i'm thinking of just making a powershell script that:
- cycles through a list of my SQL Express servers
- during each iteration, kick off the first .bat file to initiate the daily checkdb
- upon success, kick off a second .bat file to initiate the daily checkdb
- upon success, fo to next server and repeat
- if it fails at any point, exit script and email DBA team

Once I figure out that script, I can use powershell.exe in a SQL agent job step (using cmdexec type) and try it that way.

Let's see how it goes or any other feedback is welcomed
Post #1604898
Posted Monday, September 1, 2014 8:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 5, 2014 8:05 AM
Points: 178, Visits: 147
I found powershell v3 very easy to connect to our SQL servers but if you are using SQL 2008 you will also need to install SQL 2012 managament studio to make use of the SQLPS module. Very worthwhile.

I have simple routines that perform regular tasks locally and email me results (backups, integrity, version checks, transfer results).

I am in the process of creating a script that can tell me status of all required services on each of the SQL servers. This would be ran on my machine when required thus connecting remotely.

Ultimate goal is a web page output that is refreshed every 5 minutes but unsure if PowerShell can produce. Probably but not an area I have looked before.
Post #1609357
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse