Mobilink 10: problem sending blob bigger than 400kb to MS SQL 2005 consolidated database

[Former Member]

Hi, all

Our system uses MS SQL Server 2005 as consolidated database, ASA10 mobilink server and ultralite db (latest ebf 10.0.1.4239), odbc driver for MS SQL version is 2005.90.3042.00. I've got a problem uploading blobs bigger than ~400kb from mobile device to consolidated database. Smaller sizes make no problem. In the mobilink log I obtain this error messages:

E. 2011-06-21 23:44:30. <2> [-10072] Unable to insert into table 'DocLib' using upload_insert E. 2011-06-21 23:44:30. <2> [-10061] An error occurred while uploading an insert row into table 'DocLib'.

( table DocLib contains one column with blob data )

When I use ASA as consolidated database, there's no problem to upload blobs of any size on the table with the same structure. Any other desktop application using the same odbc as mobilink has no problem to write blobs of any size to this table even on MS SQL database.

Mobilink is started with this command line: mlsrv10 -c "DSN=<database>;UID=<login>;PWD=<pass>" -v+ -o log.mle

This problem occures on Windows 2003 server OS with MSSQL 2005 and on desktop Windows XP with MSSQL 2005 too. Platform for remote database is Windows mobile 6.5

Is there any way to solve this problem on MS SQL Server? Or are there needed some other technical details?

Any help appreciated, thanks in advance

Regards

Ivan

[Former Member]

I have tried to upload a 10 MB blob to my MSSQL 2005 with the MobiLink server version 10.0.1.4239 and ODBC driver version 2005.90.1399.00 on Windows XP and it works very well. Could you post the definition of your upload table that contains the ~400KB blob (please modify the table and column names if they mean something that is sensitive). -- Thanks.

[Former Member]

Yes, of course, here are the definitions (it doesn't work on any other table with the same blob datatypes, but I use this one mostly):

this is remote definition:

CREATE TABLE "HamSFA"."DocLib" ( "DL_ID" integer NOT NULL , "DL_REALID" varchar(128) NULL , "DL_NAME" varchar(255) NOT NULL , "DL_SIZE" integer NOT NULL , "DL_INTTYPE" smallint NOT NULL , "DL_TYPE" smallint NULL , "DL_KEYWORDS" varchar(255) NULL , "DL_LASTCONCHNG" datetime NOT NULL , "DL_FLAGS" integer NOT NULL DEFAULT 0 , "DL_REFTAB" smallint NOT NULL , "DL_REFID" integer NOT NULL , "DL_REFID2" integer NULL , "DL_FILE" long binary NULL , "DL_NOTE" long varchar NULL , PRIMARY KEY ("DL_ID"), )

Create script for MSSQL consolidated table is:

CREATE TABLE "HamSFA"."DocLib" ( "DL_ID" integer NOT NULL, "DL_REALID" varchar(128) NULL, "DL_NAME" varchar(255) NOT NULL, "DL_SIZE" integer NOT NULL, "DL_INTTYPE" smallint NOT NULL, "DL_TYPE" smallint NULL, "DL_KEYWORDS" varchar(255) NULL, "DL_LASTCONCHNG" datetime NOT NULL, "DL_FLAGS" integer NOT NULL default 0, "DL_REFTAB" smallint NOT NULL, "DL_REFID" integer NOT NULL, "DL_REFID2" integer NULL, "ACTIVE" bit NOT NULL default 1, "TSTAMP" datetime NOT NULL default current_timestamp, "DL_FILE" image NULL, "DL_NOTE" text NULL, PRIMARY KEY ("DL_ID") )

blob field is DL_FILE. Do you see any differences between your definitions? I tried to use the recommended driver version 2005.90.2047.00.

thanks

[Former Member]

Thank you for posting the table definitions. I have tried a sync test with your table definitions and I did not see any problem. Here are my upload_insert script and upload data:

insert into test ( pk, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c14, c15 ) values(?,?,?,?,?,?,?,?,?,?,?,?,?,? ) <2> Insert row [template_T1]: <2> 1 <2> c1 <2> c2 <2> 3 <2> 4 <2> 5 <2> c6 <2> 2011-07-07 11:06:59.355000 <2> 8 <2> 9 <2> 10 <2> 11 <2> [1000000 Bytes] <2> 0x6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A6162636465666768696A ... <2> [1000000 Characters] <2> 0x30313233343536373839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839 ... <2> end_upload <connection> (no script)

Please note: your table definition in the consolidated database is different from that in the remote. Then you need to write the upload scripts based on the table defined in the remote. Please check your upload scripts for this table. If you cannot find any problems in your setup, please open a support case with the Sybase techsupport.

[Former Member]

this is my part of log where you can see upload_insert script, values and error messages, can you see pls something wrong?

I. 2011-07-07 11:38:27. <1> Translated SQL:
                        insert into HamSFA.DocLib ( DL_ID, DL_REALID, DL_NAME, DL_SIZE, DL_INTTYPE, DL_TYPE, DL_KEYWORDS, DL_LASTCONCHNG,

DL_FLAGS, DL_REFTAB, DL_REFID, DL_REFID2,  DL_FILE, DL_NOTE )

values ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )

I. 2011-07-07 11:38:27. <1> Insert row [DocLib]:
I. 2011-07-07 11:38:27. <1>   17100044
I. 2011-07-07 11:38:27. <1> NULL
I. 2011-07-07 11:38:27. <1>   report.xls
I. 2011-07-07 11:38:27. <1>   493568
I. 2011-07-07 11:38:27. <1>   3
I. 2011-07-07 11:38:27. <1> NULL
I. 2011-07-07 11:38:27. <1> NULL
I. 2011-07-07 11:38:27. <1>   2011-07-06 18:21:43.000000
I. 2011-07-07 11:38:27. <1>   0
I. 2011-07-07 11:38:27. <1>   4
I. 2011-07-07 11:38:27. <1>   17100165
I. 2011-07-07 11:38:27. <1> NULL
I. 2011-07-07 11:38:27. <1>   [493568 Bytes]
I. 2011-07-07 11:38:27. <1>   0xD0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF090006000000000000000000000008000000010000000000000000100000A702000003000000FEFFFFFF000000000000000072000000F300000074010000F501000076020000 ...
I. 2011-07-07 11:38:27. <1> NULL
E. 2011-07-07 11:38:28. <1> [-10072] Unable to insert into table 'DocLib' using upload_insert
E. 2011-07-07 11:38:28. <1> [-10061] An error occurred while uploading an insert row into table 'DocLib'.

[Former Member]

I cannot see anything wrong. Please try to restart the MobiLink server with the command line option, -tt trace.out that turns on ODBC tracing and then rerun your sync. After the problem occurred, shutdown the MobiLink server and post the trace.out file (please make sure there is no sensitive data in the trace.out file).

[Former Member]

Yufei Guo: I have the trace.out file from the unsuccessfull upload. Can I send it to you by e-mail? Would you be so kind and look at it? Many thanks

[Former Member]

@Ivan: Is it possible to attach your ODBC tracing file to this thread? If the answer is yes, please make sure there no sensitive data in the tracing file. Thanks!

[Former Member]

Sorry to say, it tells me that I don't have enough reputation to attach files here...

[Former Member]

FYI, to email you can use firstname.lastname at this domain.

[Former Member]

The recommended ODBC driver for this configuration is 2005.90.2047.00, as stated here. We haven't tested with the ODBC driver you are using, and it may not work. Not all ODBC drivers work properly, as we've found time and time again in our testing, so it is important to stick to our recommendations.

[Former Member]

Sorry to say, but after I downgraded the ODBC driver to recommended version, I obtain the same error. No previous error appears in the mobilink log and smaller blobs are succesfully trasfereed. Could you give me pls any other advices what should I try?

Thanks a lot

I.

[Former Member]

To help determine whether the problem is with MobiLink or UltraLite, you could try using SQL Anywhere and dbmlsync on the remote side instead of UltraLite.

[Former Member]

Did you see any errors before the [-10072] error? Could you please post your MobiLink server logging messages prior to this error? Please don't post any of the upload and/or download data. Thanks!

[Former Member]

Thanks for your answer, I didn't find any previous errors, just normal upload messages. And when I'm trying to upload a smaller blob from the same device the same way, it passes ok. It's strange, I'll try the lower version of ODBC driver as Russ said I should use, but it take me some time to get it (no way to find the dll on the internet yet)...

[Breck_Carter]

From SQL Server's point of view, MobiLink is just client. Can you try inserting a 400K+ blob via some OTHER client application using ODBC? If that works, then the problem lies with MobiLink. If it does not work, then... a clue!

[VolkerBarth]

As to the question, other applications can store these blobs. So that's a clue in my book:)

[Former Member]

Yes, I tried to use win application using the same ODBC to store the big blob to the same table, and it works fine, there was no problem.

[Former Member]

Did you use the same translated SQL reported in the verbose ML server log file (log.mle) either directly or as a prepared statement?

Also, note that ML defaults to not using snapshot isolation for uploads with MSS. See the following topics (for 10.0.1):

http://dcx.sybase.com/index.html#1001/en/dbmlen10/ml-mg-basics-s-4506931.html

http://dcx.sybase.com/index.html#1001/en/dbmlen10/ml-mg-basics-s-4506931.html

[Breck_Carter]

@Volker... ah, yes, he did say that in the original question, didn't he? That would make me... wait for it... clue-less 🙂

[Former Member]

The first link should have been:

http://dcx.sybase.com/index.html#1001/en/dbmlen10/ml-ml-basics-mss-5159304.html