PowerShell, xp_cmdshell, and T-SQL

Question

PowerShell, xp_cmdshell, and T-SQL

Jared

SSC Guru

Points: 61793
More actions
December 15, 2011 at 10:29 am

#248238

I am currently working on learning PowerShell, but am still struggling with the general usage of it with SQL Server. I know many people try to discourage the use of xp_cmdshell, but many of our stored procedures use it. One of the questions that my co-worker had was how we call or open a session of PowerShell in a stored proc. I believe the answer is "you don't." For example, he wants to run some select statement and use the results then in an xp_cmdshell command. How would this be done using PowerShell; i.e. Not using stored proc to call on Powershell, but using PowerShell script to call on SQL Server.
My inquiry here is very general. Basically, give me some real-world application of PowerShell with SQL Server and a description of flow for that application.
Jared
Jared
CE - Microsoft

Viewing 13 posts - 1 through 12 (of 12 total)

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

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 1

SQLKnowItAll (12/15/2011)
One of the questions that my co-worker had was how we call or open a session of PowerShell in a stored proc. I believe the answer is "you don't."

BWAA-HAAA!!!! Ironically, one of the answers is "Call Power-Shell through xp_CmdShell". 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

I think this article is a good starting point.

http://msdn.microsoft.com/en-us/library/cc281962.aspx

There are many many administrative tasks that can be accomplished through powershell. It largely depends on what you would like to do.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jared SSC Guru Points: 61793 More actions · Answer 3

Jeff Moden (12/15/2011)
SQLKnowItAll (12/15/2011)
One of the questions that my co-worker had was how we call or open a session of PowerShell in a stored proc. I believe the answer is "you don't."
BWAA-HAAA!!!! Ironically, one of the answers is "Call Power-Shell through xp_CmdShell". 😛

BWAA-HA-HAAA!!!! lol Does that not defeat the point? I like the "idea" of not doing these types of tasks through SQL, but the other way around. I guess I really have to come up with reasons WHY it makes more sense to use SQL as a tool accessed through PowerShell instead of PowerShell as a tool through SQL. 🙂 It is hard to teach an old dog new tricks...

Jared

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 4

SQLRNNR (12/15/2011)
I think this article is a good starting point.
http://msdn.microsoft.com/en-us/library/cc281962.aspx
There are many many administrative tasks that can be accomplished through powershell. It largely depends on what you would like to do.

Thanks Jason, but I guess that's what I am asking here is some ways that people use it in a real world scenario instead of Microsoft's description of it. Its like when I first read about Service Broker on MSDN or BOL... "A way to send messages synchronously or asynchronously..." I thought literal messages like emails. 😛

Jared

Jared
CE - Microsoft

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

In that case, let's have you browse the SQL Powershell guru Aaron Nelson. He uses powershell for all sorts of stuff in SQL Server.

http://sqlvariant.com/wordpress/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jared SSC Guru Points: 61793 More actions · Answer 6

SQLRNNR (12/15/2011)
In that case, let's have you browse the SQL Powershell guru Aaron Nelson. He uses powershell for all sorts of stuff in SQL Server.
http://sqlvariant.com/wordpress/

AWESOME! Exactly what I was looking for 🙂 Thanks!

Jared

Jared
CE - Microsoft

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 7

SQLKnowItAll (12/15/2011)
Does that not defeat the point?

Not in my book. 🙂 Think about why they added the CLR capability... it's to extend what you can do from a stored procedure in T-SQL. The combination of xp_CmdShell (properly controlled, of course, and it's not that hard to do) and Powershell provides a huge extension to what can be done in SQL.

The next sound you hear will be about 10,000 people who will say something like "Just because you can do it in SQL, doesn't mean you should." My comment to that would be that just because you can do it in something other than SQL, doesn't mean you should. 😛 Right after that, they'll say "You should use the right tool for the right task" and I agree... call it from SQL which is where the data lives or will live. :hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jared SSC Guru Points: 61793 More actions · Answer 8

Jeff Moden (12/15/2011)
SQLKnowItAll (12/15/2011)
Does that not defeat the point?
Not in my book. 🙂 Think about why they added the CLR capability... it's to extend what you can do from a stored procedure in T-SQL. The combination of xp_CmdShell (properly controlled, of course, and it's not that hard to do) and Powershell provides a huge extension to what can be done in SQL.
The next sound you hear will be about 10,000 people who will say something like "Just because you can do it in SQL, doesn't mean you should." My comment to that would be that just because you can do it in something other than SQL, doesn't mean you should. 😛 Right after that, they'll say "You should use the right tool for the right task" and I agree... call it from SQL which is where the data lives or will live. :hehe:

You always have a logical way of putting things frankly, Jeff. I guess I was trying to make an argument that did not need to be made. Of course, that's assuming we have proper controls in place, which I believe we do. Like you said, it depends on the task needed and I think it best to analyze that s you would any decisions for a proper deployment of something new. Thanks again!

Jared

Jared
CE - Microsoft

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 9

Completely off the walll and not what I would do, but you could start SQL Agent job with powershell steps from a stored procedure.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 10

MysteryJimbo (12/16/2011)
Completely off the walll and not what I would do, but you could start SQL Agent job with powershell steps from a stored procedure.

I don't believe that's such an off-the-wall idea at all. In fact, that's one of the couple ways to execute xp_CmdShell (or Powershell, etc) without the user being able to execute it directly. It's also what can make SSIS routines so powerfull... calls to sprocs, calls to scripts, calls to just about anything.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 11

SQLKnowItAll (12/15/2011)
You always have a logical way of putting things frankly, Jeff. I guess I was trying to make an argument that did not need to be made. Of course, that's assuming we have proper controls in place, which I believe we do. Like you said, it depends on the task needed and I think it best to analyze that s you would any decisions for a proper deployment of something new. Thanks again!

Nah... you brought up a very good point especially since it's a frequent subject on these fine forums and you did it without carrying the proverbial war-club that most bring to bear when the subject arises. You can tell by my response that I've faced many a war-club on the subject. 🙂 Thank you for taking the time to talk about the subject without trying to porkchop me as so many have tried. I really appreciate it, Jared.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

SQLKnowItAll (12/15/2011)
SQLRNNR (12/15/2011)
In that case, let's have you browse the SQL Powershell guru Aaron Nelson. He uses powershell for all sorts of stuff in SQL Server.
http://sqlvariant.com/wordpress/
AWESOME! Exactly what I was looking for 🙂 Thanks!
Jared

Good - I hope it works out well.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events