Solved

execute powershell with parameters in sql agent job

Posted on 2014-09-25
8
433 Views
Last Modified: 2014-10-27
Hello,

I can execute the following script in cmdexec window. But when I add it on a step in SQL AGENT Job, the job executed successfully but do nothing :

powershell -nologo -command "& {&'c:\script\backup_disk.ps1' -I SRVTEST -T F -R 24 -D \\nas$\srvtest -L C:\script\logs}"

Thanks

Regards
0
Comment
Question by:bibi92
  • 3
  • 3
  • 2
8 Comments
 
LVL 28

Expert Comment

by:becraig
Comment Utility
Are you saying you run the powershell script under sql agent and nothing happens ?

Can you maybe put a start and stop transcript into your backup_disk.ps1 script and take a look at the output.
It might be an issue with a parameter or some other input.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
In the step properties(in edit window) make sure that the type of command is Powershell instead of Transact-SQL.
0
 

Author Comment

by:bibi92
Comment Utility
It's an cmdexec step and not transact-sql.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
There is also  Powershell command.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:bibi92
Comment Utility
ok I know but I have to replace powershell command by cmdexec because there are only two threads powershell for SQL AGENT.
0
 
LVL 28

Expert Comment

by:becraig
Comment Utility
Have you actually tried seeing what happens when your script is run by sql agent ?

Try adding the Executionpolicy to your powershell command, it may be an execution restriction issue.

powershell -Executionpolicy Bypass -nologo -command "& {&'c:\script\backup_disk.ps1' -I SRVTEST -T F -R 24 -D \\nas$\srvtest -L C:\script\logs}"
0
 

Author Comment

by:bibi92
Comment Utility
Have you actually tried seeing what happens when your script is run by sql agent ?

Yes, I have add output log in the step but the file is empty

I have tested , but same result :
powershell -Executionpolicy Bypass -nologo -command "& {&'c:\script\backup_disk.ps1' -I SRVTEST -T F -R 24 -D \\nas$\srvtest -L C:\script\logs}"

Thanks
0
 
LVL 28

Accepted Solution

by:
becraig earned 500 total points
Comment Utility
Please edit c:\script\backup_disk.ps1

Add the following lines at the start of the script:
start-transcript -path c:\somefile.txt -noclobber


Then add  the line below to the end of the script:
stop-transcript

We can then open "c:\somefile.txt and see what is really happening when powershell calls c:\script\backup_disk.ps1
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
Synchronize a new Active Directory domain with an existing Office 365 tenant
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now