SQL Server错误:“最大前缀数".最大值为3英寸带有subselect语法 [英] SQL Server Error: "maximum number of prefixes. The maximum is 3" with subselect syntax

问题描述

尝试运行跨服务器更新:

Trying to run a cross-server update:

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

给出错误:

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.

有什么作用?

注意:将查询重新排列为可读性较低的联接形式:

Note: Rearranging the query into a less readable join 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

没有给出错误.

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

推荐答案

是的，就是这样.前缀不能超过三个，因此超过3个时必须使用别名(主要是在加入其他服务器时).自Sql Server 7以来就是这种方式(也许在我不记得6.5之前).

Yes, that is just the way it is. You can't have more than three prefixes, so you have to use an aliases when you go over 3 (mainly when joining to other servers). It's been that way since Sql Server 7 (and maybe before I can't remember on 6.5).

如果要在使用别名时使代码更具可读性，请指定一个更有意义的别名，这将使它的使用变得更加容易.

If you want to make your code more readable when using aliases, specify a more meaningful alias which will make it a lot easier to follow.

示例:

SELECT 

production_accounting_clients.[ClientName]

FROM Production.Accounting.dbo.Clients production_accounting_clients

这篇关于SQL Server错误:“最大前缀数".最大值为3英寸带有subselect语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！