• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

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?
 
0
adumas77
Asked:
adumas77
  • 5
  • 3
  • 2
  • +5
1 Solution
 
Anthony PerkinsCommented:
And the error is ...?

Anthony
0
 
Anthony PerkinsCommented:
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
 
adumas77Author Commented:
The object name 'destinationservername.database.dbo.' contains more than the maximum number of prefixes. The maximum is 2.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Anthony
0
 
Eugene ZCommented:
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
 
adumas77Author Commented:
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
 
gdbjohnsonCommented:
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
 
Eugene ZCommented:
--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
 
Anthony PerkinsCommented:
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
 
pdrgCommented:
Workaround - use DTS?
0
 
adumas77Author Commented:
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
 
ispalenyCommented:
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
 
Anthony PerkinsCommented:
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
 
CleanupPingCommented:
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
 
monosodiumgCommented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 5
  • 3
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now