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

Question

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 database. Any suggestions on how I can get around this?

Thanks

score 14 · Accepted Answer · answered Sep 30 '14 at 11:14

14

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?

answered Sep 30 '14 at 11:14

Arvo

10,349
1
31
34

+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. – Pradeep Kumar Sep 30 '14 at 11:31

undrline - Reinstate Monica · Answer 2 · 2022-07-12T16:14:21.167

14

The accepted answer is correct, in that the example given literally has too many prefixes. In the example, one prefix had a dot in its name, and needs to be set off by square brackets (or better yet, have a name that conforms to convention).

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]

edited Jul 12 '22 at 16:14

answered Jun 04 '19 at 19:10

undrline - Reinstate Monica

526
1
7
18

2

Having the source as linked server, instead of vice versa, worked for me as well. SELECT INTO where the destination is a linked server seems not to be a workable option. – Morten Snedker Oct 06 '20 at 10:53

score 6 · Answer 3 · answered Sep 30 '14 at 11:26

6

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.

answered Sep 30 '14 at 11:26

Taylor Buchanan

4,155
1
28
40

Hi, The table name is "OPC.WriteRequests"... I know - I didn't write it :-( ! – guyh92 Sep 30 '14 at 12:14
1

In that case, you just need to qualify the table name with square brackets like this `[OPC.WriteRequests]`. – Taylor Buchanan Sep 30 '14 at 12:49

score 6 · Answer 4 · answered May 18 '17 at 12:37

6

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

answered May 18 '17 at 12:37

sree

1,870
1
21
36

That's also what I thought. – Weihui Guo Oct 30 '18 at 14:20

score 2 · Answer 5 · answered Feb 23 '17 at 12:45

2

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

answered Feb 23 '17 at 12:45

Arashtala

21
1

score 1 · Answer 6 · edited Mar 15 '17 at 16:27

1

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.

edited Mar 15 '17 at 16:27

Dario

6,152
9
39
50

answered Mar 15 '17 at 13:17

praloy

11
1

in your example there is no linked server isn't it? – uzay95 Feb 14 '19 at 21:02

score 0 · Answer 7 · answered Nov 16 '20 at 05:10

My (inherited) code was using string.Format and passing the server name, eg

string.Format("Select * from {0}.AwesomeDB.dbo.EpicTable", WebConfigurationManager.AppSettings["MuhServerName"]);

but in the config, "MuhServerName" was "MyServer.MyDomain.com/MyInstance", so it was loaded as

Select * from MyServer.MyDomain.com/MyInstance.AwesomeDB.dbo.EpicTable

instead of

Select * from [MyServer.MyDomain.com/MyInstance].AwesomeDB.dbo.EpicTable

score 0 · Answer 8 · answered Aug 28 '21 at 21:03

0

I resolved basicly.

CREATE VIEW view_LINKSERVERTABLE AS   
SELECT * FROM LINKSERVERNAME.DBNAME.DBO.TABLENAME

and then

  INSERT INTO view_LINKSERVERTABLE SELECT * FROM database

answered Aug 28 '21 at 21:03

Umut ÜÇER

1

score -1 · Answer 9 · answered Oct 17 '18 at 07:52

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

score -1 · Answer 10 · edited Dec 18 '18 at 10:20

-1

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

edited Dec 18 '18 at 10:20

Bsquare ℬℬ

4,423
11
24
44

answered Dec 18 '18 at 07:32

Balaram tej

1

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

10 Answers10

Linked