Problem inserting data into a varchar field using a stored procedure

Question

I am uploading some data from DB2 to SQL2005. The table contains one text field that is 2000 characters long in DB2 but is set up as a varchar(2000) in SQL.

When I run my insert in query browser it processes fine and the data is copied correctly, however when the same statement runs in my stored procedure the data for this field only is copied incorrectly and is shown as a series of strange characters.

The field can occasionally contain control characters however the problem occurs even when it doesn't.

Is there a setting i need to apply to my stored procedure in order to get the insert to work correctly?

Or do i need to use a cast or convert on the data in order to get it appearing correctly.

Thanks.

Update: Thanks for your suggestions. It now appears that the problem was caused by the software that we used to create the linked server containing the DB2 database. This could not handle a field 2000 characters long when run via a stored procedure but could when run interactively.

I ultimately got around the problem by splicing the field into 10 200 character long fields for the upload and then re-joined them again when they were in the SQL database.

Answer 1

It sounds like the data coming from db2 is in a different character set. You should make sure it isn't EBCDIC or something like that.

Also, try calling your stored procedure from the SQL Server Management Studio (query browser), to see if it works at all.

Answer 2

You might want to change your varchar(2000) to an nvarchars(2000) (in the stored procedure as well as the table - i assume it exists as a parameter). This would allow them to hold two byte characters. It'll depend on the DB2 configuration but it may be that it's exporting UTF-16 (or similar) rather than UTF-8.