how-to debug in sql server 2005

USE [db] GO

/** Object: Table [dbo].[tbData_Debug] Script Date: 02/12/2009 22:30:03 **/ SET ANSI_NULLS ON GO

SET QUOTED_IDENTIFIER ON GO

SET ANSI_PADDING ON GO

CREATE TABLE [dbo].tbData_Debug NULL, [colDomain_User] varchar NULL, [colMsg] varchar NULL, [colDebugLevel] [int] NULL, [colDebugMsg] varchar NULL, [colPageName] varchar NULL, [colClassName] varchar NULL, [colMethodName] varchar NULL, [colMethodNameGui] varchar NULL, [colRet] [int] NULL, [colLineNumber] [int] NULL, [colLineNumberGui] [int] NULL, [colProcedureName] varchar NULL, [colProcedureStep] varchar NULL ) ON [PRIMARY]

GO

SET ANSI_PADDING OFF GO

USE [db] GO /** Object: StoredProcedure [dbo].[procUtils_AppDebug] Script Date: 02/12/2009 22:29:24 **/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

ALTER PROCEDURE [dbo].[procUtils_AppDebug] (

@ret int = null OUT,
@msgIn varchar(4000) = null , -- the msg which the user has seen
@msgOut varchar(4000) = null OUT , -- the msg which the user has seen
@domain_user varchar(200) = null , -- the domain user echoing the message
@debugMsgIn varchar(4000) = null , -- the debug msg for internal use
@debugMsgOut varchar(4000) = null OUT, -- the debug msg for internal use
@pageName varchar(200) = null , -- the pageName originator of error
@className varchar(200) = null , -- the class Name orinator of error
@methodName varchar(200) = null , -- the methodName where the last error occured
@methodNameGui varchar(4000) = null , -- the methodNameOfTheGui where the last error occured
@lineNumber int = null , -- the line number of the line issueing the error
@lineNumberGui int = null, -- the line number of the line issueing the error
@procedureName varchar(200) = null , -- the procedureName currently envoked @procedureStep varchar(4000) = null -- the steps of the procedure concatenated )

AS
BEGIN -- proc start
SET NOCOUNT ON;

BEGIN TRY --begin try

declare @debugLevel int
select @debugLevel = Debug_Level from User_tb where Domain_Name = @domain_user

/*
select * from tbData_Debug order by 1 desc
delete from tbData_Debug
*/

insert into tbData_Debug ( colNiceTime , colDomain_User , colMsg , colDebugLevel ,
colDebugMsg , colPageName , colClassName , colMethodName , colMethodNameGui ,
colRet , colLineNumber , colLineNumberGui , colProcedureName , colProcedureStep) values ( dbo.funcGetNiceTime() , @domain_user , @msgIn , @debugLevel ,@debugMsgIn , @pageName , @className , @methodName ,@MethodNameGui , @ret , @lineNumber , @lineNumberGui , @procedureName , @procedureStep)

set @debugMsgOut = @debugMsgIn
set @msgOut = 'Action Registered'
set @ret = @@ERROR
return @ret

END TRY --end try

BEGIN CATCH
PRINT 'In CATCH block.
Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
Error message: ' + ERROR_MESSAGE() + '
Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));

set @debugMsgOut = 'error at [procUtils_AppDebug]--- Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + 'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +
CAST(ERROR_SEVERITY() AS varchar(10)) + 'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))
set @msgIn= 'error while saving application error info into database'
insert into tbData_Debug ( colMsg ) values ( @msgIn )

set @debugMsgOut = @debugMsgIn + @debugMsgOut
set @msgOut = 'Action Registration failed'
set @ret = 1

END CATCH

return @ret
END --procedure end

/*

procUtils_AppDebug
Records events from the Application Layer
20090121
Yordan Georgiev






USE [db]
GO

DECLARE @return_value int,
@ret int,
@msgIn varchar(max),
@debugmsg varchar(4000)

SELECT @ret = 1
SELECT @msgIn = N'msg'
SELECT @debugmsg = N'before'

EXEC @return_value = [dbo].[procUtils_AppDebug]
@ret = @ret OUTPUT,
@msgIn = @msgIn OUTPUT,
@domain_user = N'domain_user',
@debugmsg = @debugmsg OUTPUT,

@methodName = N'methodName'

SELECT @ret as N'@ret',
@msgIn as N'@msgIn',
@debugmsg as N'@debugmsg'

SELECT 'Return Value' = @return_value
select * from tbData_Debug order by 1 desc
GO



*/