I am Inserting records from a table on one server into a table on another server and expecting SQL server to implicitly convert the datatype of a text field to a varchar(20) field. When the original text field is empty (='') the varchar field ends up with 20 seemingly random control characters. I've tried using the CONVERT and CAST statements to, but they produce similar results.
The code looks like this (the field in question is the one going into cust_pn:
INSERT INTO tblPriceMatrix (accno, our_pn, cust_pn, price)
SELECT rtrim(m.mat_customer), rtrim(m.mat_stock_key), cast(m.mat_notes as varchar(20)), m.mat_price
FROM a.sales.dbo.stk_Matrix m, b.dbo.tblcatstock c
WHERE mat_concat_code NOT IN (SELECT rtrim(accno)+rtrim(our_pn) FROM tblPriceMatrix)
AND mat_stock_key = c.code
Has anyone come across this before, and is there a workaround?