Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
milford_morgan
milford_morgan
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
Having the same problem, but I need to use BULK INSERT. I will be inserting > 150K records and I don't think anything else is as good as Bulking.

BULK INSERT MYLINKEDSRV.myDatabase.dbo.BCPTable
FROM 'C:\list.csv'
WITH (FORMATFILE = 'C:\format_file.xml')


Milford
SQLRO
SQLRO
SSC-Enthusiastic
SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)SSC-Enthusiastic (146 reputation)

Group: General Forum Members
Points: 146 Visits: 230
Can I alter a table using Linked server?
shajuseb
shajuseb
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1
Hi, We used to backup few SQL Server 2005 tables into MS Access file. The access file is accessed by using linked server.


sp_addlinkedserver 'AccessBackup', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:\Shaju\AccessBackup\InputDB.mdb'

sp_addlinkedsrvlogin 'AccessBackup', false, 'sa', 'Admin', NULL

1) SELECT * FROM [AccessBackup]...[MailingList]

Works fine.

2) SELECT *
INTO [AccessBackup]...[Cities]
FROM [dbo].[tblCities]

Iam trying to back up tblCities to access db. The error i get is
The object name 'AccessBackup...Cities' contains more than the maximum number of prefixes. The maximum is 2.


3) INSERT INTO [AccessBackup]...[Cities]
SELECT * FROM [dbo].[tblCities]

The error i get is
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessBackup" does not contain the table "Cities". The table either does not exist or the current user does not have permissions on that table.'

How to use select into a linked server
shajuseb
shajuseb
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 1
Hi, We used to backup few SQL Server 2005 tables into MS Access file. The access file is accessed by using linked server.


sp_addlinkedserver 'AccessBackup', 'Access 97', 'Microsoft.Jet.OLEDB.4.0', 'D:\Shaju\AccessBackup\InputDB.mdb'

sp_addlinkedsrvlogin 'AccessBackup', false, 'sa', 'Admin', NULL

1) SELECT * FROM [AccessBackup]...[MailingList]

Works fine.

2) SELECT *
INTO [AccessBackup]...[Cities]
FROM [dbo].[tblCities]

Iam trying to back up tblCities to access db. The error i get is
The object name 'AccessBackup...Cities' contains more than the maximum number of prefixes. The maximum is 2.


3) INSERT INTO [AccessBackup]...[Cities]
SELECT * FROM [dbo].[tblCities]

The error i get is
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "AccessBackup" does not contain the table "Cities". The table either does not exist or the current user does not have permissions on that table.'

How to use select into a linked server
Wilhelm Josef Ratte
Wilhelm Josef Ratte
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 98
i got this error when i tried to call a function from a linked server in my select query. here's what caused my problem:

set @myquery = 
'select
PurchClaims =' + @database + '.dbo.fn_PurchClaimAmount(bb.Pinvno,b.Blockno),
from #sp_agingAgingCopy_Blocks as bb
INNER JOIN ' + @database + '.dbo.Blocks b on bb.blockno = b.blockno



so in other words it seems that while you can select from a linked server table, you cannot call functions from that linked server within the select.
in this case @database was "[192.168.0.2\dev].bs_bkup_sa_global_jan"
the alternative i had to use to still get the data was to add another column, "Purchclaims" to my #sp_agingAgingCopy_Blocks table, then i had to create a cursor or query before this select to insert those values.
miltonmonrod
miltonmonrod
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
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
forsqlserver
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1222 Visits: 2045
I am getting same problem but I have to use it....How can I do...I want to use select into..

Thanks
vfan-1110470
vfan-1110470
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 2
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].[table]
from [serverB].[db].[owner].[table]
This works.

But NOT in the one below, even you are connected to serverA:
select * into [serverA].[db].[owner].[table]
from [serverB].[db].[owner].[table]

This test is on SQL server 2008
rfrancisco
rfrancisco
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 13
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
Ivanna Noh
SSC Eights!
SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)SSC Eights! (956 reputation)

Group: General Forum Members
Points: 956 Visits: 2911
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search