Code to run for Databases compatible to 80 (or) sql server 2000

• fais

  Mr or Mrs. 500

  Points: 559

  More actions

  October 14, 2008 at 9:40 am

  #194568

  Hi all,

  I want to execute the below code only for those databases whose compatability levels are "80". I am struggling little bit. I know some one from here can help me out. Could you please add the piece of code on top which will enable this code to run only on those database whose compatibility level = 80 and databases that belong to sql server 2000 family.

  Thanks

  Code:

  SELECT req_spid AS 'spid',

  DB_NAME(rsc_dbid) AS 'Database',

  OBJECT_NAME(rsc_objid) AS 'Name',

  rsc_indid AS 'Index',

  rsc_text AS 'Description',

  ResourceType = CASE WHEN rsc_type = 1 THEN 'NULL Resource'

  WHEN rsc_type = 2 THEN 'Database'

  WHEN rsc_type = 3 THEN 'File'

  WHEN rsc_type = 4 THEN 'Index'

  WHEN rsc_type = 5 THEN 'Table'

  WHEN rsc_type = 6 THEN 'Page'

  WHEN rsc_type = 7 THEN 'Key'

  WHEN rsc_type = 8 THEN 'Extent'

  WHEN rsc_type = 9 THEN 'RID (Row ID)'

  WHEN rsc_type = 10 THEN 'Application'

  ELSE 'Unknown'

  END,

  Status = CASE

  WHEN req_status = 3 THEN 'Waiting'

  END,

  OwnerType = CASE WHEN req_ownertype = 1 THEN 'Transaction'

  WHEN req_ownertype = 2 THEN 'Cursor'

  WHEN req_ownertype = 3 THEN 'Session'

  WHEN req_ownertype = 4 THEN 'ExSession'

  ELSE 'Unknown'

  END,

  LockRequestMode = CASE WHEN req_mode = 0 THEN 'No access '

  WHEN req_mode = 1

  THEN 'Sch-S (Schema stability)'

  WHEN req_mode = 2

  THEN 'Sch-M (Schema modification)'

  WHEN req_mode = 3 THEN 'S (Shared)'

  WHEN req_mode = 4 THEN 'U (Update)'

  WHEN req_mode = 5 THEN 'X (Exclusive)'

  WHEN req_mode = 6 THEN 'IS (Intent Shared)'

  WHEN req_mode = 7 THEN 'IU (Intent Update)'

  WHEN req_mode = 8 THEN 'IX (Intent Exclusive)'

  WHEN req_mode = 9

  THEN 'SIU (Shared Intent Update)'

  WHEN req_mode = 10

  THEN 'SIX (Shared Intent Exclusive)'

  WHEN req_mode = 11

  THEN 'UIX (Update Intent Exclusive)'

  WHEN req_mode = 12 THEN 'BU. (Bulk operations)'

  WHEN req_mode = 13 THEN 'RangeS_S'

  WHEN req_mode = 14 THEN 'RangeS_U'

  WHEN req_mode = 15 THEN 'RangeI_N'

  WHEN req_mode = 16 THEN 'RangeI_S'

  WHEN req_mode = 17 THEN 'RangeI_U'

  WHEN req_mode = 18 THEN 'RangeI_X'

  WHEN req_mode = 19 THEN 'RangeX_S'

  WHEN req_mode = 20 THEN 'RangeX_U'

  WHEN req_mode = 21 THEN 'RangeX_X'

  ELSE 'Unknown'

  END

  FROM master.dbo.syslockinfo

  WHERE req_status = 3

  GO

  ,

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 14, 2008 at 10:09 am

  #884733

  something like htis might help:

  it detects the compatibility level...

  IF EXISTS(SELECT cmptlevel from master.dbo.sysdatabases WHERE master.dbo.sysdatabases.name =db_name() AND cmptlevel =70)

  begin

  print 'doing lev 70 stuff'

  end

  else IF EXISTS(SELECT cmptlevel from master.dbo.sysdatabases WHERE master.dbo.sysdatabases.name =db_name() AND cmptlevel =80)

  begin

  print 'doing lev 80 stuff'

  end

  else IF EXISTS(SELECT cmptlevel from master.dbo.sysdatabases WHERE master.dbo.sysdatabases.name =db_name() AND cmptlevel =90)

  begin

  print 'doing lev 90 stuff'

  end

  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!

• fais

  Mr or Mrs. 500

  Points: 559

  More actions

  October 14, 2008 at 11:45 am

  #884817

  Thanks a lot Lowell .