?
Solved

OPENDATASOURCE

Posted on 2016-07-14
8
Medium Priority
?
54 Views
Last Modified: 2016-09-22
How would i use OPENDATASOURCE to insert the records from a table in one database table to an identically named table in another Sql server instance on another server?
0
Comment
Question by:vbnetcoder
[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
  • 3
  • 3
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 41711386
INSERT INTO yourTargetTable
SELECT *  
FROM OPENDATASOURCE( ...)
0
 
LVL 70

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 41712687
Why use OpenDataSource? You do not want set up a linked server? In that case you'll need the correct connection string, as always. Assuming you need to use SQL Login instead of Windows Authentication:
insert into Tbl select * from OpenDataSource('SQLNCLI', 'Server=SourceServer\SourceInstance; User=sa; Password=sapwd').SourceDB.dbo.Tbl

Open in new window

0
 

Author Comment

by:vbnetcoder
ID: 41712731
I don't know all the details yet but i was told we maybe not be able to use linked server
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 70

Expert Comment

by:Qlemo
ID: 41712741
So is the info above sufficient?
0
 

Author Comment

by:vbnetcoder
ID: 41712744
OLemo.  Likely, i will check it out latter today.
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 41811092
#a41712687 is the answer, as far as we can tell. There is not much more to add to that.
0
 

Author Closing Comment

by:vbnetcoder
ID: 41811102
ty
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 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