Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

The object name ... contains more than the maximum number of prefixes. The maximum is 2. Expand / Collapse
Author
Message
Posted Thursday, July 10, 2008 6:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 12, 2008 6:08 PM
Points: 1, 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
Post #532072
Posted Tuesday, September 9, 2008 7:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 22, 2014 8:03 AM
Points: 135, Visits: 216
Can I alter a table using Linked server?
Post #566148
Posted Sunday, February 15, 2009 2:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 15, 2009 1:54 AM
Points: 2, 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
Post #657307
Posted Sunday, February 15, 2009 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 15, 2009 1:54 AM
Points: 2, 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
Post #657308
Posted Wednesday, February 25, 2009 1:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 3:40 AM
Points: 1, Visits: 72
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.
Post #664072
Posted Friday, March 20, 2009 3:46 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 25, 2009 8:48 AM
Points: 1, 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;
Post #680749
Posted Tuesday, July 7, 2009 12:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 6:20 PM
Points: 1,202, Visits: 1,928
I am getting same problem but I have to use it....How can I do...I want to use select into..

Thanks

Post #748322
Posted Tuesday, October 13, 2009 8:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 13, 2009 8:41 AM
Points: 1, 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
Post #802165
Posted Tuesday, October 26, 2010 6:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, July 12, 2011 1:32 PM
Points: 30, Visits: 11
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
Post #1010753
Posted Monday, January 31, 2011 7:28 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 8:03 PM
Points: 830, Visits: 2,463
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

Post #1056560
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse