SQL Server Error Messages - Msg 117¶

Error Message¶

Server: Msg 117, Level 15, State 2, Line 4
The object name 'Object Name' contains more than the maximum number of prefixes.  The maximum is 2.

Causes¶

There are different ways of encountering this error message which usually involves accessing or manipulating objects on a linked server. One way is when updating a table on another server using a linked server, as follows:

UPDATE [SQLServer2].[MyDatabase2].[dbo].[MyTable2]
SET [MyName] = B.[MyName]
FROM [MyDatabase1].[dbo].[MyTable1] B
WHERE [SQLServer2].[MyDatabase2].[dbo].[MyTable2].[MyKey] = B.[MyKey]

Msg 117, Level 15, State 2, Line 4
The number name 'SQLServer2.MyDatabase2.dbo.MyTable2' contains more than the maximum number of prefixes.  The maximum is 3.

Another way is when trying to create a table on another SQL Server using the data from one SQL Server using the SELECT INTO statement:

SELECT *
INTO [SQLServer2].[MyDatabase2].[dbo].[MyTable2]
FROM [MyDatabase1].[dbo].[MyTable1]

Msg 117, Level 15, State 1, Line 3
The object name 'SQLServer2.MyDatabase2.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Yet another way is while trying to drop a table on a linked SQL Server:

DROP TABLE [SQLServer2].[MyDatabase2].[dbo].[MyTable2]

Msg 117, Level 15, State 1, Line 1
The object name 'SQLServer2.MyDatabase2.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Solution / Work Around¶

To solve the issue with the UPDATE statement using a linked SQL Server, the UPDATE statement can be re-written such that the table in the linked SQL Server being updated is included in the FROM clause and provided an alias. From the following UPDATE statement shown above:

UPDATE [SQLServer2].[MyDatabase2].[dbo].[MyTable2]
SET [MyName] = B.[MyName]
FROM [MyDatabase1].[dbo].[MyTable1] B
WHERE [SQLServer2].[MyDatabase2].[dbo].[MyTable2].[MyKey] = B.[MyKey]

The updated version will be as follows:

UPDATE A
SET [MyName] = B.[MyName]
FROM [SQLServer2].[MyDatabase2].[dbo].[MyTable2] A INNER JOIN [MyDatabase1].[dbo].[MyTable1] B
  ON A.[MyKey] = B.[MyKey]

In the case of the SELECT INTO statement where the table being created is on another server that is being accessed using a linked SQL server, there are 2 possible ways of addressing the issue. The first option involves having a linked server defined on the second SQL Server that points to the first SQL Server. If such a linked server is defined, then the following statement can be executed to create the table on the second SQL Server using the data on the first SQL Server:

EXECUTE [SQLServer2].[MyDatabase2].[dbo].[sp_executesql] 
N'SELECT * INTO [dbo].[MyTable2] FROM [SQLServer1].[MyDatabase1].[dbo].[MyTable1]'

If a linked SQL Server cannot be defined on the second SQL Server that points to the first SQL Server, then the only option is to first manually create the table on the second SQL Server then issuing the following INSERT INTO ... SELECT FROM statement that copies the contents of the table.

INSERT INTO [SQLServer2].[MyDatabase2].[dbo].[MyTable2]
SELECT * FROM [MyDatabase1].[dbo].[MyTable1]

In the case of the DROP TABLE statement on a linked SQL Server, the solution is to use the [dbo].[sp_executesql] command to execute the DROP TABLE command on the linked SQL Server:

EXECUTE [SQLServer2].[MyDatabase2].[dbo].[sp_executesql] N'DROP TABLE [dbo].[MyTable2]'