We have multiple applications writing to our MS SQL 2008 R2 database and there is a need to capture information about these applications during these writes. The database code (triggers, stored procedures etc) are under my control and can be modified but not the application code.
I am leaning towards using CONTEXT_INFO to store application specific information and retrieving it in the triggers. The 1M$ question is "How can I get the applications to set the CONTEXT_INFO if they are loath to change application code ?".
I DO have access to the connection strings that these applications use as these reside in config files.
I was not able to find any properties on the connection string that I can use to set CONTEXT_INFO (or execute any TSQL at initialization). Basically I was hoping to simulate the behaviour of SQLCMD -q option e.g SQLCMD -q "SET CONTEXT_INFO 0x1244".
Is there any way to simulate this behavior in ADO.NET via configuration ? Or a different way to do this ? Any pointers appreciated.
If there is no way this can be done, I can take the message back and tell "management" that application code has to be changed.
APP_NAME().