Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
HDangerfield
Starting Member
2 Posts |
 Posted - 2015-04-22 : 06:35:40
|
| I have a stored procedure that I need to embed powershell into to execute a command to make folders however the procedure parses ok and I can press the execute icon and the command is completed successfully however when I execute the stored procedure I get the following errorMsg 102, Level 15, State 1, Line 3Incorrect syntax near '\'.the code is select @command = 'C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe invoke-command ' + @Command exec (@command)where @command has the path and folder to createany one got any ideas |
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-22 : 06:56:37
|
| [code]EXEC master.dbo.xp_cmdshell @command[/code]??note that to execute xp_cmdshell requires granting specific permission. |
 |
|
|
HDangerfield
Starting Member
2 Posts |
Posted - 2015-04-22 : 11:20:18
|
| Thank you Kristen,I was told that I had to use powershell only not xp_cmdshell I don't know enough about either to sort it out unfortunately |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2015-04-22 : 11:34:08
|
| Kristen is exactly right. When you run a Powershell command (or a bat file or a command file script) from T-SQL you are accessing the OS outside of the SQL Server environment. In order for that to work, you have to enable xp_cmdshell.To put it another way, enabling xp_cmdshell simply allows you to go to the OS and ask it to do things (such as running a powershell script).The usual usage pattern is the other way around - i.e., you use Powershell from a powershell window or script to access SQL Server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2015-04-22 : 11:46:12
|
quote:
Originally posted by HDangerfield
I was told that I had to use powershell only not xp_cmdshell I don't know enough about either to sort it out unfortunately
You are doing EXEC (something)The "something" has to be SQL commands. You are trying to run a PowerShell Command ... can't do that.SQL has a special extended procedure xp_cmdshell which runs a Command Line (Shell) on the server, and via that you can run Batch Files or Power Shell commands. Its open to abuse though, so by default it is disabled.As James has said the other way is to run a PowerShell command and, from that, run an SQL Script. I don't know anything about PowerShell, so maybe there is a more "direct" route (such as ODBC or OleDB ... or DotNet  ) but if I was doing that from a BATCH file I would runSQLCMDwhich allows you to specify server, security details, and a Script to run - the SQL in the script is then executed in the context of the permissions etc. that the SQLCMD command runs with. |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
|
|
|
|
|
Powershell