Fail to change column type in Sybase Central SQLAnywhwere database

[Former Member]

I have a table called actionParameter in the consolidated SQLAnywhere database. Within the table is a primary key column called actionParameterId. The current column type of the actionParameterId is uniqueidentifierstr.

When I look at the values of the actionParameterId column, I see strange characters. I think this is due to the fact it is a uniqueidentifierstr and not a uniqueidentifier. I want to change it to a uniqueidentifier so badly... because the actionParameterId column type in the remote database is also of type uniqueidentifier.

When I try to edit the column type in the Sybase Central I get the following error not allowing me to:

The primary key 'actionParameterId - actionParameter (mobielcmp)' could not be deleted from the database. This operation is only allowed within a synchronization schema change [Sybase][ODBC Driver][SQL Anywhere]This operation is only allowed within a synchronization schema change SQLCODE: -1329 SQLSTATE: HY000 SQL Statement: ALTER TABLE "mobielcmp"."actionParameter" DROP PRIMARY KEY

What does it mean only allowed in a synchronization schema change? Do I have to change it using the remote synchronization script?

[jeff_albion · Accepted solution]

I will assume you're using SQL Anywhere version 12.

1. Schema changes are really only major concerns for the remote database - the consolidated database should not have any issues changing schema (assuming you have shut down the MobiLink server - your consolidated database should still function the same way regarding schema changes as it did before, prior to having MobiLink run on it )

2. You will only get this error if there is a publication defined on the SQL Anywhere database. Publications are only defined for remote databases (for MobiLink) and are not defined on the consolidated database. If you are finding that you have erroneously defined publications on the consolidated, you should DROP PUBLICATION to get rid of it, and try your schema change again.

3. If you are really trying to make a schema change on a remote (with the publication defined), you should review this documentation section. Your options to upgrade the remote SQL Anywhere database schema, include:

   • Upgrade it inside the sp_hook_dbmlsync_schema_upgrade hook that is run during synchronization
   • Use the START SYNCHRONIZATION SCHEMA CHANGE statement (and correlating 'STOP ...') to perform the schema change, surrounding any DDL you need to execute

[Former Member · Accepted solution]

You assumed right, I use the latest version SQL Anywhere 12. I have created a publication earlier in the consolidated SQLAnywhere database, containing amongst others the table actionParameter.

Now I have just deleted the publication and try to change the schema again. Alas, I get the following error:

The column 'actionParameterId' could not be modified in the database. Cannot convert 'BY‚\x10B˜\x11ဠto a uniqueidentifier [Sybase][ODBC Driver][SQL Anywhere]Cannot convert 'BY‚\x10B˜\x11ဠto a uniqueidentifier SQLCODE: -157 SQLSTATE: 07006 SQL Statement: ALTER TABLE "mobielcmp"."actionParameter" ALTER "actionParameterId" UNIQUEIDENTIFIER

1. I tried to solve this by truncating the table. Because those "strange characters" are the values of the rows from that PK column. But I'm not allowed to do this, because I get an error:

TRUNCATE TABLE "mobielcmp"."actionParameter" Could not execute statement. Data modifications are not allowed in a synchronization schema change SQLCODE=-1342, ODBC 3 State="HY000" Line 1, column 1

1. I also see a strange icon in the table name. See this screenshot: http://s14.postimage.org/ordpc2kxt/Screen_shot_2012_01_23_at_20_21_01.png What does it mean? Is it the reason for all the errors I get?

[jeff_albion · Accepted solution]

Cannot convert 'BY‚\x10B˜\x11ဠto a uniqueidentifier [Sybase][ODBC Driver][SQL Anywhere]Cannot convert 'BY‚\x10B˜\x11ဠto a uniqueidentifier

Yes, as you have surmised, this means that the column you're changing the data type for contains data that cannot be converted to the new data type.

TRUNCATE TABLE "mobielcmp"."actionParameter" Could not execute statement. Data modifications are not allowed in a synchronization schema change

This means that you have attempted a DML statement after issuing "START SYNCHRONIZATION SCHEMA CHANGE" - Note that this statement is NOT required for a SQL Anywhere consolidated database. You only need to use this syntax for changing schema on the remote database.

You need to issue 'STOP SYNCHRONIZATION SCHEMA CHANGE' to be able to modify rows once again.

What does it mean? Is it the reason for all the errors I get?

The icon means that it is a 'synchronized table' defined in a publication, that has been selected by a "START SYNCHRONIZATION SCHEMA CHANGE" statement and is currently 'locked' (which prevents modifying the data inside the table).

[Former Member · Accepted solution]

Thank you Jeff for the additional comment. 🙂 I have execute the STOP SYNCHRONIZATION SCHEMA CHANGE query and after that I was able to truncate the table. Then I successfully changed the PK column type to uniqueidentifier.