Click here to Skip to main content
12,698,179 members (23,367 online)
Click here to Skip to main content
Add your own
alternative version

Stats

53.6K views
17 bookmarked
Posted

Creating Log file for Stored Procedure

, 19 Apr 2007
Rate this:
Please Sign up or sign in to vote.
In this articleI am going to show how to create log file for stored Procedure

Introduction

We create stored procedure for our business logic. Some time for our convenience we want to record some message line by line Here I am providing one example to prepare log file for stored procedure.

Example

Creating Log file for Stored Procedure:


In this example I am going to show how to create log file for stored Procedure.

Log File will be created on Database Server.

Give write permission in c: drive or folder where to create the logfile.

Create a table Person

create table Person
(
PID int Primary Key identity (1, 1)
,Person_Name varchar(50)
,Person_Address varchar(150) not null
)

This is test table used in the stored procedure

create procedure CreateLog
(
@Msg varchar(500)
,@Start bit
)
as
begin
declare @cmd varchar(2000)
if(@Start = 1)
begin
set @cmd = 'echo --------------'+ convert(varchar(10),getDate(),101) +'------- --------------- > C:\MyLog.txt'
exec master..xp_cmdshell @cmd
end

set @cmd = 'echo ' + @Msg + ' >> C:\MyLog.txt'
exec master..xp_cmdshell @cmd
end

Now I am going to create a stored procedure in which i will insert one record and then update the Person_Address with null value which will throw error and i will record that in logfile.

create procedure PersonUpdate
(
@PID int
,@Address varchar(50)
)
as
begin
declare @LineNumber varchar(500)

begin transaction

insert into Person values ('Mack','New York')

if(@@error <> 0) goto ErrorHandler

exec CreateLog 'Mack , New York inserted in Person table',1

Update Person set Person_Address = @Address where PID = @PID

if(@@error <> 0) goto ErrorHandler

exec CreateLog 'city has been update For give PID',0

commit transaction
return
ErrorHandler:
begin
Rollback transaction
set @Msg = 'Transaction Rollbacked, Error occured'
exec CreateLog @Msg,0
End
End


now execute the command

exec PersonUpdate 1,null

Now go to C:\MyLog.txt
and open this file messages are recorded here.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

sarvesh.upadhyay
Architect
India India
Sarvesh Upadhyay is Technical Architect in Microsoft Technologies with 12 yrs of hands on experience on various domain like CRM, CMS, Inventory Management System, Telecome Billing, E-Commerce application, Retail etc.
He has indepth knowledge on Design Patterns, Asp.Net MVC, MVP , C#, JavaScript, Knockout, MS-SQL Server,Web Services,Web API, MSMQ, Saleslogix CRM, etc.

You may also be interested in...

Pro

Comments and Discussions

 
QuestionThe EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'." Pin
Member 1120784110-Dec-14 19:50
memberMember 1120784110-Dec-14 19:50 
AnswerRe: The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'." Pin
sarvesh.upadhyay27-Apr-15 0:10
professionalsarvesh.upadhyay27-Apr-15 0:10 
QuestionNext line char in sql string Pin
Milind Panchal19-Feb-13 21:13
memberMilind Panchal19-Feb-13 21:13 
QuestionHow can i auto increment the log file Pin
kneekill7-Mar-09 3:59
memberkneekill7-Mar-09 3:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170118.1 | Last Updated 19 Apr 2007
Article Copyright 2007 by sarvesh.upadhyay
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid