asked on

select into remote table

Hi... I'm getting an error in trying to execute the following:

select * into destinationservername.database.[owner].new_tablename from tablename

Any ideas in how to fix it?

Anthony Perkins

And the error is ...?

Anthony

Anthony Perkins

Also, please maintain your many open questions. For the record:
Questions Asked 18
Last 10 Grades Given A A A A A A A A A
Question Grading Record 9 Answers Graded / 9 Answers Received

Thanks,
Anthony

adumas77

ASKER

The object name 'destinationservername.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.

Anthony Perkins

Is "destinationservername" a linked server? If not try linking (check out the stored Procedure sp_addlinkedserver) this server to the current database.

Anthony

EugeneZ

1."SELECT INTO" will not work, in your case
-------------------------

2. use "INSERT INTO"

----------------------
3. from destinationservername

select * into #t from openquery(sourceservername,'select * from pubs..authors')

adumas77

ASKER

Eugene, thanks for the reply, but I'm a little lost on the syntax you provided....

Taking your example, what is #t refer to: (the destination linked server name?)

select * into #t from openquery(sourceservername,'select * from pubs..authors')

gdbjohnson

You should be able to use the

'destinationservername.database.dbo.TABLENAME' syntax

but perhaps you haven't started the Distributed Transaction Coordinator in SQL (it's under the Support Services folder)?

EugeneZ

--1. We run it on Destination server
----------
--2. 'SourceServerName' - linked server
---on destination server
--I use for this example #Temporary Table - you can use: -----or temp. or perm. table on Destination server

SELECT * INTO #TempTable
FROM
OPENQUERY(SourceServerName,'SELECT * FROM pubs..authors')

Anthony Perkins

There are some SQL commands that do not accept linked servers. "Truncate Table" and User Defined Functions also come to mind. You may want to look at workarounds, such as reversing the order of the linked servers:

select * into new_tablename from sourceservername.database.[owner].tablename

Anthony

pdrg

Workaround - use DTS?

adumas77

ASKER

Thanks all for the replies, but I need to run the script from the source server;

and I need to use truncate table among other things

ASKER CERTIFIED SOLUTION

ispaleny

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

CleanupPing

adumas77:
This old question needs to be finalized -- accept an answer, split points, or get a refund. For information on your options, please click here-> http:/help/closing.jsp#1
EXPERTS:
Post your closing recommendations! No comment means you don't care.

monosodiumg

No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
Accept: ispaleny http:#8169655

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer