The object name contains more than the maximum num

问题:

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

回答1:

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?

回答2:

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.

回答3:

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

回答4:

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

回答5:

I had similar issues where I was getting the message when I was trying to execute the following code(make table)

"into SalesCube_temp.SalesCube_temp.dbo.ALL_SUPPLIER_SALES_METRICS_I"

I then Realized that I was using the incorrect Syntax and then rectified as

"into SalesCube_temp.dbo.ALL_SUPPLIER_SALES_METRICS_I"

IT WORKED, END OF MY STORY. But I spent nearly 10 to 15 minutes tr

Hope it will help somebody.

回答6:

Please change the tables from the start and try to insert with the normal 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 

回答7:

I had the same issue. The problem was due to the following mistake: instead of passing textbox1.text as argument to the query being called from code-behind i passed textbox1.

Hope it helps