Executing Job or PERL Script in Stored Procedure

Question

Executing Job or PERL Script in Stored Procedure

dtotten@ford.com

SSC Veteran

Points: 205
More actions
November 11, 2004 at 9:17 am

#84769

I am trying to create a stored procedure that will either execute a perl script (sending pages) or execute another job with the PERL inside. What is the command to execute either PERL in the SP or another job. Below is the SQL I am using in the SP to determine if I need to execute the perl.
select b.server, a.name as Job_Name,
   b.run_date, c.next_run_date, getdate() as CRNT_DT
  from sysjobhistory b, sysjobs a, sysjobschedules c
  where a.job_id = b.job_id
   and a.job_id = c.job_id
   and b.step_id = 1
   and b.run_status <> 1
   and run_date = CAST( cast(datepart(year,getdate()) as varchar) +
    right('00' + cast(datepart(mm,getdate()) as varchar), 2) +
    right('00' + cast(datepart(dd,getdate()) as varchar), 2) as int)
My maintenance plans will email me if the jobs fail, however I do NOT want to be paged at 2:00 AM. I want to run this at 8:00 daily and send the page as another reminder of any problems. Thanks in advance.
Dan.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sqlbuff SSC Eights! Points: 964 More actions · Answer 1

You can call another job using the stored procedure call msdb.dbo.sp_start_job. I use this sp to start another job on another server and it works fine. Read Books Online for information about the parameters to pass to it.

Although I haven't used Perl, I think that you can invoke it by using the call xp_cmdshell to issue the call from the o/s.

dtotten@ford.com SSC Veteran Points: 205 More actions · Answer 2

I used the sp_start_job in the stored procedure with success. Thanks for helping automate my monitoring.

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 3

I'm just beginning to learn PERL and finding uses for it. Would you mind posting your code for paging?

Michelle

dtotten@ford.com SSC Veteran Points: 205 More actions · Answer 4

Here is the Perl for paging: I hope this helps.

#

use Socket;

if ( $#ARGV != 2 ) {

printf "Usage: sendpage.pl <from-cdsid> <to-cdsid> \"message\"\n";

exit(1);

}

($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime();

$timestamp=sprintf "%02d/%02d/%02d %2d:%2d",$mon+1,$mday,$year-100,$hour,$min;

$ret=page_send($ARGV[0],$ARGV[1],"$ARGV[2]. $timestamp");

exit $ret;

sub page_send{

my($remote,$port,$iaddr,$paddr,$buf,$error);

my $error=0;

$remote='something.something.com';

$port=Insert port number;

$iaddr=inet_aton($remote);

$paddr=sockaddr_in($port,$iaddr);

socket(SOCK,AF_INET,SOCK_STREAM,$protocol)|| return -1;

$error=connect(SOCK, $paddr);

if(! $error) {

$remote='server name - something.something.com';

$iaddr=inet_aton($remote) or return -1;

$paddr=sockaddr_in($port,$iaddr);

socket(SOCK,AF_INET,SOCK_STREAM,$protocol)|| return -1;

connect(SOCK, $paddr) || return -1;

}

if((! send SOCK, "\001$_[0] $_[1] n n $_[2]\n",0)){return -1;}

recv SOCK,$buf,500,0;

close SOCK;

print "$buf\n";

return 0;

}