June 26, 2013 at 1:51 am
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,,,,
June 26, 2013 at 2:13 am
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?
June 26, 2013 at 2:34 am
The destination datatype is varchar and we can't change it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy