Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Database was not updated since Temporary Tables Exist

Dear All

when i run SAP B1 on my system it seems an error

"Database was not updated since Temporary Tables Exist"

Can any boby help me in this regards

Sohail Anwar Ali

Former Member
replied

Sohail,

is this problem solved ? let us know. here is an official solution (from SAP Note) :

"Temporary tables found in database" or upgrade failure

Symptom

1. In an attempt to upgrade SAP Business One database, the following

error message is displayed: "Temporary tables found in database"

1. Upgrade process is completed but version no. in "Choose Company"

window was not updated and database is inaccessible.

Other terms

Upgrade, Database Upgrade, Temporary Tables, Error Message,

Log on, Log on Failed

Reason and Prerequisites

Application error

Solution

During the database upgrade process, each SAP Business One table is

re-created.

The tables are re-created as follows:

1. The table is renamed. The new name is Temp_table (e.g. Temp_OINV).

2. A new table is created (e.g. OINV).

3. Records from the Temp_table are copied into the new table.

4. The temporary table is dropped.

In case problems occur during the upgrade, the system might fail to

drop the temporary tables and/or copy records from temporary tables to

new tables and as a result, the error message: "Temporary tables found

in database" is displayed and the database upgrade is blocked.

The most common cause for this problem is changes in the original table

structure which were made by the user (Field Size, Field Type etc.).

Examples:

The size of the field "Bank Name" in the table ODSC (Banks Codes) is 32

characters long.

The user had changed the size to 40 characters.

The user enters values which are longer than 32 characters into the

field "Bank Name".

After an upgrade, the table is re-created and the field "Bank Name" is

changed back to its original size (32 characters).

The results are:

1. SAP Business One fails to copy values which are longer than 32

characters from the temporary table into the new table.

2. SAP Business One fails to drop the temporary table.

3. SAP Business One documents the failure details into the table CULG

(in the company database).

4. SAP Business One creates a log file - "UpgradeLog". This log file is

stored in SAP Business One's folder, where the database upgrade

process took place.

Additional causes for upgrade failure:

5. The data file or the log file growth is restricted.

Error message in upgrade log file is:

"The log file for database 'xxx' is full.

Back up the transaction log for the database to free up some log space."

In order to indicate it do the steps below:

Open the enterprise manager.

Right click the database.

Select properties.

Select Data files

Under "Maximum file size" select "unrestricted file growth".

Select Transaction Log file tab page

Under "Maximum file size" select "unrestricted file growth".

in order to resolve the problem.Contact your support center

Attach the log file "UpgradeLog" to your message.

Note!

In versions 6.01 - 6.20.36 the database upgrade blocking, in case

temporary tables are found, is missing.

Fixed in version 6.20.36.07.

Note!

In versions prior to 6.5, SAP Business One used to generate the

"temporary table" alert for each table which starts with "Temp".

The problem was solved in version 6.5, Major Release.

Queries for analyzing upgrade failur

Data base compare

The query below can be used to analyze changes in SAP Business One database tables structure. The way to indicate any structure changes can be achieved by comparing user database to a new created SAP Business database.

In case an upgrade failur occucred follow the steps below:

1. Backup the company database which failed to upgrade.

2. Name it "company_name_failed".

3. Restore into the company database (step 1) the backup before upgrade.

4. Create new SAP Business One database with same version as the restored

database. Name it "DB1".

Since your SAP Business One server is already upgraded,

create the "DB1" database on a different system (same SAP Business One

release as the restored database - step 3)or ask support to provide you

a backup of an empty database with same release.

1. Run the query below to indicate changes in SAP Business One tables

default structure. Send results to support.

