SSMA for Sybase truncating image data type to 32768

Tahir Khalil 61

Hi,

I am using SSMA for Sybase to migrate Sybase ASE 16 database to MS SQL Server 2022 on Linux.

A table with image type column is migrated but the data in this column limited to 32768. I believe if this data migration program is using bcp - it might be the limitation of that, but I am not sure.

Can someone help in this? I try to figure out the setting, but could not find anything related to image data type.

Thanks

2 answers

LiHongMSFT-4306 29,516 Reputation points

2024-10-23T03:19:46.8333333+00:00
Hi @Tahir Khalil

How about change the column type to VARBINARY(MAX):

CAST(ColumnName AS VARBINARY(MAX))

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.

1 person found this answer helpful.
Tahir Khalil 61 Reputation points

2024-10-23T13:41:29.76+00:00

You mean at target SQL Server side - Although I read in some documentations of SQL Server that the image type would be deprecated but still the SSMS is mapping it to image data type at target. I am not sure the impact - if someone can confirm that SQLBulkCopy class functionality would work without limiting the copying of data from source to target for varbinary. I have seen the defaults of BCP usually dont go beyond 32K.

The Sybase documentation says for BCP

When you copy out text or image data, the SAP ASE server, by default, copies only the first 32K of data in a text or image field.

The -T text_or_image_size parameter allows you to specify a different value.

Just need a confirmation a way to get more than 32k from Sybase is enabled in SQLBulkCopy class?

Thanks

Tahir Khalil 61 Reputation points

2024-11-04T17:21:16.84+00:00

Anyone can check this please?

Tahir Khalil 61 Reputation points

2024-11-05T22:09:26.0866667+00:00

@LiHongMSFT-4306I tried with your suggestion the result is same - the data migrator is again transferring the same amount of data ie. 32768.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Pieter Coene 0 Reputation points

2024-12-16T13:03:21.09+00:00

Same issue here, did you find a solution for this problem?

Greetz
Pieter
Please sign in to rate this answer.
Tahir Khalil 61 Reputation points

2024-12-16T13:24:03.09+00:00

No solution yet.

Pieter Coene 0 Reputation points

2024-12-16T16:18:17.32+00:00

Well, i had this behaviour with the 9.5 version of SSMA. I upgraded this week to the version 10 (no sure what is changed, the releasenote says 'minor bugfixes'). I wanted to reproduce this issue (using the GUI -> Migrate Data) , now the 'image' column is fully copied meaning that the column on the MS SQL side is > 32 Kb.

Can you try this?

Greetz

Pieter Coene 0 Reputation points

2024-12-16T16:22:13.5833333+00:00

I had this issue with version 9.5 of the SSMA. I upgrade this week to version 10 (not sure what is changed, the releasenote just says 'some minor bugfixes'). When i wanted to reproduce this issue (using the GUI -> Migrate Data), the 'image' colum is now fully migrated, meaning the column contains data > 32 Kb.

Can you try this?

Greetz

Pieter

Tahir Khalil 61 Reputation points

2024-12-16T18:09:09.44+00:00

Thank you - let me download & try the new version

Pieter Coene 0 Reputation points

2024-12-17T07:25:52.97+00:00

I tested again, it only works when i select just one table -> Migrate Data. When i select more than one table, i get the 32Kb limit...

Pieter Coene 0 Reputation points

2024-12-17T07:48:34.1333333+00:00

Tested again, i only works when you select one table -> Migrate Data. When i tried with multiple tables, i also het the 32Kb limit. Very annoying...

Tahir Khalil 61 Reputation points

2024-12-17T12:46:36.74+00:00

Ah ! thats why I was confused with the same behavior of my 9.4 or 9.5 version - when I tried single table - it did transfer correctly, but in a full batch - it was not.

I dont know if Microsoft guys are looking or taking care of these discussions.

Pieter Coene 0 Reputation points

2024-12-23T13:45:29.7833333+00:00

I don't know how long this SSMA tool exists already, but for me this is a bug they should have fixed many years ago. Its never too late but don't get your hopes to high...
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

SSMA for Sybase truncating image data type to 32768

2 answers

Your answer