The object name contains more than the maximum number of prefixes. The maximum is 3

insert into linked server
truncate table linked server
how to insert data from one server to another in sql server 2008
how to import data from one sql table to another
sql move table to another server
how to copy tables from one schema to another schema in sql server
migrate data from one database to another
sql server export schema and data

My stored procedure is trying to write a record in to a database on another Server. The statement is here:

IF @Builds > 0
   BEGIN
        SET @DPU = @Failures / @Builds
        INSERT INTO SQL05.ManufacturingPortal.dbo.OPC.WriteRequests (ID, RegisterID, Value, RequestedDate, IsCompleted)
        VALUES(@PLCID, 'F8:10' , CAST(@DPU AS NUMERIC(10,2)), GETDATE(), 0)    
    END 

However when I try to create the stored procedure - I get the following error:

The object name 'SQL05.ManufacturingPortal.dbo.OPC.WriteRequests' contains more than 
the maximum number of prefixes. The maximum is 3.

I have tried creating an alias of Server.DB.DBO to shorten the number of prefixes however seem to be implementing this wrong.

I cannot change my database schema on the target DB. Any suggestions on how I can get around this?

Thanks Guy

Correct four-part table name is server.database.schema.tablename - you have some excess parts there.

Looks like table name is OPC.WriteRequests? If yes, then you have to use brackets: SQL05.ManufacturingPortal.dbo.[OPC.WriteRequests]

But maybe you just have some part of name incorrect?

SQL Server Error Messages, 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  sql - The object name contains more than the maximum number of prefixes. The maximum is 2 - Stack Overflow. The object name contains more than the maximum number of prefixes. The maximum is 2. Ask Question. Asked 4 years, 11 months ago. Active 2 months ago. Viewed 8k times.

The reason you are receiving the error is because you are not using a valid name. You appear to be referencing two schemata, dbo and OPC.

The valid syntax is server_name.database_name.schema_name.object_name as referenced on the MSDN article for INSERT.

Remove the incorrect schema and try again.

The object name contains more than the maximum number of , The number name 'ServerName.AAD.dbo.t_employee' contains more than the maximum number of prefixes. The maximum is 3. I couldn't find  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:

I was using everything correct still the issue persisted. My command was like below

select * into server.database.schema.table from table2

I resolved it by creating the table in the server first and then used the insert into statement which executed without issues

Create Table...........
Insert into server.database.schema.table  select * from table2

Thanks, Sree

The object name contains more than the maximum number of prefixes, The number name 'ServerName.AAD.dbo.t_employee' contains more than the maximum number of prefixes. The maximum is 3. I couldn't find any other threads​  Msg 117, Level 15, State 2, Line 5 The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains more than the maximum number of prefixes. The maximum is 3. sql-server sql-server-2000 linked-server. share.

In my case, it actually wanted to be run on the server where the INTO table would live:

SELECT * 
INTO [database].[schema].[table] 
FROM [server].[database].[schema].[table]

Migrating every table to a new linked server has too many prefixes , If you have worked with Microsoft SQL long enough, you may have learned never trusted SQL error message 100%. An example below is a  The object name ‘SERVER01_TO_SERVER02.Target_DB.dbo.Target_Table’ contains more than the maximum number of prefixes. The maximum is 2 . So where is the issue?

use with braket "[]" for name and remote database server like this. [87.247.678.80,1666].[danesfe].[admin1].[homefarsi]

Linked Servers Problem, The object ' linkedserver.DB.DBO.SourceTable ' name contains more than the maximum number of prefixes. The maximum is 2. How to do this. The object name ‘..dbo.t_Student’ contains more than the maximum number of prefixes. The maximum is 2. Method 3 : In method 3, the format to alter the table will be Schema.Table Name. use test GO Alter table dbo.[t_Student] Add [Date of Admission] Datetime NULL --OUTPUT Command(s) completed successfully. Conclusion :

Why am i getting this error with linked server, Msg 117, Level 15, State 1, Line 1 The object name 'eih-dr01.eih.ehs.org.DBA.​dbo.fp_monthly' contains more than the maximum number of  The object name ‘x’ contains more than the maximum number of prefixes. The maximum is 2.

Posts about The object name contains more than the maximum number of prefixes. In method 3, the format to alter the table will be Schema. The object name 'SW-01.master.dbo.sysdatab ases.broke r.customer ' contains more than the maximum number of prefixes. The maximum is 3. The maximum is 3. Having found many references to alias searching this error, I tried:

The object name 'SW-01.master.dbo.sysdatabases.broker.customer' contains more than the maximum number of prefixes. The maximum is 3. Having found many references to alias searching this error, I tried: select c. In setting up Linked Servers, I can successfully SELECT data from a server other than the host server, but if I try to SELECT * INTO I get the following error: The object name 'DB1.ogSerial.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Comments
  • +1 Good guess. I would refrain from such table names. They add more to the confusion than doing any good. If at all a two word table name is needed, either use Pascal Casing or use _ to join the words.
  • Hi, The table name is "OPC.WriteRequests"... I know - I didn't write it :-( !
  • In that case, you just need to qualify the table name with square brackets like this [OPC.WriteRequests].
  • That's also what I thought.
  • in your example there is no linked server isn't it?