Cross Server Query

Posted on 2008-11-11
Last Modified: 2012-05-05
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



INTO TableB 

FROM [ServerA].[DatabaseA].[dbo].[TableA]

Open in new window

Question by:computerstreber
    LVL 16

    Assisted Solution

    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.

    LVL 142

    Accepted Solution

    you could use OPENROWSET() which is like a linked-server-on-the-fly...
    LVL 9

    Assisted Solution

    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   *
             'Data Source=YourServer;User ID=yourUser;Password=pass'
    SELECT a.*
    FROM OPENROWSET('SQLNCLI', 'Server=YourServer;Trusted_Connection=yes;',
         'select * from YourDataBase.dbo.YourTable') AS a

    Open in new window


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now