SQL Server Error: "Maximum Number of Prefixes. Maximum 3" with Subquery Syntax

Question

Attempt to run cross-server update:

UPDATE asilive.Contoso.dbo.lsipos SET PostHistorySequencenNmber = ( SELECT TransactionNumber FROM Transactions WHERE Transactions.TransactionDate = asilive.CMSFintrac.dbo.lsipos.TransactionDate) 

Gives an error:

 Server: Msg 117, Level 15, State 2, Line 5 The number name 'asilive.Contoso.dbo.lsipos' contains more than the maximum number of prefixes. The maximum is 3. 

What gives?

---

Note. Reordering a request in a less readable form:

 UPDATE asilive.Contoso.dbo.lsipos SET PostHistorySequenceNumber = B.TransactionNumber FROM cmslive.Contoso.dbo.lsipos A INNER JOIN Transactions B ON A.TransactionDate = B.TransactionDate 

does not give an error.

see also

• SQL Server Error: Maximum Number of Prefixes. The maximum is 3. with join syntax
  (Suggestions with join syntax, this question is about subselect syntax)

Answer 1

That is how it is. You cannot have more than three prefixes, so you need to use aliases when you go through 3 (mainly when connecting to other servers). So it was with Sql Server 7 (and maybe before I can not remember on 6.5).

If you want to make your code more readable by using aliases, specify a more meaningful alias that will be much easier to follow.

Example:

 SELECT production_accounting_clients.[ClientName] FROM Production.Accounting.dbo.Clients production_accounting_clients