0

I have a spreadsheet of data, which is basically PART_ID and SPECS. The SPECS field is a large character string which, when imported into a temporary table, is stored as NVARCHAR(255).

Example:

'Widget', 'This part needs to be heated to 400 degrees before cutting.'

The ultimate table this has to go into for our ERP system stores the SPECS as a binary field.

If I do the following, I get a binary conversion:

INSERT INTO ERP_PART_TABLE (PART_ID, SPEC)
SELECT PART_ID, CONVERT(VARBINARY(MAX), SPEC) FROM TEMP_TABLE;

However, when I then go into the ERP system, all I see is the first character, and I get a chime telling me the system encountered some weird character.

I have seen this done in past conversions with a different conversion format, such as:

INSERT INTO ERP_PART_TABLE (PART_ID, SPEC)
SELECT PART_ID, CONVERT(VARBINARY(MAX), SPEC, 1) FROM TEMP_TABLE;

However, when I try to select with that formatted conversion, I get

'Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to varbinary.'

I have tried a TRY_CONVERT(varbinary(max), bits, 1) and the results came back consistently NULL.

Is there potentially some special character in my original text field that I'm not seeing? (Just for a test, I also tried selecting CONVERT(VARBINARY(MAX), 'Hello',1) and I still get the error.)

SQL*Server 2012.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
Howard Price
  • 1
  • Check if your data has `\0` null character. `select Replaced = replace('BAD_STRING_OR_COLUMN_NAME' COLLATE Latin1_General_BIN, nchar(0x00) COLLATE Latin1_General_BIN ,'') ` then try casting to varbinary. – Lukasz Szozda Aug 27 '15 at 14:46
  • And read [Null Literal](http://stackoverflow.com/a/2828467/5070879) – Lukasz Szozda Aug 27 '15 at 14:48
  • I have found a solution. It lies in converting the bits to VARCHAR within the converstion to VARBINARY. Like so: SELECT CONVERT(varbinary(max), CONVERT(varchar(max), spec)). Thanks! – Howard Price Aug 27 '15 at 15:51

1 Answers1

0

This may help if you have problem with Null Literal or \0. Code below will remove them.

SELECT
    PART_ID,
    CONVERT(VARBINARY(MAX), REPLACE(SPEC COLLATE Latin1_General_BIN, NCHAR(0x00) COLLATE Latin1_General_BIN ,'') , 1) 
FROM TEMP_TABLE;
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275