?
Solved

How can I create a SQL query to call a scheduled task?

Posted on 2009-12-28
10
Medium Priority
?
531 Views
Last Modified: 2012-08-14
In my query, I need to check if a file exists, and if exists, using SQL I want to call a scheduled task. Please how can I go about doing this?

0
Comment
Question by:cgwinn
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26134522
Xp_fileexist 'C:\Test\*.bat'
0
 
LVL 83

Expert Comment

by:leakim971
ID: 26134680
Hello cgwinn,

Try :


DECLARE @i INT
EXEC master..xp_fileexist 'C:\PATH\TO\FILE', @i OUTPUT;
IF @i=1
   SELECT 1
ELSE 
   SELECT 2
go

Open in new window

0
 
LVL 83

Expert Comment

by:leakim971
ID: 26134685
Sorry aneeshattingal,
not refreshed the page.

cgwinn please don't give me points
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 1

Expert Comment

by:jperry31
ID: 26136867
Use EXEC msdb.dbo.sp_start_job N'Your Job name' ;
to call a scheduled task
0
 

Author Comment

by:cgwinn
ID: 26140093
@jperry31,

I tried using the Use EXEC msdb.dbo.sp_start_job N'Your Job name' command

But I got the error message, "The specified @job_name ('Scheduled_Task_Name') does not exist."

Does this only work for DTS jobs or does it also work for scheduled tasks?
0
 

Author Comment

by:cgwinn
ID: 26140452
@aneeshattingal,

I used the xp_fileexist command but it only gives me the information on whether the file exists or not.
0
 

Author Comment

by:cgwinn
ID: 26145903
This is the command I am trying to run:
[b]C:\Program Files\Monarch Data Pump\PumpCmd.exe -q -s "MONARCH_JOB_NAME"[/b]

It works fine when I run from the command prompt. But I want to add it to a stored procedure.

I have tried using the following xp_cmdshell command with no luck.
[b]exec master.dbo.xp_cmdshell 'dir C:\Program Files\Monarch Data Pump\PumpCmd.exe -q -s "MONARCH_JOB_NAME"', no_output[/b]


Please how do I write this correctly to make it work?

Thanks
0
 

Author Comment

by:cgwinn
ID: 26145918
Please ignore the last post. Here is the correct thing I meant to write:

I came across your response to an xp_cmdshell question and I was hoping you could help.
This is the command I am trying to run:
C:\Program Files\Monarch Data Pump\PumpCmd.exe -q -s "MONARCH_JOB_NAME"

It works fine when I run from the command prompt. But I want to add it to a stored procedure.

I have tried using the following xp_cmdshell command with no luck.
exec master.dbo.xp_cmdshell 'dir C:\Program Files\Monarch Data Pump\PumpCmd.exe -q -s "MONARCH_JOB_NAME"'


Please how do I write this correctly to make it work?

Thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26151389
>>I have tried using the following xp_cmdshell command with no luck.<<
What does this mean?

Have you enabled the used of xp_cmdshell ?
0
 
LVL 1

Accepted Solution

by:
jperry31 earned 200 total points
ID: 26152454
Sorry the sp_start_job is for SQL Agent jobs. With regards to xp_cmdshell if it is enabled andl and it still doesnt work it is possible that the permissions are preventing SQL from running the file. The account used to run the SQL service would need to have execute permissions for the .cmd file in the C:\Program Files\Monarch Data Pump directory. Either that or move the file to a directory area where SQL already has permissions. One other thing. You have put "dir" at beginning of your command statement, is that intentional as this will not run the file, it lists files and subdirectories.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

839 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