1: IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_EventsLog_EventDate]') AND type = 'D')
2: BEGIN
3: ALTER TABLE [dbo].[DdlLog] DROP CONSTRAINT [DF_EventsLog_EventDate]
4: END
5:
6: GO
7:
8: /****** Object: Table [dbo].[DdlLog] Script Date: 04/09/2010 23:15:32 ******/
9: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DdlLog]') AND type in (N'U'))
10: DROP TABLE [dbo].[DdlLog]
11: GO
12:
13: /****** Object: Table [dbo].[DdlLog] Script Date: 04/09/2010 23:15:33 ******/
14: SET ANSI_NULLS ON
15: GO
16:
17: SET QUOTED_IDENTIFIER ON
18: GO
19:
20: CREATE TABLE [dbo].[DdlLog](
21: [LogId] [int] IDENTITY(1,1) NOT NULL,
22: [DatabaseName] [varchar](256) NOT NULL,
23: [EventType] [varchar](50) NOT NULL,
24: [ObjectName] [varchar](256) NOT NULL,
25: [ObjectType] [varchar](25) NOT NULL,
26: [SqlCommand] [varchar](max) NOT NULL,
27: [EventDate] [datetime] NOT NULL,
28: [LoginName] [varchar](256) NOT NULL
29: ) ON [PRIMARY]
30:
31: GO
32:
33: EXEC sys.sp_addextendedproperty @name=N'0.0.2.201003119', @value=N'Adding metadata for versioning(ysg)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
34: GO
35:
36: EXEC sys.sp_addextendedproperty @name=N'CurrentVersion', @value=N'0.0.2.201003119' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
37: GO
38:
39: EXEC sys.sp_addextendedproperty @name=N'IsPlatform', @value=N'1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
40: GO
41:
42: EXEC sys.sp_addextendedproperty @name=N'LastEditor', @value=N'Yordan Georgiev' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
43: GO
44:
45: EXEC sys.sp_addextendedproperty @name=N'Mar 10 2010 4:12PM', @value=N'Adding metadata for versioning(ysg)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DdlLog'
46: GO
47:
48: ALTER TABLE [dbo].[DdlLog] ADD CONSTRAINT [DF_EventsLog_EventDate] DEFAULT (getdate()) FOR [EventDate]
49: GO
50:
51:
1: /****** Object: StoredProcedure [dbo].[procUtils_Debug] Script Date: 04/09/2010 23:17:23 ******/
2: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_Debug]') AND type in (N'P', N'PC'))
3: DROP PROCEDURE [dbo].[procUtils_Debug]
4: GO
5:
6: /****** Object: StoredProcedure [dbo].[procUtils_Debug] Script Date: 04/09/2010 23:17:23 ******/
7: SET ANSI_NULLS ON
8: GO
9:
10: SET QUOTED_IDENTIFIER ON
11: GO
12:
13:
14:
15: CREATE PROCEDURE [dbo].[procUtils_Debug]
16: -- BEGIN DEFAULT SQL API
17: @UserSessionId [bigint],
18: @PageTypeId [int],
19: @Msg [varchar](200) OUTPUT,
20: @DebugMsg [varchar](200) OUTPUT,
21: @Ret [int] OUTPUT ,
22: -- END DEFAULT SQL API
23: @Time datetime= NULL,
24: @NiceTime varchar(200)= NULL,
25: @Domain_User varchar(200),
26: --@Msg varchar(4000),
27: @LogLevel int= NULL,
28: --@DebugMsg varchar(4000),
29: @PageURL varchar(2000)= NULL,
30: @ClassName varchar(200)= NULL,
31: @MethodName varchar(200)= NULL,
32: @MethodNameGui varchar(4000)= NULL,
33: --@Ret int,
34: @LineNumber int= NULL,
35: @ProcedureName varchar(200)= NULL,
36: @ProcedureStep varchar(4000) = NULL,
37: @AlNameId int,
38: @DbNameId int
39:
40:
41: AS
42: BEGIN -- proc start
43: SET NOCOUNT ON;
44:
45: declare @ThisProcName varchar(200)
46: declare @ProcStep varchar(4000)
47:
48: set @ThisProcName = ( SELECT OBJECT_NAME(@@PROCID))
49: set @Msg = ''
50: set @DebugMsg = ''
51: set @ProcStep = ''
52:
53:
54: BEGIN TRY --begin try
55:
56: set @Msg = ISNULL( @msg , '')
57: set @DebugMsg = ISNULL ( @DebugMsg , '')
58: set @Ret = ISNULL ( @Ret , 1 )
59:
60: BEGIN TRAN
61:
62: INSERT INTO [dbo].[DebugLog] ([Time], [NiceTime], [Domain_User], [Msg], [LogLevel], [DebugMsg], [PageURL], [ClassName], [MethodName], [MethodNameGui], [Ret], [LineNumber], [ProcedureName], [ProcedureStep], [AlNameId], [DbNameId], [UserSessionId], [PageTypeId])
63: SELECT @Time, @NiceTime, @Domain_User, @Msg, @LogLevel, @DebugMsg, @PageURL, @ClassName, @MethodName, @MethodNameGui, @Ret, @LineNumber, @ProcedureName, @ProcedureStep, @AlNameId, @DbNameId, @UserSessionId, @PageTypeId
64:
65: -- Begin Return Select <- do not remove
66: SELECT [DebugLogId], [TimeStamp], [Time], [NiceTime], [Domain_User], [Msg], [LogLevel], [DebugMsg], [PageURL], [ClassName], [MethodName], [MethodNameGui], [Ret], [LineNumber], [ProcedureName], [ProcedureStep], [AlNameId], [DbNameId], [UserSessionId], [PageTypeId]
67: FROM [dbo].[DebugLog]
68: WHERE [DebugLogId] = SCOPE_IDENTITY()
69: -- End Return Select <- do not remove
70:
71: COMMIT
72:
73: set @Ret = 0
74:
75:
76: return @Ret
77:
78: END TRY --end try
79:
80: BEGIN CATCH
81: PRINT 'In CATCH block.
82: Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) + '
83: Error message: ' + ERROR_MESSAGE() + '
84: Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) + '
85: Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + '
86: XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10));
87:
88: set @Msg = 'Failed to retrieve Report '
89: set @DebugMsg = ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(10)) +
90: 'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' + CAST(ERROR_SEVERITY() AS varchar(10)) +
91: 'Error state: ' + CAST(ERROR_STATE() AS varchar(10)) + 'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(10))
92:
93: --record the error in the database
94: set @DebugMsg = @DebugMsg + @Msg
95:
96:
97: set @Ret = 1
98:
99: END CATCH
100:
101:
102: return @Ret
103: END --PROC END
104:
105: /*
106: <ProcName> procUtils_Debug </ProcName>
107: <doc>
108: Generates the Update Procedure on any table a
109: </doc> <use>
110: EXEC [dbo].[procUtils_GenerateUpdateProc] @TableName = N'Whatever'
111: </use> */
112:
113: GO
114:
115: GRANT EXECUTE ON [dbo].[procUtils_Debug] TO [public] AS [dbo]
116: GO
117:
118:
Posts
No comments:
Post a Comment
- the first minus - Comments have to be moderated because of the spammers
- the second minus - I am very lazy at moderating comments ... hardly find time ...
- the third minus - Short links are no good for security ...
- The REAL PLUS : Any critic and positive feedback is better than none, so your comments will be published sooner or later !!!!