April 1, 2019 at 2:55 pm
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?
April 1, 2019 at 3:19 pm
Without seeing what you are doing it is really hard to answer your question.
April 1, 2019 at 4:20 pm
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
Eddie Wuerch
MCM: SQL
April 1, 2019 at 8:49 pm
Thanks Eddie. The second ok got me where I needed to be
April 1, 2019 at 9:21 pm
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.
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".
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy