Service Broker Eventdata() store xml

Question

I'm using service broker to send messages across multiple databases on many servers to one "central" database that will be recording any changes made(triggers/functs/procs/views/ and tables).  Tables is giving me a hard time because SMO is not compatible with CLR.  The way my project is working is that everytime a change is made, a ddl trigger will fire and service broker will send everything to a row in table in the central database using EVENTDATA().  From this table, it is being scripted out using a CLR and then written to file.  

For tables, I am not using SMO.  I have a UDF that will allow me to get the full script of the table after the ddl is fired. Now I'm having issues with sending this message from the "child" server to the "master" server.  My UDF returns the script of the table as an xml with the 'drops' and 'creates'.  I was thinking to store this xml into a variable within EVENTDATA() before sending it to the master server using service broker.  This way everything is still available(login time, name, etc)within the message after it has reached the master database.  How can this be done?

Also, are function calls allowed in a stored procedure?  

Thanks in advance for any assistance or comments.

Answer 1

Hi ML90

I'm not completely sure if I understand the description of your problem correctly, but perhaps what you really want to use, instead of a custom solution you're describing, are event notifications ?

PS.
What do you mean by saying that SMO is not compatible with CLR?

Thanks,
Pawel

Answer 2

My ScriptTable_fn returns script command as an xml element.  The problem occurs on the .modify().  I’m not sure how to use it properly.  I’ve looked around and could not find a solution.  Any help would be appreciated a lot.  Thanks



CREATE TRIGGER [tableTrg]

ON DATABASE

FOR CREATE_TABLE, ALTER_TABLE

AS

DECLARE @Body XML

DECLARE @tableName nvarchar(100)

DECLARE @tableTsqlCommand XML

DECLARE @script XML

SET @Body = eventdata() -- get all other attributes...login, datetime, etc.

SET @tableName = @Body.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') -- get name of table to be scripted

SET @script = (select dbo.ScriptTable_fn(@tableName))

SET @Body.modify('replace value of (/EVENT_INSTANCE/tsqlcommand) [1] with "(@script)"')

EXEC dbo.sendmsg_sp @Body

GO

 

Answer 3

As I said earlier, you seem to be trying to re-implement event notifications. Give event notifications a try and you should be able to get away without wrapping eventdata() in your custom message type.