Access multiple data servers

Posted on 2007-07-30
Last Modified: 2011-04-14
I am using MS SQL 2005.

We have 35 databases split on 2 servers PS-SQL and PS-SQL-02

I need to do a query that accesses data from both servers.  I am looking for the syntax to help with this.

Is there an issue with having - in the server name.

Thanks John
Question by:John_R_PETS
    LVL 11

    Assisted Solution

    by:Ved Prakash Agrawal
    There is no issue.

    you can create the Linked Server. I can say the name of your linked serer is  LinkesPSSQL02

    SELECT * from LinkesPSSQL02.database.dbo.TableName
    LVL 75

    Accepted Solution

    use Fully Qualified name

    SELECT * FROM  [ServerName].[dbName].[ObjectOwner].TableName

    Author Comment

    Hi aneeshattingal:

    I get this error

    Msg 10054, Level 20, State 0, Line 0
    A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

    when i run SELECT * FROM  [PS-SQL].[Wholesale].[DBO].Stocktrans

    I need to run a query that unions between 2 databases - one on each server.


    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    As Ved17Nov mentioned in his post, you need to create a linked server first.

    Author Comment

    Can you advise how to create the link.  My knowledge of this is quite limited
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    759 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

    8 Experts available now in Live!

    Get 1:1 Help Now