Binary to Binary - GUIDs

[Prashant15]

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?