Update linked server

• e000911

  Grasshopper

  Points: 16

  More actions

  June 26, 2013 at 1:51 am

  #275595

  Hi

  i have linked server called TESTdata

  with coloumn id which is varchar datatype that contain number .

  i want to update the last recourd with +1 .

  DECLARE @CR_num TABLE (

  NUM varchar(50) )

  declare @NUM as varchar(50)

  select @NUM=convert(int,[CCFNUM_LASTNUM_00N])+1 from [TEST_DATA].[***].[dbo].[CCFNUM] where [CCFNUM_LOCATION_00N]='9999'

  update [TEST_DATA].[***].[dbo].[CCFNUM] set [CCFNUM_LASTNUM_00N]=(select @NUM) where [CCFNUM_LOCATION_00N]='9999'

  but i get this error :

  OLE DB provider "*****" for linked server "TEST_DATA" returned message " Ambiguous table reference: (CCFNUM)

  UPDATE << Syntax Error >> CCFNUM SET CCFNUM_LASTNUM_00N = ?

  WHERE CCFNUM_LOCATION_00N = ? AND CCFNUM_LASTNUM_00N

  ".

  Msg 7343, Level 16, State 4, Line 5

  The OLE DB provider "****" for linked server "TEST_DATA" could not UPDATE table "[TEST_DATA].[****].[dbo].[CCFNUM]".

  ===========

  but

  when i update wit this command :

  update [TEST_DATA].[****].[dbo].[CCFNUM] set [CCFNUM_LASTNUM_00N]=1515 where [CCFNUM_LOCATION_00N]='9999'

  it run successfully ???!!!

  any suggesting ?

  regards,,,,

• HanShi

  SSC-Dedicated

  Points: 33506

  More actions

  June 26, 2013 at 2:13 am

  #1627417

  I think it is related to the datatype of the variable. If the columntype is INT the variable must be implicitly converted from the VARCHAR type.

  If the value is always a number, why have you declared it as a VARCHAR and not as an INT?

  ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

• e000911

  Grasshopper

  Points: 16

  More actions

  June 26, 2013 at 2:34 am

  #1627424

  The destination datatype is varchar and we can't change it.