Context_Info across databases

  • I have a stored procedure that works across databases.  So the "integration" database will take data from a temp table and insert that data into the "production" database.

    There are insert triggers on the table in the "production" database I want to bypass.  I'm setting context_info = 0x55555  right before the insert statement (in the integration database) but it does not seem to be bypassing the triggers.

    My question is does context_info work if my procedure is in one database but the actual inserting occurs in a different database?

    Thanks!

  • It should work I think.  But 0x55555 is an odd number of chars, so the value would actually get set to 0x055555.

    Try setting it to 0x555555, that should work more the way you want it to.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • Seems to work. Used two databases here:

    Results are :

    1 X

    2 NULL

     

    USE compare2
    GO
    CREATE TABLE TriggerTest (myid INT, mychar CHAR(1))
    GO
    CREATE TRIGGER tri_triggertest ON dbo.TriggerTest FOR INSERT
    AS
    BEGIN
    IF CONTEXT_INFO() = 0x1256698456
    PRINT 'caught'
    ELSE
    UPDATE dbo.TriggerTest
    SET mychar = 'X'
    FROM inserted i
    WHERE i.myid = dbo.TriggerTest.myid
    END
    GO
    USE compare1
    GO
    SET CONTEXT_INFO 0x000
    GO
    INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (1, NULL)
    GO
    SET CONTEXT_INFO 0x1256698456;
    GO
    SELECT CONTEXT_INFO();
    GO
    IF CONTEXT_INFO() = 0x1256698456
    SELECT 'true'
    GO
    INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (2, NULL)
    GO
    SELECT * FROM compare2.dbo.TriggerTest AS tt
  • Thanks for the answers!  I appreciate it.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply