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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Powershell

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 error
Msg 102, Level 15, State 1, Line 3
Incorrect 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 create
any 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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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 run

SQLCMD

which 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.
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-04-22 : 13:37:41
One option is to run sqlps via the sql server agent.
Also, If it's a batch job , you can invoke the SQLCMD through powershell. This allows you to maintain\use your script libraries across different servers. http://www.sqlserver-dba.com/2009/01/powershell-sqlcmd-and-invoke-expression.html

Are you attempting to schedule the execution of this stored procedure?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -