Error converting database ASA 9.0 to 16.0

[fvestjens]

I'm trying to upgrade a database from one of our customers from ASA version 9.0.2 build 2451 to SAP SQL Anywhere version 16.0 build 1915.

I'm using the unload database utility in Sybase Central.

I get the following error:

Error in file unloadold.sql at line 21 with sqlcode -121

***** SQL Error: Permission denied: you do not have permission to select from "SYSUSERPERM"

An error occurred while attempting to unload the database 'c:\database\....'.

Version 16 is the only version installed on the system.

Windows 2008 server R2 standard service pack 1 64-bit

Memory 12 Gb

Also tried to unload with version 12.0.1 build 3967 with the same error.

Even unloading only the structure gives the same error message.

In both situations logging on as DBA.

Any one any idea what might be wrong?

Kind regards,

Frank

[jeff_albion · Accepted solution]

Hi Frank,

When we're unloading a version 9 database, we're essentially starting the database up on the 'dbunlspt.exe' engine and running the 'scripts\unloadold.sql' file.

One of the first checks is against the SYS.SYSUSERPERM table:

select * from SYS.SYSOPTION o
  JOIN SYS.SYSUSERPERM u ON (o.user_id = u.user_id)
     where lower("option") = 'tsql_variables'
     and user_name = 'PUBLIC'

And it seems that you're really getting a SQLCODE -121 with a permission denied when you execute this.

In both situations logging on as DBA.

Can you login to the database on version 9 with the same DBA account, and run the above SELECT successfully?

Regards,

Jeff Albion

SAP Active Global Support

[fvestjens · Accepted solution]

Hi Jeff,

That's no problem.

Regards,

Frank

[jeff_albion · Accepted solution]

Hi Frank,

That's quite strange - it looks like it's a potential problem within the unload process then. We did have another report of a similar issue from a customer a while back, but it doesn't look like we were able to determine the absolute root cause as the issue was resolved once the software was re-installed.

Is there any other information you might be able to provide about this database about its origin and history? The "dbinfo" information might be useful, along with 'SELECT * FROM SYS.SYSHISTORY'. Does 'DBA' really have 'DBA' authority, or are there other permissions involved?

Can this database be provided to us for further analysis? If all of the table data is dropped, does the issue still reproduce?

Regards,

Jeff Albion

SAP Active Global Support

[fvestjens · Accepted solution]

Hi Jeff,

Sybase 9 was not installed on the machine where I tried to unload the database.

After installing Sybase 9 the problem did not disappear.

I still wasn't able to unload the database in Sybase 16 and 12.

I tried to unload the database into a new database from within Sybase 9 with sybase central and I got the following error after reloading statistics:

***** SQL error:Invalid parameter
The database 'DCFusionTereos' could not be unloaded.

So next I unloaded the database on disk. that was no problem.

I then create a new database in sybase 9 and then reload the script. That worked with only 2 errors.

then I used that new database to be able to unload with sybase 12 into a new database.

That also worked.

Is it possible to use the script from sybase 9 to create and reload the database in version 16?

Or should I use unload database from the new created database in Sybase 16?

Do you have a place where I can put the database? It's zipped about 3 Gb. If you want to have a closer look what is wrong with the database.

Does sybase 9 run on windows 2008 R2?

Regards,

Frank

[fvestjens]

I've finally found the issue.

If the database that is being converted contains a remote server and the conversion is not done on the machine having the remote connection.

In our setup it was a ODBC connection to another sybase database.

After removing the remote server I was able to convert the database from Sybase 9 to Sybase 17

Rergards,

Frank