Saturday, April 03, 2010

CONTEXT_INFO Connection wide variable

Need for a connection wide variable at the database level arose when I was creating an interface between Navision and MSCRM

The requirement was that when an Item was created in Navision it was pushed to CRM and when any changes were made in CRM they were reflected back in Navision we used triggers and things were fine but due to a two way interface it was required that the trigger should ignore any updates which were being made during the synch process and only the entries which were being made by the user on the front end should be captured for sync.

This is what we did. The procedure which was updating the CRM item table set the context_info as shown below

--context_info is used in the target tables triggers to ignore updates
--else it results in endless updates
declare @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = CAST ('SynchronizeNAV' as varbinary(128) )

In the trigger on the Item table in CRM we checked if the update/ insert was being made by the user or by the sync procedure as below

declare @sourceProc varchar(20)
select @sourceProc = cast ( context_info() as varchar)
set @sourceProc = ltrim( rtrim( @sourceProc ) )
if ( @sourceProc = 'SynchronizeCRM' )

one thing to ensure while using the context_info is that once the use is done it should be reset to null else it will continue to exist with the set value
thus at the end of the process we user

set context_info null

No comments: