I am trying to execute a power shell script from SQL Server. Every thing works to me with Static value of passed arguments. But when i tried with dynamic argument its not working.
following is the statement which I am trying:
declare @dteBatchDate as varchar(20)
set @dteBatchDate='2014-06-23 15:49:00'
EXEC xp_cmdshell 'powershell.exe -c "get-service | D:\Listing1.ps1 -@dteBatchDate"'
I think it is a syntax issue. Can any body suggest solution regarding this.
Thanks in advanced
The way you are passing the variable to xp_cmdshell does not allow it to be rendered for PowerShell to see it. You are basically doing this from the view of a PowerShell prompt:
powershell.exe -c "get-service | D:\Listing1.ps1 -@dteBatchDate"
In the same manner when you need a variable rendered before the command is executed in PowerShell, you have to do the same thing with xp_cmdshell.
So building your command before you pass it to xp_cmdshell.
Try this one:
declare @dteBatchDate as varchar(20)
declare @sql as varchar(255)
set @dteBatchDate='2014-06-23 15:49:00'
set @sql = 'powershell.exe -c "get-service | D:\Listing1.ps1 -' + @dteBatchDate + '"'
EXEC xp_cmdshell @sql
