While upgrading some databases, we sometimes encounter checks stating that differences exists between UDT - UDF defined at the SQL-Server level and what is configured Inside Business One.
One of the "bad side" of these messages is that they don't tell what are the faulty objects, relying on the administrator to find them alone.
Basically checks have to be done from both side (what is defined in the SQL-Server and is not defined as UDT - UDF and the opposite) and regarding UDF can be done thru the following SQL code:
-- Search defined UDF which don't exists in system tables
select TableID, AliasID
from CUFD
WHERE NOT TableID + '|' + AliasID IN (
select so.name + '|' + RIGHT (sc.name, len (sc.name) - 2)
from syscolumns sc
inner join sysobjects so on sc.id = so.id
where sc.name like 'U__%' ESCAPE '_'
)
-- Search defined columns in SQL-Server which are not defined as UDT
select so.name TableName, sc.name ColumnName
from syscolumns sc
inner join sysobjects so on sc.id = so.id and so.xtype = 'U'
where sc.name like 'U__%' ESCAPE '_'
AND NOT so.name + '|' + RIGHT (sc.name, len (sc.name) - 2) IN (
select TableID + '|' + AliasID
from CUFD
)
After having checked the content of these messages, it can be usefull to update the datamodel and to remove the objects defined at one side and missing at the other one.
Regarding UDF, it can be done thru the following SQL code:
DECLARE @TableName sysname
DECLARE @ColumnName sysname
DECLARE @SQL varchar(MAX)
DECLARE cursTableCol CURSOR FOR
select so.name TableName, sc.name ColumnName
from syscolumns sc
inner join sysobjects so on sc.id = so.id and so.xtype = 'U'
where sc.name like 'U__%' ESCAPE '_'
AND NOT so.name + '|' + RIGHT (sc.name, len (sc.name) - 2) IN (
select TableID + '|' + AliasID
from CUFD
)
OPEN cursTableCol
FETCH NEXT FROM cursTableCol INTO @TableName, @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'ALTER TABLE [' + @TableName + '] DROP COLUMN [' + @ColumnName + ']'
EXEC (@SQL)
FETCH NEXT FROM cursTableCol INTO @TableName, @ColumnName
END
CLOSE cursTableCol
DEALLOCATE cursTableCol
I know that SAP forbid to directly modify the datamodel, but in this specific case, I just don't know any other method to correct a database...
Hope it will help somebody!