/* This query

USE [DB1]

GO

SET NOCOUNT ON

CREATE TABLE #TEMP_COMP

(TableName varchar (5),

Alias varchar (20),

TypeBef varchar (20),

TypeAft varchar (20),

StatusBef varchar (10),

StatusAft varchar (10)

)

DECLARE

@table SYSNAME,

@column SYSNAME,

@type tinyint,

@length smallint,

@outtype varchar (20),

@status int,

@outstatus varchar (10)

declare Currs CURSOR

for

select name

From sysobjects where type = 'U' and len(name)= 4 and

name not like '@%' and uid = 1

Open Currs

Fetch next From Currs

Into @table

While ( @@Fetch_Status = 0)

Begin

declare Currs1 CURSOR

for

select syscolumns.name,syscolumns.xtype,syscolumns.prec from syscolumns inner Join

sysobjects on sysobjects.id = syscolumns.id

where sysobjects.name = @table

Open Currs1

Fetch next From Currs1

Into @column,@type,@length

While ( @@Fetch_Status = 0)

Begin

SET @outtype =

CASE

WHEN @type = 56 THEN 'int'

WHEN @type = 52 THEN 'smallint'

WHEN @type = 48 THEN 'tinylint'

WHEN @type = 62 THEN 'float'

WHEN @type = 108 THEN 'numeric'

WHEN @type = 61 THEN 'datetime'

WHEN @type = 34 THEN 'image'

WHEN @type = 35 THEN 'text'

WHEN @type = 99 THEN 'ntext'

WHEN @type = 175 THEN 'char(' + cast(@length as varchar (6)) + ')'

WHEN @type = 239 THEN 'nchar(' + cast(@length as varchar (6)) + ')'

WHEN @type = 167 THEN 'varchar(' + cast(@length as varchar (6)) + ')'

WHEN @type = 231 THEN 'nvarchar(' + cast(@length as varchar (6)) + ')'

ELSE 'xxx'+ cast (@type as varchar (6))

END

select @status = (select Top 1 sysindexes.status

from sysindexkeys,syscolumns,sysobjects,sysindexes

where sysindexkeys.colid = syscolumns.colid and

sysindexkeys.id = sysobjects.id and

syscolumns.id = sysobjects.id

and sysindexes.id = sysobjects.id

and sysindexes.indid = sysindexkeys.indid

and sysindexkeys.keyno = 1

and sysobjects.name = @table

and syscolumns.name = @column)

SET @outstatus =

CASE

WHEN @status = 2 THEN 'unique'

WHEN @status = 18450 THEN 'primary'

ELSE 'nostatus'

END

INSERT INTO #TEMP_COMP (TableName,Alias,TypeBef,StatusBef)

SELECT @table,@column,@outtype,@outstatus

Fetch next From Currs1

Into @column,@type,@length

End

Close Currs1

Deallocate Currs1

Fetch next From Currs

Into @table

End

Close Currs

Deallocate Currs

-


USE [DB2]

GO

DECLARE

@table SYSNAME,

@column SYSNAME,

@type tinyint,

@length smallint,

@outtype varchar (20),

@status int,

@outstatus varchar (10)

declare Currs CURSOR

for

select name

From sysobjects where type = 'U' and len(name)= 4 and

name not like '@%' and uid = 1

Open Currs

Fetch next From Currs

Into @table

While ( @@Fetch_Status = 0)

Begin

declare Currs1 CURSOR

for

select syscolumns.name,syscolumns.xtype,syscolumns.prec from syscolumns inner Join

sysobjects on sysobjects.id = syscolumns.id

where sysobjects.name = @table

Open Currs1

Fetch next From Currs1

Into @column,@type,@length

While ( @@Fetch_Status = 0)

Begin

SET @outtype =

CASE

WHEN @type = 56 THEN 'int'

WHEN @type = 52 THEN 'smallint'

WHEN @type = 48 THEN 'tinylint'

WHEN @type = 62 THEN 'float'

WHEN @type = 108 THEN 'numeric'

WHEN @type = 61 THEN 'datetime'

WHEN @type = 34 THEN 'image'

WHEN @type = 35 THEN 'text'

WHEN @type = 99 THEN 'ntext'

WHEN @type = 175 THEN 'char(' + cast(@length as varchar (6)) + ')'

WHEN @type = 239 THEN 'nchar(' + cast(@length as varchar (6)) + ')'

WHEN @type = 167 THEN 'varchar(' + cast(@length as varchar (6)) + ')'

WHEN @type = 231 THEN 'nvarchar(' + cast(@length as varchar (6)) + ')'

ELSE 'xxx'+ cast (@type as varchar (6))

END

select @status = (select Top 1 sysindexes.status

from sysindexkeys,syscolumns,sysobjects,sysindexes

where sysindexkeys.colid = syscolumns.colid and

sysindexkeys.id = sysobjects.id and

syscolumns.id = sysobjects.id

and sysindexes.id = sysobjects.id

and sysindexes.indid = sysindexkeys.indid

and sysindexkeys.keyno = 1

and sysobjects.name = @table

and syscolumns.name = @column)

SET @outstatus =

CASE

WHEN @status = 2 THEN 'unique'

WHEN @status = 18450 THEN 'primary'

ELSE 'nostatus'

END

UPDATE #TEMP_COMP

SET TypeAft = @outtype,StatusAft = @outstatus

WHERE #TEMP_COMP.TableName = @table AND

#TEMP_COMP.Alias = @column

Fetch next From Currs1

Into @column,@type,@length

End

Close Currs1

Deallocate Currs1

Fetch next From Currs

Into @table

End

Close Currs

Deallocate Currs

-


SELECT * from #TEMP_COMP

WHERE

(TypeBef <> TypeAft OR

TypeBef like 'xxx%' OR

TypeAft like 'xxx%' OR

StatusBef <> StatusAft)

ORDER BY TableName,Alias

DROP TABLE #TEMP_COMP

SET NOCOUNT OFF

Alert regarding Temporary tables might be generated as a result of atable corruption.

Usually the result is:

Temporary table exists

No error message in CULG

In order to indicate the problem, do the steps below:

Identify the temporary tables in the database. The tables prefix is

Temp_

For each temporary table which was indicated, run from the

Query Analyzer the SQL function below:

DBCC CHECKTABLE ('Table_Name')

e.g.:In case you indicated a temporary table Temp_RITM, replace the

('Table_Name') in the query with ('RITM').

This SQL function is indicating data page integrity problems in SQLtables.

In case any errors found, SQL will generate the error message:

error: Object ID 1913825930...,

to solve the problem, contact your Data base administrator.

For testing the whole database tables, run the function below:

DBCC CHECKDB ('DB_Name')

Replace ('DB_Name') in the query with the database name.

Rgds,

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question