if object_Id( 'dbspDBARpt_DBALog') Is Not Null
 drop procedure dbspDBARpt_DBALog
go

CREATE procedure dbspDBARpt_DBALog
as
/*
*************************************************************
Name: dbspDBARpt_DBALog
Description:
   Adds DBALog log entries to the Daily dba report.
Use this insert to seed the PrcssTrckr table with the initial
value for transfers.
 insert PrcssTrckr (tblname, lastxfr, laststatus) 
  values ('DBALog', '01/01/1900', 0)

Usage:exec dbspDBARpt_DBALog

Author: Steve Jones

Input Params:
-------------

Output Params:
--------------

Return:

Results:
---------

Locals:
--------
@err		Holds error value
@txt		txt message begin inserted into report.
@typ		Type of entry being added to the report
@db		Name of database

Modifications:
--------------
11-16-2000 jsj
   Added an update to PrcssTrckr to update the log date.
11-30-2000 jsj
   Added a section to insert a note if there are no log entries.
*************************************************************
*/
set nocount on
declare 	@err int,
			@txt char( 80),
			@typ char( 20),
			@db char( 40),
			@cmd varchar( 500), @tbl char( 40)

select @err = 0
/*
Check parameters and exit if not correct.
*/
if @err = -1
 begin
  Raiserror( 'Parameter Error:Usage:exec dbspDBARpt_DBALog', 12, 1)
  return @err
 end

select @typ = 'DBALog'

insert DBARpt ( Srvr, Typ, txt, entrydt) values ( @@servername, @typ, ' ', getdate())

select @txt = 'Misc DBALog Entry Report'
insert DBARpt ( Srvr, Typ, txt, entrydt) values ( @@servername, @typ, @txt, getdate())
select @txt = '---------------------------------------------------------'
insert DBARpt ( Srvr, Typ, txt, entrydt) values ( @@servername, @typ, @txt, getdate())

insert DBARpt ( Srvr, Typ, txt, entrydt)
 select 	@@servername, 
			@typ,
			rtrim( cat) + ':' + rtrim( msg), 
			getdate()
  from DBALog d, PrcssTrckr df
  where df.tblname = 'DBALog'
  and d.entrydt > df.lastxfr

/*
If no rows are inserted, then add a msg
*/
if @@rowcount = 0
 insert DBARpt ( Srvr, Typ, txt, entrydt) 
  select @@servername, 
		@typ, 
		'No Entries',
		getdate()

/*
Update the log date
*/
update PrcssTrckr
 set lastxfr = getdate()
 where tblname = 'DBALog'

return @err
GO
if object_id( 'dbspDBARpt_DBALog') Is Null
 select 'Error:dbspDBARpt_DBALog Not Created'
else
 select 'dbspDBARpt_DBALog Created'
go