SQL stored procedure change logging trigger not saving all text

Question

I created a stored procedure to log changes made to stored procedures. The issue is that the larger ones aren't being saved in their entirety and are being chopped off.

I need to be able to save the entire procedure text and it is also used as a means of reverting the a previous revision.

The trigger:

/****** Object:  DdlTrigger [StoredProcUpdateInsert]    Script Date: 12/05/2015 14:05:05 ******/
DROP TRIGGER [StoredProcUpdateInsert] ON DATABASE
GO

/****** Object:  DdlTrigger [StoredProcUpdateInsert]    Script Date: 12/05/2015 14:05:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE TRIGGER [StoredProcUpdateInsert]
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE

AS
BEGIN
SET NOCOUNT ON


DECLARE @data XML
SET @data=EVENTDATA()

INSERT INTO dbo.ProcedureChanges
        ( ProcName ,
          ProcText ,
          ModifiedBy ,
          DateTimeLastUpdated
        )
VALUES  ( @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') , -- ProcName - nvarchar(450)
          (SELECT TOP 1 text FROM syscomments WHERE id=OBJECT_ID(@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'))) , -- ProcText - nvarchar(max)
          @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') , -- ModifiedBy - nvarchar(250)
          GETDATE()  -- DateTimeLastUpdated - datetime
        )



END 


GO

SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

ENABLE TRIGGER [StoredProcUpdateInsert] ON DATABASE
GO

score 2 · Accepted Answer · answered May 12 '15 at 13:18

2

According to this article: https://msdn.microsoft.com/en-us/library/ms186293.aspx you shouldn't use syscommentsand instead should use sys.sqlmodules

The definition of syscomments.textfrom the article above says it's defined as nvarchar(4000). Presumably this means any DDL longer than 4000 characters is truncated and therefore you're only getting the first 4000 characters in your log table.

I haven't tested this but you could try:

INSERT INTO dbo.ProcedureChanges
        ( ProcName ,
          ProcText ,
          ModifiedBy ,
          DateTimeLastUpdated
        )
VALUES  ( @data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)') , -- ProcName - nvarchar(450)
          (SELECT Definition FROM sys.sql_modules WHERE object_id=OBJECT_ID(@data.value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)'))) , -- ProcText - nvarchar(max)
          @data.value('(/EVENT_INSTANCE/LoginName)[1]','nvarchar(max)') , -- ModifiedBy - nvarchar(250)
          GETDATE()  -- DateTimeLastUpdated - datetime
        )

answered May 12 '15 at 13:18

Tobsey

3,390
14
24

I've just tried this and unfortunately it provides exactly the same result. – connersz May 12 '15 at 13:51
@connersz I've just run this on a server and got the full definition which was 5581 characters long. can you confirm how much of the SP is being logged. See how long it is. If it is 8192 then it's probably Management Studion that is truncating the result, but the full SP is being stored. See here: http://stackoverflow.com/questions/952247/sql-server-truncation-and-8192-limitation – Tobsey May 12 '15 at 14:28
I have getting 718 rows out of a total 1837. – connersz May 12 '15 at 14:39
OK, seems it was truncated. – connersz May 12 '15 at 14:55

SQL stored procedure change logging trigger not saving all text

1 Answers1