?
Solved

select into remote table

Posted on 2003-03-18
16
Medium Priority
?
226 Views
Last Modified: 2012-06-21
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?
 
0
Comment
Question by:adumas77
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +5
16 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8161096
And the error is ...?

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8161105
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
0
 

Author Comment

by:adumas77
ID: 8161110
The object name 'destinationservername.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 75

Expert Comment

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

Anthony
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8161181
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')
0
 

Author Comment

by:adumas77
ID: 8161223
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')
0
 
LVL 2

Expert Comment

by:gdbjohnson
ID: 8161373
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)?

0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8161637
--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')
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8161861
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
0
 
LVL 1

Expert Comment

by:pdrg
ID: 8166922
Workaround - use DTS?
0
 

Author Comment

by:adumas77
ID: 8168974
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
0
 
LVL 13

Accepted Solution

by:
ispaleny earned 100 total points
ID: 8169655
No other database-level operations or statements are allowed on linked servers.

Use
exec master.dbo.xp_cmdshell 'isql ...'

See topic "External Data and Transact-SQL" in BOL
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8169852
ispaleny has the right source regarding the list of statements that can be executed against linked servers.

Also, please do not forget your many open questions.  You will find experts more responsive if you do so.  Here are listed your open questions:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20359724.html
http://www.experts-exchange.com/Web/Web_Languages/JavaScript/Q_20350188.html
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20450211.html
http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20369302.html
http://www.experts-exchange.com/Databases/Oracle/Tools_Development/Q_20369303.html

The following questions do not have any contributions and should be deleted.  Post a message in Community Support at http://www.experts-exchange.com/Community_Support/ to this effect:
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20390760.html
http://www.experts-exchange.com/Web/Web_Languages/JavaScript/Q_20330670.html

Thanks,
Anthony
0
 

Expert Comment

by:CleanupPing
ID: 9275750
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.
0
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183464
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
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question