• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_22036718.html
0
mossmis
Asked:
mossmis
  • 3
  • 3
1 Solution
 
tigin44Commented:
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
0
 
Scott PletcherSenior DBACommented:
>> 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

0
 
mossmisAuthor Commented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

just DATABASENAME.SCHEMA.OBJECTNAME

since your databases are on the same server you dont need the first part...
0
 
tigin44Commented:
By the way I assume that the user running the query have the enough privilages on both databases...
0
 
mossmisAuthor Commented:
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?
0
 
mossmisAuthor Commented:
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.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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