Solved

execute powershell with parameters in sql agent job

Posted on 2014-09-25
8
493 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 29

Expert Comment

by:becraig
ID: 40347216
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
ID: 40347484
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
ID: 40348446
It's an cmdexec step and not transact-sql.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 40348584
There is also  Powershell command.
0
 

Author Comment

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

Expert Comment

by:becraig
ID: 40350377
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
ID: 40362532
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 29

Accepted Solution

by:
becraig earned 500 total points
ID: 40362628
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article will help you understand what HashTables are and how to use them in PowerShell.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

777 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