Using MSSQL Binary Columns w/Datastage Server 7.5.1

[dstucke]

I'm having trouble using binary MSSQL columns in my DS job. I have a lookup table with a binary(16) key. Key values are created as GUIDs. I search the lookup table (using another column) to get the key to populated in a column on a target table, also a binary(16). I'm using an ODBC stage for both the lookup and target tables. We are using NLS for our project, having selected the MS1252 mapping.

I've followed the server edition documentation for developers that states for binary data to set the column data type as varchar(36) and the data element as SQL.GUID. I've done that on both the lookup table I'm selecting from and the target table I'm writing to. Additionally in these stages, I have column-level NLS selected for and have specified 'NONE' for the NLS map for both binary columns.

When I run the job, the row is never written to the database, and I get the following error: [DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification. (It appears that selecting the SQL.GUID data element automatically puts convert(uniqueidentifier,?) in the insert statement for me. In the insert statement for the binary column from the DS log, the data value appears as: X~+?K???'(?m.

Using SQL Server Query Analyzer, the binary value appears as 0x58057E2BBDBA924B90D6C64C2728F56D but appears as X~+?K???'(?m in Datastage.

Any ideas on how I can get this to work?

[dstucke]

In case anyone was interested, with IBM's help we were able to solve this problem. In addition to what was stated in the Datastage documentation on how we should handle GUID data types, we needed to add the following function in the transformer moving the binary data from source to target: OCONV(<binary_column_name>, "MX0C").

So, in Datastage we've followed the server edition documentation for developers that states for binary data to set the column data type as varchar(36) and the data element as SQL.GUID. I've done that on both the lookup table I'm selecting from and the target table I'm writing to. Additionally in these stages, I have column-level NLS selected for and have specified 'NONE' for the NLS map for both binary columns.

Adding the OCONV() function in the transformer did the trick. Special thanks to Ruth Gonzalez for her help!