How to query data from one SQL DB from another SQL DB

Posted on 2011-10-27
Last Modified: 2012-05-12
I have a single SQL server with several databases on it. There are two databases I am actively using. There is a stored procedure I want to create that gathers certain data from one database and dumps into temporary tables into another. Or to better rephrase the question I want to be a be to run a query from one database that gathers data from another SQL database that is on the same server. How can I go about this? Some of the examples I found  so far are not very clear.

ie. This was hard to follow if it is applicable:
Question by:mossmis
    LVL 26

    Expert Comment

    sql server uses four part naming convention...


    so from database A you may refenece to a table from database B as

    SELECT *
    FROM B.dbo.tableName
    LVL 68

    Expert Comment

    >> I want to create that gathers certain data from one database and dumps into temporary tables into another. <<

    You don't necessarily need to copy into the second db.  You can just reference the original data in its original db, even in JOINs, for example:

    SELECT t1.col1, t2.col3, ...
    FROM dbo.table_in_same_db t1
    INNER JOIN other_db.dbo.table_in_other_db t2 ON
        t2.join_col = t1.join_col


    Author Comment

    I get this error trying tigin44's solution:

    Could not find server 'mySQLServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver

    Is running that procedure necessary or Did I do something wrong?  What kind of permissions do I need to assign to the user to the second database if I will be only reading from it?
    LVL 26

    Accepted Solution

    if you examine my post in the example I used 3 part...


    since your databases are on the same server you dont need the first part...
    LVL 26

    Expert Comment

    By the way I assume that the user running the query have the enough privilages on both databases...

    Author Comment

    OK, I think I'm almost there. the DATABASENAME.SCHEMA.OBJECTNAME, seems to be working ,however I don't have the right permissions setup for the user. I have been trying to add this user to have access to to second database but I'm getting:

    The server principal "SQLUSER" is not able to access the database "NEWSQLDB" under the current security context.

    I only want this user, SQLUSER, to have read access. Where should I be adding this?

    Author Closing Comment

    The "DATABASENAME.SCHEMA.OBJECTNAME" utimitley worked. I was having authentications issue as I was trying to use mixed mode Windows/SQL authentication and my SQL user could not connect to the second database.  I ended up using only windows authentication and I was able to connect to both. I guess using windows authentication is  more secure; just I little more work giving specific users access.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    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…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how the fundamental information of how to create a table.

    737 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

    17 Experts available now in Live!

    Get 1:1 Help Now