Context Info Across Databases–#SQLNewBlogger

Does Context Info work across databases? This post shows it does.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Here are some hints to get started.

The Demo

Someone asked the question, would a trigger in another database see context info from a different database. I thought it should work, but decided to test it.

Here I’m going to create a table and trigger in database compare2. This is looking for a context value.

USE compare2
CREATE TABLE TriggerTest (myid INT, mychar CHAR(1))
CREATE TRIGGER tri_triggertest ON  dbo.TriggerTest FOR INSERT
     IF CONTEXT_INFO() = 0x1256698456
         PRINT 'caught'
         UPDATE dbo.TriggerTest
          SET mychar = 'X'
          FROM inserted i
          WHERE i.myid = dbo.TriggerTest.myid

Now, back in DB 1, I’m going to set CONTEXT_INFO and insert a value into the database. This should give me a result where the trigger updates the table. A “normal” action.

USE compare1
INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (1, NULL)

This does, as the table contains a 1 and X.

Now, same connection, let’s set the magic value for context and insert a row. Now the trigger should avoid the update, letting my bypass the normal action. This is what someone was trying to do.

SET CONTEXT_INFO 0x1256698456;  
INSERT compare2.dbo.TriggerTest (myid, mychar) VALUES (2, NULL)

When I look at the results, I have the “caught” message. The final results from the table are shown here:

2023-01-27 15_05_55-SQLQuery5.sql - ARISTOTLE_SQL2022.compare1 (ARISTOTLE_Steve (53))_ - Microsoft S

As you can see here, the context is with the connection, not the database. The database doesn’t matter for this value, it’s whether or not the connection that sets the context (the session really) is still alive when it accesses the other database.

SQL New Blogger

This was a quick test for me to answer a question and prove this to someone (and myself). I thought this would work, but I spent 5 minutes devising a test. It took me less than 10 minutes to put this post together.

This shows volunteerism (helping someone), testing ability, and diligence to prove something I suspected was true. I didn’t assume, I tested. Lots of employers love that.

You can raise your brand and be a SQL New Blogger like this, showing your knowledge.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.