November 11, 2004 at 9:17 am
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.
November 12, 2004 at 6:47 am
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.
November 16, 2004 at 12:41 pm
I used the sp_start_job in the stored procedure with success. Thanks for helping automate my monitoring.
November 16, 2004 at 3:35 pm
I'm just beginning to learn PERL and finding uses for it. Would you mind posting your code for paging?
Michelle
November 18, 2004 at 12:34 pm
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;
}
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy