September 3, 2009 at 9:36 pm
I'm trying to learn some PS and i have a script that runs fine PowerGUI, but I can't seem to figure out how to schedule it in SQLServer Agent. I am using SQL Server 2008, but would be more than happy with a 2005 solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2009 at 7:35 am
Hey Jack,
I haven't tried this on 2005 yet, but I assume you've got to do a seperate install of PowerShell, then the SQL Server extensions. After that, you should be able to call it from the command line through Agent.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2009 at 7:57 am
September 4, 2009 at 8:09 am
Thanks guys.
Steve,
I'd been to the site and I've gotten some PS to work, but I wrote a script in PowerGUI that runs fine there, but not in an Agent Job. I think the issue is that I'm using Get-ChildItem to navigate the file system, but the PowerShell job subsystem uses SQLPS and Get-ChildItem navigates the SQL Server structure. I think I'll need to use a CMDEXEC job subsystem and call powershell from that.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2009 at 8:13 am
I pinged my resident PS expert with this thread, so hopefully Mr. White will respond.
September 4, 2009 at 8:40 am
So this section means that you can not run the Powershell extension on a 2000/2005 instance?
You must use the SQL Server 2008 client components to run SQL Server features for Windows PowerShell. The SQL Server provider for Windows PowerShell can connect to instances of SQL Server 2008, SQL Server 2005, or SQL Server 2000. The earliest version of SQL Server 2005 that you can use is SP2. The earliest version of SQL Server 2000 that you can use is SP4. When the SQL Server provider connects to SQL Server 2005 and SQL Server 2000, it is limited to the functionality available in those versions of SQL Server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2009 at 10:37 am
Hi all!
If you're using SQL 2008 Agent, you can use the PowerShell task type, then put the full path to the script into the task window. If you don't want to use sqlps.exe or you are running SQL 2005 Agent you can enter the command like this:
powershell "& C:\Demos\backup.ps1"
(This of course will execute the backup.ps1 script in the C:\Demos directory.)
In either case, PowerShell 1.0 (or greater) needs to be installed on the server running Agent. If you're running SQL 2008 PowerShell has already been installed.
Allen White
SQL Server MVP
September 4, 2009 at 10:46 am
Hey Allen. Great presentation the other day.
So, what about just installing Powershell on a 2000/2005 server, or what if the 2000/2005 is running on a 2008 Server machine? Can you get the extensions installed & run the PowerShell scripts locally?
I'm asking (and stepping on Jack's time) because I'd like to use Powershell script in Operations Manager, but you have to have Powershell installed on the server that the script is going to run on or it won't work.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2009 at 10:55 am
Thanks, Grant. I really wanted to see yours as well, but I'm at a client site during the day and couldn't.
You can install PowerShell 1.0 on any Windows Server system (2003 and up), and XP SP2 or later, and all flavors of Vista. PowerShell 2.0 comes pre-installed on Windows Server 2008 R2 and Windows 7. Just go to microsoft.com and download the flavor of PowerShell for your operating system and install it and you're good to go.
Did that answer your question?
Allen
September 4, 2009 at 11:03 am
AllenMWhite (9/4/2009)
Thanks, Grant. I really wanted to see yours as well, but I'm at a client site during the day and couldn't.You can install PowerShell 1.0 on any Windows Server system (2003 and up), and XP SP2 or later, and all flavors of Vista. PowerShell 2.0 comes pre-installed on Windows Server 2008 R2 and Windows 7. Just go to microsoft.com and download the flavor of PowerShell for your operating system and install it and you're good to go.
Did that answer your question?
Allen
Thanks. Not exactly. After I do the above, can I get the SQL extensions installed and then run Powershell scripts from the newly installed .DLL's against a SQL Server 2000 or 2005 server? That's how it would have to work to use PowerShell with Operations Manager & 2000 or 2005.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2009 at 11:03 am
One more item, while I'm thinking about it. You do need the SMO dlls to communicate with SQL Server from PowerShell, so from the MSDN article:
http://msdn.microsoft.com/en-us/library/ms162189.aspx
The SMO files can be specifically added or removed from the installation by choosing the SDK branch of the Client Components option on the Feature Selection screen during setup.
So, just install the SDK files from an installation source and install PowerShell, and you'll have everything you need.
Allen
September 4, 2009 at 11:16 am
Ah. Ok. Go to Michiel Wories' blog post here:
http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx
Copy out the script in that post. Once you've executed that, you've got the SQL 2008 dlls and PowerShell snapins loaded, then you can use the new features. I've used that script to load the snapins into both PowerShell 1.0 and PowerShell 2.0 without any problems. (You'll need to load the Client SDK before you run this script, though.)
Allen
September 4, 2009 at 11:18 am
AllenMWhite (9/4/2009)
Ah. Ok. Go to Michiel Wories' blog post here:http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx
Copy out the script in that post. Once you've executed that, you've got the SQL 2008 dlls and PowerShell snapins loaded, then you can use the new features. I've used that script to load the snapins into both PowerShell 1.0 and PowerShell 2.0 without any problems. (You'll need to load the Client SDK before you run this script, though.)
Allen
WICKED!
That's great. Thanks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 4, 2009 at 11:19 am
In case I didn't mention it before, you can run scripts using SQL 2008 SMO against SQL 2005 and SQL 2000 servers. SMO doesn't support SQL 7 or earlier, though.
Allen
September 4, 2009 at 11:22 am
AllenMWhite (9/4/2009)
In case I didn't mention it before, you can run scripts using SQL 2008 SMO against SQL 2005 and SQL 2000 servers. SMO doesn't support SQL 7 or earlier, though.Allen
Yeah, I learned that one from you last year at the Summit.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply