Connecting to remote server using powershell subtype

Question

Connecting to remote server using powershell subtype

msandico 57892

Ten Centuries

Points: 1063
More actions
August 18, 2014 at 1:20 pm

#292468

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

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

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

Gary Varga SSC Guru Points: 82166 More actions · Answer 1

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 2

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

msandico 57892 Ten Centuries Points: 1063 More actions · Answer 3

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

Drenlin Ten Centuries Points: 1099 More actions · Answer 4

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.