The object name contains more than the maximum number of 2

Question

Post reply

The object name contains more than the maximum number of 2

tpiazza

SSC Enthusiast

Points: 187
More actions
April 1, 2019 at 2:55 pm

#2370674

I am trying to execute an alter command on a linked server. I am getting the error "The object name blah contains more than the maximum number of prefixes. The maximum is 2."
If I run a select command it works fine. I only get the issue when its an alter statement. Also everything is brackets
Any ideas on what would cause this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Without seeing what you are doing it is really hard to answer your question.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 2

You must execute that ALTER statement in the context of the database that contains the object.

When calling an object over a linked server, you must use a four-part name:

[1 - Linked Server Name].[2 - Database Name].[3 - Schema Name].[4 - Object Name]

The restriction on two-part names only allows this:

[1 - Schema Name].[2 - Object Name]

Not OK:

ALTER TABLE [Srv3].[MyRemoteDB].[dbo].[MyTable] -- four-part name

OK:

ALTER TABLE [dbo].[MyTable] -- two-part name

Also OK, calling sp_executesql in your remote database to execute the ALTER statement:

EXEC [Srv3].[MyRemoteDB].sys.sp_executesql N'ALTER TABLE [dbo].[MyTable] <etc>'-- four-part name to sp_executesql

This reply was modified 5 years, 8 months ago by Eddie Wuerch.
This reply was modified 5 years, 8 months ago by Eddie Wuerch.

Eddie Wuerch
MCM: SQL

tpiazza SSC Enthusiast Points: 187 More actions · Answer 3

Thanks Eddie. The second ok got me where I needed to be

ScottPletcher SSC Guru Points: 101106 More actions · Answer 4

You can have the db name in an ALTER, just not the server name (note the message says two prefixes, i.e., a total of 3 levels of object name). So this should be OK too and could be easier to execute on multiple remote instances:

EXEC (N’ALTER TABLE [Db_Name].dbo.Table_Name <etc>’) AT [Server_Name]

The tricky part is making sure that the login used to connect to the remote server has permissions to ALTER the table.

This reply was modified 5 years, 8 months ago by ScottPletcher.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".