InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 17:10:38 GMT20http://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspxI 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:[code="sql"]USE Utility_DbGOCREATE 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 bytesBEGINRETURN ( 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 --FUNCTIONGO--Then in the set up code:USE data_dbDECLARE @context_info varbinary(128)SET @context_info = Utility_Db.dbo.SetContextInfo ( 10, 0xEE ) --starting byte, new valueSET CONTEXT_INFO @context_infoGO--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[/code]Tue, 19 Feb 2013 17:59:13 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspxOkie ,Got it.Now I have better understanding of @@spid and Context_Info better than before Thank you once again Lowell :)Tue, 19 Feb 2013 15:03:04 GMTsdennishttp://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspxEdit. Removed As hadn't seen whole thread. #stupidtabletTue, 19 Feb 2013 15:00:20 GMTmister.magoohttp://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspx@@spid is the unique Id SQL server assigned to the connection, which in this case, would be executing your stored procedure.if you run [b]exec sp_who2[/b] on your server, you'll see that most connections above 50 are various user connections; within that connection they may run multiple queries, but when someone disconnects, the spid context_info [i]related [/i]to the spid would be destroyed, and the spid could then be assigned/reused for a new connection.@@procid would be the id of the calling stored procedure, which is another option; you could do WHERE OBJECT_NAME(@@PROCID) = 'MyProc' [b]i think[/b], but that needs to be tested and verified...@@procid might return the id of the trigger instead!Tue, 19 Feb 2013 15:00:17 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspxThank you so muchBut I have a question(It might be dumb,I'm not sure. I'm a beginner)but the @@spid here returns the sessionid of the storeprocedure???(I was thinking since the current sessionID would be the execution of trigger)ALTER TRIGGER [dbo].[tuReferral_UpdateAudit] ON [dbo].[tReferral]FOR UPDATEAS-- Select the context informationDECLARE @sess VARBINARY(128), @var NVARCHAR(64)SET @sess = (SELECT context_info FROM master.dbo.sysprocesses WHERE spid = @@spid)SET @var = ISNULL(CAST(@sess AS NVARCHAR(64)),'')-- Keep Audit "Update" audit columns current (reflect data changes) for table: tReferralUPDATE tblSET tbl.UpdateLogin = SUSER_SNAME() ,tbl.UpdateApp = SUBSTRING(APP_NAME(),1,64) ,tbl.UpdateDateTime = CURRENT_TIMESTAMPFROM dbo.tReferral tbl JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM inserted) i ON tbl.ReferralID = i.ReferralID JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM deleted) d ON i.ReferralID = d.ReferralIDWHERE i.CSUM != d.CSUM AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc') and @var <> 'SKIPME'ThankYouSwethaTue, 19 Feb 2013 14:38:03 GMTsdennishttp://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspxhere's one idea: modify the trigger to also use CONTEXT_INFO as the update criteria[code]ALTER TRIGGER [dbo].[tuReferral_UpdateAudit] ON [dbo].[tReferral]FOR UPDATEAS-- Select the context informationDECLARE @sess VARBINARY(128), @var NVARCHAR(64)SET @sess = (SELECT context_info FROM master.dbo.sysprocesses WHERE spid = @@spid)SET @var = ISNULL(CAST(@sess AS NVARCHAR(64)),'')-- Keep Audit "Update" audit columns current (reflect data changes) for table: tReferralUPDATE tblSET tbl.UpdateLogin = SUSER_SNAME() ,tbl.UpdateApp = SUBSTRING(APP_NAME(),1,64) ,tbl.UpdateDateTime = CURRENT_TIMESTAMPFROM dbo.tReferral tbl JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM inserted) i ON tbl.ReferralID = i.ReferralID JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM deleted) d ON i.ReferralID = d.ReferralIDWHERE i.CSUM != d.CSUM AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc') and @var <> 'SKIPME'[/code]then in your stored procedure, you do this:[code]Create Procedure MyProcASBEGINDECLARE @var VARBINARY(128)SET @var = CAST(N'SKIPME' AS VARBINARY(128))SET CONTEXT_INFO @var--do stuff--disable the work aroundSET @var = CAST(N'' AS VARBINARY(128))SET CONTEXT_INFO @varEND --PROC[/code]the advantage of that is it is a per session variable,and not global like a row in a table would be.Tue, 19 Feb 2013 14:06:36 GMTLowellhttp://www.sqlservercentral.com/Forums/Topic1421837-392-1.aspxHiI have a trigger tuReferral_UpdateAudit on update of a table [dbo].[tReferral].The trigger is used for audit purposes where it updates 3 columnsbelow is the triggerALTER TRIGGER [dbo].[tuReferral_UpdateAudit] ON [dbo].[tReferral]FOR UPDATEAS-- Keep Audit "Update" audit columns current (reflect data changes) for table: tReferralUPDATE tblSET tbl.UpdateLogin = SUSER_SNAME() ,tbl.UpdateApp = SUBSTRING(APP_NAME(),1,64) ,tbl.UpdateDateTime = CURRENT_TIMESTAMPFROM dbo.tReferral tbl JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM inserted) i ON tbl.ReferralID = i.ReferralID JOIN (SELECT *,CHECKSUM(*) AS CSUM FROM deleted) d ON i.ReferralID = d.ReferralIDWHERE i.CSUM != d.CSUM AND SUSER_SNAME() NOT IN ('sa','PKX\prodsqlsvc')But I do not want this trigger to Update updatedatetime column when I'm updating the table [dbo].[tReferral] in one of my stored procedure "spGenerateBillingBatch" which has update query on treferral table .One solution I could think of was to disable the trigger and run this stored procedure and then update only the columns required and enable the trigger back.But My manager was not really happy about this method.Do we have any other solution for this ?ThanksSwethachTue, 19 Feb 2013 13:48:53 GMTsdennis