Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Short Version: Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected?

Long Version I have a customized synchronization routine that moves data back and forth between dis-similar database schemas.

When this process grabs a modified record from Database A, it needs to transform it into a record that goes into Database B. The database are radically different, but share some of the same data such as user accounts and user activity (however even these tables are structurally different).

When data is modified in one of the pertinent tables, a trigger fires which writes the PK of that record to a "sync" table. This "sync" table is monitored by a process (a stored proc) which will grab the PK's in sequence, and copy over the related data from database A to database B, making transformations as necessary.

Both databases have triggers that fire and copy the PK to the sync table, however these triggers must ignore the sync process itself so as not to enter into "endless" loop (or less, depending on nesting limits).

In SQL 2005 and up, I use the following code in the Sync process to identify itself:

SET CONTEXT_INFO 0xHexValueOfProcName

Each trigger has the following code at the beginning, to see if the process that modified the data is the sync process itself:

IF (CONTEXT_INFO() = 0xHexValueOfProcName)
BEGIN
--  print '## Process Sync Queue detected. This trigger is exiting! ##'
  return
END

This system works great, keep chugging along, keeps the data in sync. The problem now however is that a SQL2000 server wants to join the party.

Does anyone know of a way --inside a SQL 2000 trigger-- of detecting which process modified the data, and exiting the trigger if a particular process is detected?

Thanks guys!

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

SET CONTEXT_INFO exists for SQL Server 2000 too.

However, you have to query sysprocesses.context_info to read it back. CONTEXT_INFO() was new for SQL Server 2005.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.