Log Creation inside stored procedure

[13415]

I want to create a log in text format inside a stored procedure of sql seve 7.0 for database acivities, like
procedure stared at 12:30 Am
2000 rows inserted
procedure completed at 13:30

please advise me.
thanks

 

[dbtech]

U need to create a table to capture the details.

Ex
Create table auditsp(
Spname varchar(30),
StartTime datetime,
EndTime datetime,
rowsaffected int)

Inside your sp
create proc test1
as
Declare @startTime datetime
SET @startTime =GETDATE()
/*ur code*/
insert something
Insert into audit('test1',@starttime,getdate(),@@rowcount)

this an outline only, You can capture the user who executed the sp and process also using suser_sname(), @@process

dbtech

 

[tlbroadbent]

You can use xp_cmdshell if the Administrator allows that.

declare @msg varchar(80)

Set @msg='echo process started at ' + convert(varchar(21),getdate()) + '>c:\temp\proclog.txt'
Exec xp_cmdShell @msg
.
. More statements
.
Set @msg='echo Phase 1 completed at ' + convert(varchar(21),getdate()) + '>>c:\temp\proclog.txt'
Exec xp_cmdShell @msg
.
. More statements
.
Set @msg='echo Phase 2 completed at ' + convert(varchar(21),getdate()) + '>>c:\temp\proclog.txt'
Exec xp_cmdShell @msg
.
.
. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin