question

swiden avatar image
swiden asked

How to execute powershell script from sql server stored procedure?

I want to pass BCP commands from a SP to be executed by a powershell script.

stored-proceduressql serverscriptpowershellexecute
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

borispinsky avatar image
borispinsky answered

You have a few options:

1. The easy way is to use xp_cmdshell inside your procedure (be aware of a security risk )

2.Create SQL server job with Powershell step and run the job fro your procedure

3.Build a SSIS package with Process Task run the package from your procedure

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

swiden avatar image
swiden answered

@swiden

Hi!

Thanks for your contribution!

In a SQL agent job, T-SQL step that processes a stored procedure. From that SP do I execute a Powershell script as:

set @CmdPowershell ='Powershell -file '+char(34)+'\\SRVqwert\SWIDEN\PS_script.ps1'+char(34)

EXEC @rc =MASTER..xp_cmdshell @CmdPowershell

if @rc != 0

-----

I have issues with error "must be digitally sign". I don´t know how to go further.

-----

xp_cmdshell is used just to reach Powershell. Any way to avoid xp_cmdshell? How much security risk is xp_cmdshell?, impossible question but..


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.