SQL Server 2008 Linked Server and CONTEXT_INFO

I'm trying to use the SQL Server 2008 Change Tracking feature. Once the feature is enabled, you can make use of the CHANGETABLE(... function to query the change tracking history that is kept internally by SQL Server, e.g.:

SELECT
CT.ID, CT.SYS_CHANGE_OPERATION,
CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT
FROM
CHANGETABLE(CHANGES dbo.CONTACT,20) AS CT

where the SYS_CHANGE_CONTEXT column records the CONTEXT_INFO() session value. This column is useful for auditing who changed what etc.

Some of the statements that change data are executed using four-part notation by a remote SQL Server that has the home server as a linked server e.g.:

INSERT INTO [home server].[db name].[dbo].[CONTACT](id) values(@id)

My problem is that the CONTEXT_INFO() as set on the remote server in the session executing the query does not get picked up in my home server change tracking, i.e. it doesn't look like the CONTEXT_INFO spans a distributed query. This means that the following will not result in the CONTEXT_INFO being logged on the home server change tracking.

-- I'm running on a remote server
WITH CHANGE_TRACKING_CONTEXT (0x1256698477) 
INSERT INTO [home server].[db name].[dbo].[CONTACT](id) values(@id)

Does anyone know whether this is a limitation or if there is a way to persist/communi开发者_高级运维cate CONTEXT_INFO across the distributed query?

Thanks

I was thinking about using Context_Info to audit changes (web app). but after doing some tests understood its not good idea. Because of connection pooling context_info was not working the way i desired.

Ended up with using GUID identifier associated with each logical session + table, where is stored session GUID and information related to session + each table stores that identifier in separate column. Not as easy to code as it would be with context_info()..

And as far as i understood from documentation, change tracking is not designed for audit purposes (think that is what you trying to do).