Trouble updating to external table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trouble updating to external table

Author

Topic

mirirom
Starting Member

7 Posts

Posted - 2004-08-25 : 13:12:20

hi,

i'm trying to write a query that updates/inserts to a database on a linked server. here's a piece of the code


-- UPDATE the project intersection set.  Only update changes to names and active states
UPDATE [MORIARTY\SEPIALINE].Argos.dbo.argProjects
SET [MORIARTY\SEPIALINE].Argos.dbo.argProjects.[Name] = #TempProtraxProjects.[Name],
    [MORIARTY\SEPIALINE].Argos.dbo.argProjects.Active = #TempProtraxProjects.Active
FROM #TempProtraxProjects JOIN [MORIARTY\SEPIALINE].Argos.dbo.argProjects
ON ([MORIARTY\SEPIALINE].Argos.dbo.argProjects.Number = #TempProtraxProjects.Number)

however, the code returns the following error:

Server: Msg 117, Level 15, State 2, Line 51
The number name 'MORIARTY\SEPIALINE.Argos.dbo.argProjects' contains more than the maximum number of prefixes. The maximum is 3.

based on what i could find in Books Online, i tried aliasing the external table name like:


UPDATE [MORIARTY\SEPIALINE].Argos.dbo.argProjects AS A
SET A.[Name] = ...

but this returns a syntax error "near AS" - um, ok. last i checked, this was valid. can anyone shed light on what's going on here? many thanks in advance!

..::mirirom::..

mirirom
Starting Member

7 Posts

Posted - 2004-08-25 : 13:58:02

just figured it out. my bad; forgot that aliases can only be used in a FROM clause. and in this case, just the name of the column to update is fine. this works...:


UPDATE [SERVERNAME].database.owner.targetTable
SET columnName = sourceTable.Value,
FROM sourceTable JOIN [SERVERNAME].database.owner.targetTable AS T      -- alias is useless here
ON (sourceTable.someVal = T.someVal)

..::mirirom::..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources