cdc tables - SQL Server Forums

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

SQL Server Administration (2008)

cdc tables

Author

Topic

lilinikco
Starting Member

28 Posts

Posted - 2010-11-01 : 06:29:36

hi all.
we have one web application and one database that are on two diffrent server.now we want to store changed data that occure
with insert.update&ddelete,we can store this with cdc tables easily,but we need to store client's IP and computer name of
client which do this changes.what way suggest?what way we do?

lili@@

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-11-12 : 16:44:52

modify the trigger to use master..sysprocesses for the @@spid and take out the information...also you can use host_name() and maybe that will be enough for your requirement?

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

tclaw
Starting Member

5 Posts

Posted - 2010-11-15 : 11:56:12

This is the insert I utilize to track all DDL changes to the database. You should be able to extract what you need from here to include the data you need:


CREATE TRIGGER track_ddl_changes
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS
INSERT track_ddl_changes 
   (date, nt_user, sql_user, hostname, event, tsql) 
   VALUES 
   (GETDATE(), 
   CONVERT(nvarchar(100), NT_CLIENT() ),
   CONVERT(nvarchar(100), CURRENT_USER), 
   CONVERT(nvarchar(100), HOST_NAME() ),
   EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
   EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(8000)') ) ;
GO

Hope this helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources