Server TRIGGER to insert data into Linked server table

• @fracionero

  Newbie

  Points: 0

  More actions

  May 8, 2008 at 7:10 am

  #190179

  Hi All,

  For audit reasons I need to save information about some database events (CREATE, ALTER, DROP) into a central table, for that I've deployed a Server Trigger that rec the information in a Linked Server table, but fails.

  I've checked the trigger using a local table instead a linked server table, and runs correct, but when I change the table source by linked server table fails again.

  Do you have any idea to solve?

  Thanks in advance.

  This is the Server trigger

  /****** Object: DdlTrigger [ServerDBEvent] Script Date: 05/08/2008 15:00:30 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TRIGGER [ServerDBEvent]

  ON ALL SERVER

  for CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE

  AS

  SET NOCOUNT ON

  DECLARE @data AS xml

  DECLARE @EventType AS varchar(25)

  DECLARE @PostTime AS varchar(25)

  DECLARE @ServerName AS varchar(25)

  DECLARE @DBName AS varchar(150)

  DECLARE @login AS varchar(50)

  DECLARE @TSQLCommand AS varchar(MAX)

  DECLARE @idnt as int

  DECLARE @msg as varchar(50)

  DECLARE @comp as bit

  SET @msg='completed'

  SET @data = EVENTDATA()

  SELECT @EventType =

  @data.value('(/EVENT_INSTANCE/EventType)[1]','varchar(25)')

  , @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]','varchar(25)')

  , @ServerName =

  @data.value('(/EVENT_INSTANCE/ServerName)[1]','varchar(25)')

  , @login = @data.value('(/EVENT_INSTANCE/LoginName)[1]','varchar(25)')

  , @DBName =

  @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','varchar(25)')

  , @TSQLCommand =

  @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','varchar(max)')

  if @EventType='DROP_DATABASE'

  begin

  raiserror ('Only DBA team can drop any database, please contact with us to do.

  If you are a DBA member you must first disable ServerDBevent trigger to drop a database',16,1)

  set @comp=0

  rollback

  end

  else

  begin

  Print 'Hi ' + @login + ', your has been processed and its status is: ' + @msg

  set @comp=1

  end

  INSERT INTO [SQLSRV1001\AUDIT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG]

  ([ENV_SRV_INS_DB_CHG_Server]

  ,[ENV_SRV_INS_DB_CHG_Name]

  ,[ENV_SRV_INS_DB_CHG_EventType]

  ,[ENV_SRV_INS_DB_CHG_PostTime]

  ,[ENV_SRV_INS_DB_CHG_Login]

  ,[ENV_SRV_INS_DB_CHG_TSQLCommand]

  ,[ENV_SRV_INS_DB_CHG_Completed])

  values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)

  RETURN

  SET NOCOUNT OFF

  GO

  SET ANSI_NULLS OFF

  GO

  SET QUOTED_IDENTIFIER OFF

  GO

  ENABLE TRIGGER [ServerDBEvent] ON ALL SERVER

  Francisco Racionero
  twitter: @fracionero

• MarkusB

  Newbie

  Points: 0

  More actions

  May 8, 2008 at 7:22 am

  #812400

  Trigger actions are limted to the local server.

  Replace you're ddl trigger with an event notification. You can then send message to anther server using service broker.

  [font="Verdana"]Markus Bohse[/font]

• Johan Bijnens

  Newbie

  Points: 0

  More actions

  May 8, 2008 at 7:23 am

  #812404

  -We record this at the sqlinstance itself and only grant insert access to that particular table of our "admin" db.

  - This just to avoid having issues if your central audit server is down, ..

  - We gather the data to a central server every week.

  [remove this]

  If you still want to use the linked server solution (and hook everything up to your audit server):

  - Did you create the linked server ?

  - what security did you setup to be used with the linked server ?

  [/remove this]

  As MarkusB stated, triggers only work local.

  If you still try to create a trigger to a remote db, you'll get an error like:

  Msg 8624, Level 16, State 1, Procedure ddlDatabaseTriggerLog, Line 32

  Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

  In this case I've setup a ddl-trigger to insert into a remote db, made sure all connections were handled by sql-userid RmtAudit which has select/insert/viewdefinition rights for the target table;

  Classic insert works, as long as you don't use an XML datatype (not supported for remote queries ??!!?? )

  Once encapsulated in a ddl-trigger, I receive Msg 8624.

  Service broker may be an option, but may fail for the same reasons as you are wanting to set this system up using a central server.

  i.e. avoid local tampering :unsure:

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me