How to insert Binary values

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  October 9, 2008 at 6:26 am

  #92648

  hi

  IF EXISTS (SELECT * FROM sysobjects WHERE type = 'U' AND name = 'SYNC_OBJECT_SEND')

  DROP TABLE [SYNC_OBJECT_SEND]

  GO

  CREATE TABLE [dbo].[SYNC_OBJECT_SEND](

  [object_text] [varchar](max) NOT NULL,

  [object_name] [varchar](200) NOT NULL ,

  [eventtype] [varchar](max) NOT NULL,

  [Context_Info] [varchar](7)NOT NULL

  ) ON [PRIMARY]

  Go

  set context_info 0x55555

  GO

  INSERT INTO SYNC_OBJECT_SEND (object_text,object_name,EventType,Context_Info)

  VALUES ('select ''bhv8hv8sd''','sp_test','ALTER_PROCEDURE',context_info)

  -----------------------------------

  The problem is that i m inserting "context_info" value into insert command but whenever i do that

  it is taking some unrecognized format (FF[][][][]) something

  i thought there may be problem of casting into varchar but it also couldn't work .

  Please suggest

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  October 9, 2008 at 9:18 pm

  #883221

  Two things

  1. to retrieve the context_info value you assigned, you should use the CONTEXT_INFO() function.

  2. Context_info is a binary value. you should use a VARBINARY(128) column to store it.

  Here is an example:

  DECLARE @t TABLE (info VARBINARY(128))

  SET CONTEXT_INFO 0x55555

  INSERT INTO @t (info) SELECT CONTEXT_INFO()

  SELECT * FROM @t

  /*

  info

  ---------------------------------

  0x0555550000000000000000000000...

  */

  .