Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Connecting to remote server using powershell subtype


Connecting to remote server using powershell subtype

Author
Message
msandico 57892
msandico 57892
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 195
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..
Gary Varga
Gary Varga
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8366 Visits: 6161
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
Gazareth
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2872 Visits: 5351
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
msandico 57892
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 195
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
Drenlin
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 165
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search