I support the idea of using CONTEXT_INFO for this.
But I'd suggest limiting the use of it to a single, predefined byte location. And also not destroying any unused in CONTEXT_INFO when you set it: you never know, you might be wiping out something used by another process or trigger.
Since it's a pain to "implant"/stuff binary values to certain byte(s), I use a function to generate the new CONTEXT_INFO value with the byte(s) provided "stuffed" into the byte location specified:
USE Utility_Db
GO
CREATE FUNCTION [dbo].[SetContextInfo] (
@start int,
@value varbinary(128)
)
RETURNS varbinary(128)
AS
--SELECT dbo.SetContextInfo ( 10, 0xA1B2C3D4 ) --sample call that sets 4 bytes, starting at
--byte 10, other bytes are left unchanged; can set anywhere from 1 to 128 bytes
BEGIN
RETURN (
SELECT
COALESCE(SUBSTRING(CONTEXT_INFO(), 1, @start - 1), CAST(REPLICATE(0x00, @start - 1) AS varbinary(128))) +
@value +
COALESCE(SUBSTRING(CONTEXT_INFO(), @start + DATALENGTH(@value), 128 - @start - DATALENGTH(@value) + 1), 0x)
)
END --FUNCTION
GO
--Then in the set up code:
USE data_db
DECLARE @context_info varbinary(128)
SET @context_info = Utility_Db.dbo.SetContextInfo ( 10, 0xEE ) --starting byte, new value
SET CONTEXT_INFO @context_info
GO
--Finally, in the query that checks CONTEXT_INFO,
-- you can use a variable, as shown in the other code posted above;
-- or, if you prefer, you can just test CONTEXT_INFO directly:
...
and ISNULL(SUBSTRING(CONTEXT_INFO(), 10, 1), 0x00) <> 0xEE
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.