String or binary data would be truncated on Linked Server Query

• hookit2000

  SSC Veteran

  Points: 213

  More actions

  February 3, 2009 at 9:10 am

  #130660

  Hi,

  I am trying to fetch data from a linked server and insert into the dev server with the same data type and length. Casting or converting doesnt help either. linked server is sql2000 and destination server is sql2005

  But i am getting error

  Msg 8152, Level 16, State 14, Line 4

  String or binary data would be truncated.

  The statement has been terminated.

  When i set the ansi_warnings to OFF and ran the same query it throws an error

  Msg 7405, Level 16, State 1, Line 4

  Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

  eg:

  SET ANSI_WARNINGS ON

  SET ANSI_NULLS ON

  SET QUOTED_IDENTIFIER ON

  SET IDENTITY_INSERT Applications ON

  GO

  INSERT INTO Applications

  (Application_ID) --int4

  SELECT ApplicationID --int4

  FROM [LinkedServer].Sampledb.dbo.Applications

  WHERE ApplicationID NOT IN (SELECT Application_ID FROM Applications WHERE Application_ID IS NOT NULL)

  Thanks

  Ganesh

• CrazyMan

  SSChampion

  Points: 12560

  More actions

  February 3, 2009 at 9:12 am

  #937234

  Hi

  This error occurs when there is a problem with the length of the data field, check the length on both tables 🙂

• hookit2000

  SSC Veteran

  Points: 213

  More actions

  February 3, 2009 at 9:28 am

  #937254

  Thanks for the reply. I figured it out.

  length of the field is same in both tables.

  But the default value length for one of the columns is more than what is set for the column length.