Using IBMDA400 OLE DB driver to create linked server in MSSQL, need help translating data from CCSID 65535

Technically, if data is tagged as CCSID 65535, there is no conversion -- it's not text data, but rather binary data such as an image.

However, because many older systems were put in service with CCSID 65535 as the default, i.e., they were left in their "from the factory" state, there are many cases where text is stored as CCSID 65535 decades after a meaningful CCSID attribute should have been assigned. So, it's likely that a connection attribute to cause conversion as if the text data was properly defined in the database would be useful. (Best would be to correct the database column definition and fix any apps that use it.)

In any case, we need to see actual code for both what works and what fails. Any info on exactly what client version/fix level should also be supplied. There may be other issues as well, such as correcting user profile attributes on the server and language settings on the client, but details of the actual problem give a good starting point.

Hi Gary

You were right, it was the syntax.  I had been putting in spaces. Once I removed them the connection creates successfully and the data is translating correctly.

Now that it seems to work, I have tested some variations of the provider string statement.
 
All of these translate correctly.

"Force Translate=0;Default Collection=cltdta"
"Force Translate=1;Default Collection=cltdta"
"Force Translate=37;Default Collection=cltdta"

All of these do not translate.

"Force Translate=65535;Default Collection=cltdta"
"Default Collection=cltdta"

Can you tell me which is most correct to use and possibly why the others "are working" or "appear to work" as well?

And a second question, I am using the IBMDA400 OLE DB driver, there is also an IBMDASQL OLE DB driver, is there a reason I should use one over the other?

Thanks for the clarification.  I appreciate all the help.

Thanks again

If the database server jobs start up using a default CCSID of 37 (they usually do on a US-localized system), you can also use Force Tran late=0, which just says to translate 65535 as it is is the default CCSID used by the database server job running the request.

This is only an assumed minor (possible) typo correction. It looks to me as if the intended wording is:

If the database server jobs start up using a default CCSID of 37 (they usually do on a US-localized system), you can also use Force Tran late=0, which just says to translate 65535 as if it is in the default CCSID used by the database server job running the request.

"Force Translate" seemed like the the correct connection property name, but I've seen at least two additional ones depending (apparently) on the specific product version and release.

For "Force Translate=1", I was pretty sure that it was the opposite of "Force Translate=0". There is no defined 'CCSID 1' (00037 is the lowest CCSID value defined in IBM i systems), so it would be a "special" value. And because "0" should cause a translation, "1" would disable translation. But if it results in correct character values on the client system, it makes less sense to me.

Toms correction is right.  I will edit my response.  Thanks Tom.