asked on

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

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:
https://www.experts-exchange.com/questions/22036718/Connect-another-SQL-Server-Within-select-Query.html

tigin44

sql server uses four part naming convention...

SERVERNAME.DATABASENAME.SCHEMA.OBJECTNAME

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

SELECT *
FROM B.dbo.tableName

Scott Pletcher

>> 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

mossmis

ASKER

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?

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tigin44

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

mossmis

ASKER

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?

mossmis

ASKER

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.