Text datatype adds spurious characters when converted to varchar during an Insert

• barry555

  SSC Eights!

  Points: 944

  More actions

  June 22, 2004 at 3:04 am

  #360157

  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?

• June 25, 2004 at 8:00 am

  This was removed by the editor as SPAM

• Sylvain Coutu

  Ten Centuries

  Points: 1077

  More actions

  June 25, 2004 at 9:27 am

  #511983

  Hey!!! just a tought

  ISNULL ( check_expression , replacement_value )

  I personnaly did not try it.