SQL Server Error: maximum number of prefixes. The

问题:

Trying to run a cross-server update:

UPDATE ASILIVE.CustomerManagementSystem.dbo.Sessions
SET ASILIVE.CustomerManagementSystem.dbo.Sessions.VarianceAmount=Variances.VarianceAmount
FROM ASILIVE.CustomerManagementSystem.dbo.Sessions
    INNER JOIN Variances
    ON ASILIVE.CustomerManagementSystem.dbo.Sessions.SessionGUID = Variances.SessionGUID
WHERE ASILIVE.CustomerManagementSystem.dbo.Sessions.VarianceAmount <> Variances.VarianceAmount

Gives the error:

Msg 117, Level 15, State 2, Line 5
The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains 
more than the maximum number of prefixes. The maximum is 3.

What gives?

---

See also

• SQL Server Error: "maximum number of prefixes. The maximum is 3" with subselect syntax
  (Deals with sub-select syntax; this question deals with join syntax)

---

Unimportant research:

i tried randomly aliasing things to s:

UPDATE ASILIVE.CustomerManagementSystem.dbo.Sessions s
SET s.VarianceAmount=Variances.VarianceAmount
FROM ASILIVE.CustomerManagementSystem.dbo.Sessions s
    INNER JOIN Variances
    ON s.SessionGUID = Variances.SessionGUID
WHERE s.VarianceAmount <> Variances.VarianceAmount

But that doesn't work:

Msg 117, Level 15, State 2, Line 5
The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains 
more than the maximum number of prefixes. The maximum is 3.

Hamlin suggested added brackets:

UPDATE [ASILIVE].[CustomerManagementSystem].dbo.Sessions
SET [ASILIVE].[CustomerManagementSystem].dbo.Sessions.DisciplineVarianceAmount=DisciplineVariances.VarianceAmount
FROM [ASILIVE].[CustomerManagementSystem].dbo.Sessions
    INNER JOIN DisciplineVariances
    ON [ASILIVE].[CustomerManagementSystem].dbo.Sessions.SessionGUID = DisciplineVariances.SessionGUID
WHERE [ASILIVE].[CustomerManagementSystem].dbo.Sessions.DisciplineVarianceAmount <> DisciplineVariances.VarianceAmount

but that doesn't work:

Msg 117, Level 15, State 2, Line 5
The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains
more than the maximum number of prefixes. The maximum is 3.

回答1:

Do you really like a lot of typing? :-)

UPDATE s
  SET s.DisciplineVarianceAmount = v.VarianceAmount
  FROM [ASILIVE].[CustomerManagementSystem].dbo.Sessions AS s
  INNER JOIN dbo.Variances AS v
  ON s.SessionGUID = v.SessionGUID
  AND s.VarianceAmount <> v.VarianceAmount;

Take note that you may want to describe what to do here if either variance amount is currently NULL.

回答2:

Often times, you need to add brackets, at a minimum, surrounding your Linked Server Name.

[ASILIVE].[CustomerManagementSystem].dbo.Sessions

EDIT - Try this in addition

UPDATE S
SET DisciplineVarianceAmount = Variances.VarianceAmount
FROM [ASILIVE].[CustomerManagementSystem].dbo.Sessions as S
    INNER JOIN Variances ON S.SessionGUID = Variances.SessionGUID
WHERE S.VarianceAmount <> Variances.VarianceAmount