Syntax error 170 during index creation on SQL 2000

July 14, 2009 at 6:24 am

[] [] [] []

  1. XPRA: CL_ENH_BADI_SORTER_CALL syntax errorSymptom The termination occurs if a transport contains the deletion of an implementation of the BAdI BADI_SORTER and, at the same time, at least one BAdI definition with more than one implementation....
  2. DB2-z/OS:CCMS: DB13: DB2 Utilities SHRLEVEL SyntaxSymptom The DB13 job templates to call DB2 Utilities contain a wrong syntax for SHRLEVEL. The value of SHRLEVEL is accidentally enclosed in brackets. This prevents the fix from IBM APAR PK87122...

Symptom

You are running an upgrade or an import and you receive an error during index creation that shows the following error output:
In SE14 or in the activation log you see the error:
CREATE
INDEX [] ON [

(
)
WITH ( ONLINE=OFF )
Line 1: Incorrect syntax near '('.
In transaction SM21 you see the following output:
Database error 170 at EXE
> Line 1: Incorrect syntax near '('.

Reason and Prerequisites

This error happens only on SQL 2000.
For SAP Releases >= 701 this error output is expected because we do not support SQL 2000 on those releases.
For SAP releases 7.00 and lower this error is unwanted.

Solution

Remark:
Although it does not solve your current problem you should soon upgrade to MS SQL Server 2005.
To solve your problem for future activities please apply the corresponding correction instructions if the system is not locked (before any SPAM import). If your system is locked please apply the note right after your imports.
With Basis (not ABA) support package 16 this bug will be imported so that you have to re-aplly the note if you decide in SPAU to take over the SAP default again.
To fix any issue while an upgrade or SPAM import is running please go into MS SQL Server Query Analyzer (or SQL Management Studio), select the right database in the top line of Query Analyzer(e.g. for BWP) and execute the following two SQL statements. is the schema where your system runs in (e.g. bwp):
update .TATAF
set PRTEXT = ' '
where PRTEXT like '%WITH ( ONLINE=OFF )%';
update .TATAF
set PRTEXT = ' '
where PRTEXT like '%WITH ( FILLFACTOR=%)%';

delete from .TBATG where OBJECT = 'INDX' and
( FCT = 'CRE' or FCT = 'CNV' and INDNAME <> ' ' and SEVERITY = 'E' )
Please be sure to be in the right database. is either dbo or the name of your schema if you have a non-dbo schema system.
If the problem occurs during the upgrade (phase PARMVNT_SHD) then please execute additionally via MS SQL Server Query Analyzer (or SQL Management Studio)
update .[TATAF~] set PRTEXT = ‘ ‘
where PRTEXT like ‘%WITH ( ONLINE=OFF )%’;
update .[TATAF~]
set PRTEXT = ‘ ‘
where PRTEXT like ‘%WITH ( FILLFACTOR=%)%’;
delete from .[TBATG~] where OBJECT = ‘INDX’ and
( FCT = ‘CRE’ or FCT = ‘CNV’ and INDNAME <> ‘ ‘ and SEVERITY = ‘E’ )
( and database as before).
After your import or upgrade is finished please apply the note corrections. Then find any missing indexes with transaction DB02 (if TBATG entries were deleted you need to create the indexes now) and recreated them.
Now everything should work fine again.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

Leave a Comment