How to store DBCCresults ?

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 23, 2008 at 12:33 am

  #127546

  i need to store DBCC results in a table for audit

  for ex:

  Create table Sp_info (Eventtype varchar(50),parameters int,eventinfo nvarchar(max))

  insert into Sp_info

  dbcc inputbuffer(51)

  Can somebody help me ???

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

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  September 23, 2008 at 1:16 am

  #875446

  bhuvnesh.dogra (9/23/2008)

  ---

  i need to store DBCC results in a table for audit

  for ex:

  Create table Sp_info (Eventtype varchar(50),parameters int,eventinfo nvarchar(max))

  insert into Sp_info

  dbcc inputbuffer(51)

  Can somebody help me ???

  insert into Sp_info

  EXECUTE('dbcc inputbuffer(51)')

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 23, 2008 at 1:31 am

  #875453

  hi gila ,

  i need to pass spid as parameter

  so its giving error :

  declare @spid int,@lstr varchar(100)

  set @spid = 52

  set @lstr = 'insert into Sp_info dbcc inputbuffer( ' +cast( @spid as varchar) + ')'

  exec @lstr

  Msg 156, Level 15, State 1, Line 4

  Incorrect syntax near the keyword 'set'.

  please rectify error ???

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

• Gail Shaw

  SSC Guru

  Points: 1004494

  More actions

  September 23, 2008 at 1:43 am

  #875459

  bhuvnesh.dogra (9/23/2008)

  ---

  hi gila ,

  i need to pass spid as parameter

  so its giving error :

  declare @spid int,@lstr varchar(100)

  set @spid = 52

  set @lstr = 'insert into Sp_info dbcc inputbuffer( ' +cast( @spid as varchar) + ')'

  exec @lstr

  Msg 156, Level 15, State 1, Line 4

  Incorrect syntax near the keyword 'set'.

  please rectify error ???

  declare @spid int,@lstr varchar(100)

  set @spid = 52

  set @lstr = 'dbcc inputbuffer( ' +cast( @spid as varchar) + ')'

  insert into Sp_info

  exec (@lstr)

  You don't need to make the table column varchar(max). DBCC inputbuffer returns a max of 4000 characters

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  September 23, 2008 at 1:58 am

  #875466

  check this link

  http://qa.sqlservercentral.com/Forums/Topic567637-146-1.aspx

  have i provided right answer ????:)

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