Unused stock item cannot be deleted

Going into Stock Control, Stock Records and Delete Stock Item for an item which is not used.  Seeing "The item cannot be deleted while there is allocated stock, an order or return using the stock item, or build operations within the Bill of Materials Module".

Once these areas have been checked and nothing is found the next step would be:

1. Use SQL Profile and go to Delete Stock Item and then look at the profile trace to determine where the item is used and then take appropriate action in Sage 200.
2. Use a script to search for the item in the database to find out in which tables it exists and then take appropriate action in Sage 200.

Script to search for values in the database:

*/

declare @searchText varchar(255)
set @searchText = '%ADVFEDFAR%'
declare @datatype int
set @datatype = null  -- nchar

/*Set type of coloumn to search


null All types of strings*


-1 All types of numbers*


127 Bigint


173 binary


104 Bit


175 Char


61 datetime


106 decimal

62 Float

34 image

56 Int

60 money

239 nchar

99 ntext

108 numeric

231 nvarchar

59 Real

 

58 smalldatetime

 

52 smallint

 

122 smallmoney

 

98 sql_variant

 

231 sysname

 

35 text

 

189 timestamp

 

48 tinyint

 

36 uniqueidentifier

 

165 varbinary

 

167 varchar

 

241 xml

 

*/

 

 

 

declare @innerSQL varchar(4000)

 

 

 

DECLARE tables CURSOR READ_ONLY

 

FOR (

 

      select so.name as TableName, sc.name as ColName, ss.name as sch

 

      from syscolumns sc left join sysobjects so

 

      on sc.id = so.id

 

      left join sys.all_objects ao

 

      on so.id = ao.object_id

 

      left join sys.schemas ss

 

      on ao.schema_id = ss.schema_id

 

      where OBJECTPROPERTY(so.id, N'IsUserTable') = 1

 

            and (sc.xtype = @datatype

 

            or (@datatype is null and collation is not null)

 

            or (@datatype = -1 and sc.xprec>0))) -- to include text only

 

 

 

create table ##found ( tablename varchar(255), colname varchar(255), val nvarchar(4000) )

 

 

 

DECLARE @tablename varchar(255)

 

declare @colname varchar(255)

 

declare @sch varchar(255)

 

 

 

OPEN tables

 

 

 

FETCH NEXT FROM tables INTO @tablename, @colname, @sch

 

 

 

WHILE (not @@fetch_status = -1)

 

BEGIN

 

      IF (not @@fetch_status = -2)

 

      BEGIN

 

            --print 'table: ' + @sch + '.' + @tablename + ', column: '+ @colname

 

            set @innerSql = 'insert into ##found (tablename, colname, val) select '''

 

            + @sch + '.' + @tablename + ''', ''' + @colname + ''', Convert(nvarchar(4000),['

 

            + @colname + ']) from ' + @sch + '.[' +@tablename + '] where Convert(nvarchar(4000),['

 

            + @colname + ']) like ''' + @searchText + ''' '

 

 

 

            exec( @innerSql )

 

 

 

      END

 

      FETCH NEXT FROM tables INTO @tablename, @colname, @sch

 

END

 

 

 

CLOSE tables

 

 

 

DEALLOCATE tables

 

 

 

--Output result

 

select *

 

from ##found

 

drop table ##found