copy a table over to a linked server by ''SELECT ...INTO''

Question

copy a table over to a linked server by ''SELECT ...INTO''

Sergei Zarembo

Hall of Fame

Points: 3887
More actions
June 29, 2005 at 10:00 am

#362992

Hi,
I'm trying to make a backup copy of a table '_all_props' on my local MS SQL Server 2000 by copying it to a linked server on a network. The linked server is called 'beta', destination db is 'work'. When I execute statement
select *
into beta.work.dbo.Table_backup
from _all_props
I'm receiving message:
Msg 117: The object name 'beta.work.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
Any idea how to accomplish the copying by code in Query Analyzer, other than by doing it through DTS Import/Export Wizard in SQL Server Enterprise Manager?
Thanks,
Sergei

Viewing 15 posts - 1 through 15 (of 27 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I would then presume that this operation is not doable that way. Have you tried creating the table on the other server and simply inserting into it?

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 2

the reason I asked is that I have a vague recollection that it can be accomplished that way, fast with just a couple of lines - I did it before but simply forgot the details. Alternative (though slower) way is to use DTS Manager. This morning I ended up doing exactly that.

Thank u

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Slower might be a strong word here. If you know your wizards, it takes less than 1 minute to do that. Now how fast can you type .

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 4

it's mostly clicking in the DTS wizard pointing to objects - boring to death. I'd better typa smth in QZ, or use a template.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Well hopefully someone will know how to do it. Good luck.

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 6

Sergei Zarembo

Hall of Fame

Points: 3887

June 29, 2005 at 10:52 am

#569448

same to u

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Actually, I was doing something along these lines on a linked server and ran into the same problem. I too will be interested to know if there another way to identify a table on a linked server by using 4 prefixes.

I wasn't born stupid - I had to study.

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 8

something like

select * from beta.data_base.dbo.table_name

works just fine, the problem starts when u attempt to create the new table on a linked server remptely from a local server.

Farrell,

what do u mean by '4 prefixes' ? How can it be? Pls give an example. I thought 3 prefixes is a max.

th

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Select * from (LinkedServer).(Database).(Owner).(Table/view)

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 10

cant you use this syntax

insert

into beta.work.dbo.Table_backup

select * from _all_props

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

No he wants Select into. Insert would do fine but he doesn't want to have to create the table first.

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 12

Remi:

i was under impression tha 'prefix' is defined as 'identifier before an entity'

ie in yr query above (post 2626) there are 3 prefixes before the table name, or in other words, the table name has 3 prefixes.

Farrell Keough SSCoach Points: 17414 More actions · Answer 13

Remi was correct in interpretting my meaning. I should have said three (3) prefixes instead of four; I erroneously counted the table name as a prefix. Dopey me.

I wasn't born stupid - I had to study.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

That could be correct :

(LinkedServer).(Db).(Owner).(Table).Column = 4 prefixes .