Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

execute powershell with parameters in sql agent job

Posted on 2014-09-25
8
518 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
I have a large data set and a SSIS package. How can I load this file in multi threading?
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

860 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