Unable to use OBJECT_NAME() and context_info() together

• samalex

  SSCertifiable

  Points: 5429

  More actions

  November 23, 2010 at 10:45 am

  #226923

  Hi,

  I'm trying to save the currently running stored procedure to context_info() then pull this out in a Trigger so I can save the Procedure name to a log table. Problem though is no matter what I try it only returns the first character of the Trigger name.

  Here's what I have thus far for testing:

  Create PROCEDURE uspTest1 AS

  BEGIN

  DECLARE @ProcedureName nvarchar(30) = OBJECT_NAME(@@PROCID)

  declare @context_info varbinary(30)

  set @context_info = cast(@ProcedureName as varbinary(30))

  set context_info @context_info

  SELECT @ProcedureName as ProcedureName

  END

  Go

  EXEC uspTest1

  select cast(context_info() as varchar(30))

  And here's the output:

  ProcedureName

  uspTest1

  (No column name)

  u

  The SELECT @ProcedureName code returns the full procedure name 'uspTest1' but the second Select from context_info() only returns 'u'. Any suggestions on how to get the full Procedure name saved in Context_Info?

  Thanks for any suggestions -- Take care,

  Sam

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 23, 2010 at 10:57 am

  #1254611

  ok, i compared my snippets to your code, and the only thing i have that is different is that my code always gets the value of the context_info from a system table, my example sonly assign values to context_info, but never tried to read them back.

  the select i added after your code seems to return what you are after:

  ALTER PROCEDURE uspTest1 AS

  BEGIN

  DECLARE @ProcedureName nvarchar(30) = OBJECT_NAME(@@PROCID)

  declare @context_info varbinary(30)

  set @context_info = cast(@ProcedureName as varbinary(30))

  set context_info @context_info

  SELECT @ProcedureName as ProcedureName

  END

  Go

  EXEC uspTest1

  select cast(context_info() as varchar(128))

  SELECT CONVERT(nvarchar(64),context_info) FROM master.dbo.sysprocesses

  WHERE spid = @@spid

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!