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

Question

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

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

miltonmonrod SSC Journeyman Points: 91 More actions · Answer 1

I have this problem deleting a table

The object name 'PRODUCCIONREPORTES.DWIcbfSimReportes.dbo.TmpConsDenunciaPeticion' contains more than the maximum number of prefixes. The maximum is 2.

Command SQL

DROP TABLE PRODUCCIONREPORTES.DWIcbfSimReportes.dbo.TmpConsDenunciaPeticion;

forsqlserver SSCoach Points: 18902 More actions · Answer 2

I am getting same problem but I have to use it....How can I do...I want to use select into..

Thanks

vfan-1110470 Valued Member Points: 63 More actions · Answer 3

I found that you can prefix server name in From part.

For example, you connect to serverA

(Assuming you have added the linked serverB)

select * into [db].[owner].

from [serverB].[db].[owner].

This works.

But NOT in the one below, even you are connected to serverA:

select * into [serverA].[db].[owner].

from [serverB].[db].[owner].

This test is on SQL server 2008

rfrancisco Mr or Mrs. 500 Points: 544 More actions · Answer 4

I was searching for an answer for this error message and I found the following link that might help others:

http://www.sql-server-helper.com/error-messages/msg-117.aspx

http://www.sql-server-helper.com

Ivanna Noh SSCarpal Tunnel Points: 4138 More actions · Answer 5

an old thread i know....but I had a similar problem trying to do a select into over a linked server. To solve the problem, I ran the following query from the target database pulling the data from the database that is on the linked server:

SELECT field1, field2, field3, field 4

INTO myDatabase.mySchema.myTable --run the query from this db, using three part naming

FROM aLinkedServer.aDatabase.aSchema.aTable --this is the db on the linked server

michaeledmoore SSC Journeyman Points: 79 More actions · Answer 6

Assuming the servers have been set up as linked servers, you can run a query in the context of ServerB while still using a connection to ServerA (assuming having privileges on both databases and servers)

exec ServerB.DatabaseB.dbo.sp_executesql N'INSERT INTO ServerA.DatabaseA.dbo.TableA SELECT * FROM DatabaseB.dbo.TableB(or ViewB or FunctionB)'

To use with a function, you can do something similar to

exec ServerB.DatabaseB.dbo.sp_executesql N'INSERT INTO ServerA.DatabaseA.dbo.TableA SELECT * FROM DatabaseB.dbo.

FuncB(@input)',N'@input varchar(MAX)', @input='whatever'

This allows you to bypass issues when you need the 4 part name, but of course I would recommend testing your query directly on ServerB first, and then applying the above

ericb1 SSCrazy Points: 2354 More actions · Answer 7

I've also found that this works: EXEC(‘TRUNCATE TABLE [Test_DB].dbo.thisTable’) AT [Link-Server]

arthurz SSC Enthusiast Points: 111 More actions · Answer 8

One needs to use the square brackets [ ] in this case (one of the few cases when there is no escape).

So the incorrect syntax SERVER.DOMAIN.DATABASE.SCHEMA.TABLE must become

[SERVER].[DATABASE].[SCHEMA].

e.g.

SELECT TOP 1 *

FROM [MYSERVER.MYDOMAIN.MYCORP.MYCOMPANY.COM].[MYDATABASE].[dbo].[MYTABLE] ;