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