commandText get from eventdata() function not work

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

commandText get from eventdata() function not work

Author

Topic

ranjeetsingh_6
Posting Yak Master

125 Posts

Posted - 2010-08-20 : 05:49:00

Hi,

I am maintaing a log table by create a trigger on Database lavel and save all sql command text into log table.
But when i execute command text that was save into log table then it not execute proper and either give error or not any output.
my senario is below

---Create a table
CREATE TABLE [dbo].[tbl_logtable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Version] [nvarchar](100) NULL,
[ModifyDate] [datetime] NULL,
[ModifyBy] [nvarchar](100) NULL,
[ServerName] [nvarchar](200) NULL,
[CommandText] [nvarchar](max) NULL,
CONSTRAINT [PK_logtable] PRIMARY KEY CLUSTERED
(
[ID] ASC
))

--create a trigger----will track for all DDL command on database
alter TRIGGER [trdblog]
ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS
AS
SET NOCOUNT ON
DECLARE @xEvent XML
SET @xEvent = eventdata() --–capture eventdata regarding SQL statement user have fired

declare @version varchar(50)
set @version='1.0'

INSERT INTO tbl_logtable VALUES(@version,getdate(),CONVERT(NVARCHAR(100),
@xEvent.query('data(/EVENT_INSTANCE/UserName)')),
CONVERT(NVARCHAR(200), @xEvent.query('data(/EVENT_INSTANCE/ServerName)')),
replace(CONVERT(NVARCHAR(MAX), @xEvent.value(N'(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')),'#x0D;',char(13))

)

----create a procedure---------------------------------
create procedure sp_Test
as
begin

select 'Store into table'

end
-- alter procedure add some comment in this
alter procedure sp_Test
as
begin
/*
exec sp_Test
*/
---start to maintain released version into table
select 'Store into table'

end

--execute procedure
exec sp_Test

--it work fine and generate output as accepted

--output

'Store into table'

-----select sql command from log table------

select commandtext from tbl_logtable

-----output ------------------

alter procedure sp_Test as begin /* exec sp_Test */ ---start to maintain released version into table tbl_RelesedSchema table select 'Store into table' end

--when i execute the just above alter sp_Test command line then it give me error that

Msg 102, Level 15, State 1, Procedure sp_Test, Line 1
Incorrect syntax near 'begin'.

why my alter command not execute proper while it was save into log table "tbl_logtable" with same by above created trigger (trdblog)

I read the solution for this then find some unicode related probleem that can solve to place N before a string.

My main probleem is how i get sql command in well formate from eventdata() function and save sql command into table "tbl_logtable" by trigger "trdblog" so that when i execte the sql command it execte proper.
mainly i face problem when sql command have any comment or dynamic
statement.

Thanks & Regards

Ranjeet

Ranjeet Kumar Singh

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-08-20 : 09:38:28

were you able to retrieve the event data without saving it to the table first? use the value property and [1]

try this...


declare @EventType varchar(100)
declare @SchemaName varchar(100)
declare @ObjectName varchar(100)
declare @ObjectType varchar(100)
declare @objectDefinition varchar(max)
declare @login varchar(300)
declare @spid bigint
declare @databaseName varchar(300)

SELECT 
 @EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')  
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')  
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)') 
,@databaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(300)') 
,@objectDefinition=EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
,@login=EVENTDATA().value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(300)')
,@spid=EVENTDATA().value('(/EVENT_INSTANCE/SPID)[1]','bigint')

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources