Can't Delete GUID

rcostanzo's picture

I have an error shown below in my NS for a system that does not exist.  I have the GUID from the log file.  Is there a way to stop this error or delete the GUID from the database safely?

Name: D:\Program Files\Altiris\Notification Server\Logs\a.log
Priority: 1
Date: 10/1/2009 1:41:25 PM
Tick Count: 97347750
Host Name: NS Server
Process: w3wp.exe (2424)
Thread ID: 2492
Module: AltirisNativeHelper.dll
Source: Altiris.NS.ItemManagement.Item.GetName
Description: The specified GUID does not refer to a item (GUID: Deleted: computer name)

SK's picture

Use this query

You just have to modify this line:  set @GUID = rtrim(ltrim('enter guid here'))

Its resultset will be a delete statement that you run in a different window.  You then run this query again, and run the delete statement it creates if it finds any remining locations.  If it doesnt find anythinbg, it will return two rows.
______________________________________________________________________________

-- Temporary table to hold Table and Column names that are related to GUID
create table #TablesWithGuid
 (
 Tablename varchar(255),
 ColName varchar(255),
 Rows int
 )

-- Declare the variables
declare @TableName varchar(255),
  @ColName   varchar(255),
  @GUID   varchar(40),
  @xtype   int,
  @strSql  nvarchar(4000),
  @SqlResult  int

-- GUID that is being searched for.
set @GUID = rtrim(ltrim('enter guid here'))

-- Drop outside characters if longer than 36. (Eg. { and } characters)
if len(@GUID) = 38 and charindex('{',@GUID,1) = 1 and charindex('}',@GUID,1) = 38
 begin
   set @GUID = substring(@GUID, 2, 36)
 end
else if len(@GUID) <> 36
 begin
  select 'Invalid Guid Specified' as [Error]
  return
 end

-- Tables with a 'Guid'in the column name or having a Uniqueidentifier column. Full column name as well.
declare GuidTableCol cursor for
select distinct
 cast(so.name as varchar(255)) as 'Table Name',
 cast(co.name as varchar(255)) as 'Guid Column',
 co.xtype
from sysobjects so
inner join (
 select name, id, xtype
 from syscolumns
 where name like '%Guid%'
    or xtype = 36
 ) co on co.id = so.id
where so.xtype = 'U' --Only User Tables

open GuidTableCol
fetch next from GuidTableCol into @TableName, @ColName, @xtype

while @@FETCH_STATUS = 0
begin
 --Find which tables and columns have a matching Guid to the one we are searching for.
 set @SqlResult = 0

 --Set guid string to have brackets if the column is a varchar
 if @xtype = 167
 begin
    set @GUID = '{' + @GUID + '}'
 end

 -- Build the SQL Query string
 set @strSql = '
  select @Result = count(CAST([' + @ColName + '] as varchar(40)))
  from [' + @TableName + ']
  where cast([' + @ColName + '] as varchar(40)) = ''' + @GUID + ''''

 -- Execute the SQL Query string
 execute sp_executesql @strSql, N'@Result int out', @SqlResult out

 --If the result count is > 0 then add table, column, and count to #TablesWithGuid
 if @SqlResult > 0
  begin
   insert into #TablesWithGuid values (@TableName, @ColName, @SqlResult)
  end

 --Reset guid string
 if @xtype = 167
  begin
   set @GUID = substring(@GUID, 2, 36)
  end
 fetch next from GuidTableCol into @TableName, @ColName, @xtype
end

-- Close cursor and deallocate
close GuidTableCol
deallocate GuidTableCol

-- Display results
--select * from #TablesWithGuid

-- Delete script
 select ' declare @GUID uniqueidentifier'
 union
 select ' set @GUID = ' + Char(39) + cast(@GUID as nvarchar(36)) + Char(39)
 union
 select ''
 union
  select 'delete from ' + [Tablename] +  ' where ' + [ColName] + ' like ' + '@GUID'
 FROM #TablesWithGuid
 

-- Drop temp table
drop table #TablesWithGuid