Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4076
  • Last Modified:

Cross Server Query

Is there a way, besides creating a linked server, to enable cross server queries? I have two servers; ServerA and ServerB. I want to take the top 10000 row from TableA on DatabaseA on ServerA and insert them into a new TableB on DatabaseB on ServerB. Example:
USE DatabaseB
GO
 
SELECT TOP 10000 
INTO TableB 
FROM [ServerA].[DatabaseA].[dbo].[TableA]

Open in new window

0
computerstreber
Asked:
computerstreber
3 Solutions
 
brad2575Commented:
The only way to do this without using a linked server is to export the data from Server A and then import the data into Server B.

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could use OPENROWSET() which is like a linked-server-on-the-fly...
0
 
ErnariashCommented:
You could create a SSIS package to do your task or use (my choice)
OPENROWSET or OPENDATASOURCE funtions that will provide you with ad hoc connection information as part of a four-part object name without using a linked server name.  Please check that SQL Server may blocked access to STATEMENT OpenRowset and OpenDatasource. Ad Hoc Distributed Queries component is turned off as part of the security configuration. Only system administrators can enable the use of Ad Hoc Distributed Queries (use sp_configure.)
 

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=YourServer;User ID=yourUser;Password=pass'
         ).YourDataBase.dbo.YourTable
 
SELECT a.*
FROM OPENROWSET('SQLNCLI', 'Server=YourServer;Trusted_Connection=yes;',
     'select * from YourDataBase.dbo.YourTable') AS a

Open in new window

0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now