Code:
use Win32::ODBC;
use Time::Local;
use Getopt::Std;
my $cString = sprintf("Driver=SQL Server;Server=szrh721;Database=pagesSys_5701SR4;Trusted Connection=yes");
my $filename="C:\\Perl\\scriptt\\DB.txt";
#my $filename="P:\\DB.txt";
unlink($filename);
$dbQuery = "BEGIN
SET NOCOUNT ON
DECLARE
\@V_BATCHJOBID INT ,
\@V_jobaccountid INT ,
\@V_jobaccountstatusid INT,
\@V_PCName VARCHAR(50),
\@V_JobName VARCHAR(50),
\@V_Description VARCHAR(50),
\@V_ErrorMessage VARCHAR(3000),
\@V_StatusTimeStamp DATETIME ,
\@V_Flag_Stuck VARCHAR(2),
\@V_Limit varchar(2)
-- One hour
SET \@V_Limit =1
DECLARE CUR_PEND CURSOR FOR
SELECT JobType.BATCHJOBID,
PCStatus.jobaccountid,
CurrStatus.jobaccountstatusid,
JobType.PCName,
JobType.Name,
PCStatus.Description,
PCStatus.ErrorMessage,
PCStatus.StatusTimeStamp
FROM (SELECT BJ.PCName, BJ.BATCHJOBID AS BATCHJOBID ,BJ.Name as JobName, BJT.Name, BJT.TimeLimit
FROM BatchJob BJ (nolock), BatchJobType BJT (nolock)
WHERE BJ.BatchJobTypeID = BJT.BatchJobTypeID
AND (BJ.BatchJobStatusID = 1 OR BJ.BatchJobStatusID = 3)
AND BJ.BatchJobID =
(SELECT Min(BJ2.BatchJobID)
FROM BatchJob BJ2 (nolock)
WHERE BJ2.PCName = BJ.PCName
and (BJ.BatchJobStatusID = 1 OR BJ.BatchJobStatusID = 3))) JobType
LEFT OUTER JOIN (SELECT Distinct Jas.PCName,JAS.jobaccountstatusid,JAS.jobaccountid,
Acs.Description,
Jas.StatusTimeStamp,
Jas.ErrorMessage
FROM AccountStatus Acs (nolock), JobAccountStatus Jas (nolock)
WHERE Jas.StatusID = Acs.StatusID
AND Jas.StatusTimeStamp =
(SELECT Max(Jas2.StatusTimeStamp)
FROM JobAccountStatus Jas2 (nolock)
WHERE Jas.PCName = Jas2.PCName)) PCStatus ON JobType.PCName =
PCStatus.PCName
LEFT OUTER JOIN (SELECT BJ.PCName, Count(*) as c
FROM BatchJob BJ (nolock)
WHERE (BJ.BatchJobStatusID = 1 OR
BJ.BatchJobStatusID = 3)
Group by PCName) Assigned ON JobType.PCName =
Assigned.PCName
JOIN (select jobaccountstatusid from JobAccountStatus(nolock)) CurrStatus
on CurrStatus.jobaccountstatusid=PCStatus.jobaccountstatusid
OPEN CUR_PEND
FETCH NEXT FROM CUR_PEND INTO
\@V_BATCHJOBID ,
\@V_jobaccountid ,
\@V_jobaccountstatusid,
\@V_PCName ,
\@V_JobName ,
\@V_Description ,
\@V_ErrorMessage ,
\@V_StatusTimeStamp
WHILE \@\@FETCH_STATUS = 0
BEGIN
--- Find current pending reports
SET \@V_Flag_Stuck= (SELECT COUNT(b.BatchJobID)
FROM BatchJob b (nolock)
WHERE b.NextDate <= getdate()
AND b.Active = 1
AND b.BatchJobStatusID <= 3
AND B.BATCHJOBID <> \@V_BATCHJOBID)
-- FORMAT FOR OUTPUT
/*
select @v_batchjobid as Batchjobid,
@V_StatusTimeStamp as VisibleTime,
BJ2.STARTDATE as ActualStartTime,
@V_PCName as PCNAME,
datediff(mi,BJ2.STARTDATE,getdate())as RunTime from BatchJob BJ2
WHERE BJ2.BATCHJOBID= @V_BATCHJOBID
and datediff(mi,BJ2.STARTDATE,getdate()) > 2 */
select 'JOB ID: '+cast(\@v_batchjobid as varchar(12))+
' STARTED AT: '+ CAST( \@V_StatusTimeStamp AS VARCHAR(20))+
' ON '+ \@V_PCName +
' HAS BEEN RUNNING FOR MORE THAN '+ CAST( datediff(hh,BJ2.STARTDATE,getdate()) AS VARCHAR(8))+
' Hour(s). CURRENTLY '+
CAST (\@V_Flag_Stuck AS VARCHAR(10)) +
' WAITING ' as STATUS from BatchJob BJ2 (nolock)
WHERE BJ2.BATCHJOBID= \@V_BATCHJOBID
and datediff(mi,BJ2.STARTDATE,getdate()) >= \@V_LIMIT
FETCH NEXT FROM CUR_PEND INTO
\@V_BATCHJOBID ,
\@V_jobaccountid ,
\@V_jobaccountstatusid,
\@V_PCName ,
\@V_JobName ,
\@V_Description ,
\@V_ErrorMessage ,
\@V_StatusTimeStamp
END
CLOSE CUR_PEND
DEALLOCATE CUR_PEND
END";
############################################################
# connect
my $Conn;
$Conn = new Win32::ODBC($cString);
die "Unable to connect to database" . Win32::ODBC::Error( ) . "\n"
unless (defined $Conn);
#printf("Using %s database on %s \n\n", $configData->{"db"}, $configData->{"server"} );
#printf("%s\n\n", $sql);
if( $Conn->Sql($dbQuery ) )
{
print "Query failed. \n";
print "Error: " . $Conn->Error() . "\n";
print $dbQuery;
$Conn->Close();
exit(1);
}
my $temp="";
open(Spool, ">>$filename") or die "Error opening $filename\n";
while( $Conn->FetchRow() )
{
my $Temp =$Conn->Data("STATUS");
#print "Value is $Temp";
print Spool "This line goes to the file.\n"; #Testing
printf(Spool "%s",$Conn->Data("STATUS" ));
}
$Conn->Close();
close Spool;
###Read Spool file if it has something to say
open(Spool, "> $filename") or die "Error opening $filename\n";
while (<spool>) {}
my $lineCtr = $.;
print $lineCtr;
if (@linctr >0) {
#Send Email using VB code
system("start C:\\My-Scripts\\Email_Batch.vbs");
}
#unlink($filename);
exit(0);
