I am using SQL Anywhere version 10.0.1.4310. I am using SQL Server as the consolidated database. I am using mobilink server to sync an ultralite database with SQL Server. I currently have these setup and working on old servers that need to be upgraded. I setup 2 new servers running Windows 2008 R2. I have SQL Anywhere/Mobilink installed on one and SQL Server on the other. I made a back up of the SQL server database and restored it on the new SQL Server. When I try to sync the utlratlite database, I can see it is connecting to the new server. Eventually it hits an error. When I check the mobilink log file, I get this error.

E. 2013-03-27 14:53:01. <1> [-10002] Consolidated database server or ODBC error: ODBC: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'ml_database' when IDENTITY_INSERT is set to OFF. (ODBC State = 23000, Native error code = 544)

I. 2013-03-27 14:53:02. <1> Synchronization failed

I did not make any changes to the SQL server database. I just created a new database and restored it from the .bak file.

After seeing this error I ran this on the SQL server.

SET IDENTITY_INSERT ml_database ON

That did not make a difference. What do I need to do to resolve this error?

asked 27 Mar '13, 16:00

bwilmeth's gravatar image

bwilmeth
1223
accept rate: 0%

edited 27 Mar '13, 17:24

Mark%20Culp's gravatar image

Mark Culp
22.7k9129266


You don't mention which version of SQL Server you are using, but it seems that IDENTITY_INSERT is a session property that can only be set for one table at a time in a session, and it requires specific permissions. Thus you might need to set it in your begin_connection script, if you only need to do that for one table. Or maybe the ML user doesn't have correct permission in your new database.

Note that since SQL Server IDENTITY columns have automatic values, it's not usual to synchronize them. If you want to upload values to that column, maybe it should be a different type.

permanent link

answered 27 Mar '13, 17:33

Graham%20Hurst's gravatar image

Graham Hurst
2.6k11843
accept rate: 28%

Thanks for you response. I'm using SQL Server 2008. This is the same version of SQL on the original server. I didn't have to set that in the connection on the old server. I did check the ML user's rights on the new server. They all look the same. I will double check this.

ml_database table is a mobilink system table. It looks like it uses it to authenticate. Not sure I should change the type.

(27 Mar '13, 17:41) bwilmeth
Replies hidden
Comment Text Removed

It seems like the ml_database table has changed since it was created by running the MobiLink\setup\syncmss.sql file to install the MobiLink system setup. Here's how that table is created in the 10.0.1.4310 version of syncmss.sql:

create table ml_database (
    rid                  integer          not null,
    remote_id            varchar( 128 )   not null unique,
    description          varchar( 128 )   null,
    primary key( rid ) )
go

That doesn't have any IDENTITY columns.

Sorry that I didn't initially notice that you were having the problem with a MobiLink system table. It seems that the MobiLink system setup is corrupt in the new SQL Server database.

(27 Mar '13, 18:15) Graham Hurst

I'm not sure how it got changed, it doesn't seem to be a problem on our old server or are test environment though. I did find something interesting. On the old server and our testing server, it doesn't seem to be inserting the rid value when I sync.

This is from the log file.

INSERT INTO ml_database ( remote_id, description ) VALUES( ?, ? )

But on the new server it is

INSERT INTO ml_database ( rid, remote_id, description ) VALUES( ?, ?, ? )

Where can I find this script to change it? What would make it be different?

The other difference is the old server and the test server are running sybase central on Windows 2003 R2 Standard. The new server is Windows 2008 R2 Enterprise

(28 Mar '13, 13:24) bwilmeth
Replies hidden
1

A colleague has pointed out to me that a change we made from version 10 to version 11 was to make that column an IDENTITY column. So your ML system setup isn't corrupt, it just doesn't match the version of the ML server that you are using.

In other words, on your new server you have a version 11 ML system setup, but are running a version 10 ML server. Your old server and test environment are apparently using version 11 ML servers.

(28 Mar '13, 14:08) Graham Hurst

I found the problem. The old server had some database setup for SQL anywhere version 10 and some with 11. The SQL server database I backed up was setup using SQL Anywhere version 11 not 10. I installed version 11 on the new server and set the mobilink service to run mlsrv11.exe and it is now syncing without the error.

permanent link

answered 28 Mar '13, 15:29

bwilmeth's gravatar image

bwilmeth
1223
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×284
×135
×72
×8

question asked: 27 Mar '13, 16:00

question was seen: 1,750 times

last updated: 28 Mar '13, 15:29