Linked Server Update - difference in behaviour SQL2005/SQL2000

Question

Post reply

Linked Server Update - difference in behaviour SQL2005/SQL2000

mg2h

Grasshopper

Points: 21
More actions
May 30, 2010 at 10:35 am

#235719

Sorry if I have the wrong place for this question but I'm new to these forums..
I have an update as follows which works fine in SQL server 2005
but throws a 'statement could not be prepared / ambiguous column names for each of the columns I am trying to update when run in SQL Server 2000
UPDATE [LINKEDSERVER].DATABASE.dbo.TABLE1
SET
TableNumber = T.TableNumber,
LineNumber = T.LineNumber,
.....
UnitNumber = T.UnitNumber
FROM [LINKED SERVER].DATABASE.dbo.TABLE1 AS W,
[LINKEDSERVER].DATABASE.dbo.temporaryTABLE AS
WHERE W.IdentityID = T.IdentityID
Can anyone shed any light on this as I need to get the update to work in SQL 2000?

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323750 More actions · Answer 1

if there are two tables, aliased W and T, i think it should look like this:

UPDATE W

SET

W.TableNumber = T.TableNumber,

W.LineNumber = T.LineNumber,

.....

UnitNumber = T.UnitNumber

FROM [LINKED SERVER].DATABASE.dbo.TABLE1 AS W,

[LINKEDSERVER].DATABASE.dbo.temporaryTABLE AS T

WHERE W.IdentityID = T.IdentityID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mg2h Grasshopper Points: 21 More actions · Answer 2

Unfortunately not - although your suggestion looks fine the error is the same:

Server: Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Server: Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'UnitNumber'. .......

Ultimately I am trying to update TABLE1 from TemporaryTABLE, both of which are on the linked server. This syntax works fine on SQL2005 and SQL2000 if you are just updating TABLE1 from a local table but as soon as you try updating from a table on the linked server itself SQL 2000 complains.

I am running the update entirely on the linked server for performance reasons and it works well in SQL2005 but fails in SQL2000. If there is a better way of doing this, perhaps using OPENQUERY I'd appreciate some pointers but the question remains : what is the right syntax to update one table from another with BOTH tables on a linked server and in SQL2000?

Brandie Tarvin SSC Guru Points: 173116 More actions · Answer 3

Try re-writing your code in ANSI style joins.

UPDATE W

SET TableNumber = T.TableNumber,

LineNumber = T.LineNumber,

.....

UnitNumber = T.UnitNumber

FROM [LINKED SERVER].DATABASE.dbo.TABLE1 AS W

INNER JOIN [LINKEDSERVER].DATABASE.dbo.temporaryTABLE AS T

ON W.IdentityID = T.IdentityID

The method you are using is deprecated and will be going away as soon as Microsoft decides to stop supporting it.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

mg2h Grasshopper Points: 21 More actions · Answer 4

Many thanks for the suggestion but still no luck - the same error.

Having said that I got round the problem by renaming the columns in the temporary table so that the two tables no longer had identical column names.

mr_kamaal_khan Grasshopper Points: 15 More actions · Answer 5

yes, you can solve the issue by renaming the columns in any of the table. Thanks for the hint.