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

Question

My stored procedure is trying to write a record 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 a 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 tried creating an alias for Server.DB.DBO to reduce the number of prefixes, however, it seems to be wrong.

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

Thanks Guy

Answer 1

The correct four-part table name is server.database.schema.tablename - you have extra parts.

Looks like the table name is OPC.WriteRequests? If so, then you should use parentheses: SQL05.ManufacturingPortal.dbo.[OPC.WriteRequests]

But maybe you have some part of the wrong name?

Answer 2

The reason you get the error is because you are not using a valid name. You seem to be referring to two schemes: dbo and OPC .

Valid syntax for server_name.database_name.schema_name.object_name as specified in

Answer 3

I used everything correctly, but the problem continued. My team was lower

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

I resolved it by first creating a table on the server, and then using insertion into a statement that ran without problems

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

Thanks, Sree

Answer 4

use with the symbol "[]" for the name and the remote database server like this. [87.247.678.80,1666]. [Danesfe]. [Admin1]. [Homefarsi]

Answer 5

I had similar problems when I received a message, when I tried to execute the following code (make table)

"into SalesCube_temp.SalesCube_temp.dbo.ALL_SUPPLIER_SALES_METRICS_I"

Then I realized that I used the wrong syntax and then straightened like

"into SalesCube_temp.dbo.ALL_SUPPLIER_SALES_METRICS_I"

THIS WORKS, THE END OF MY STORY. But I spent 10 to 15 minutes tr

Hope this helps someone.

Answer 6

In my case, he really wanted to be running on the server where the INTO table will live:

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

Answer 7

Please modify the tables from the very beginning and try pasting in the usual way.

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

Answer 8

I had the same problem. The problem was caused by the following error: instead of passing textbox1.text as the argument of the request called because of the code, I passed textbox1 .

Hope help