[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 196
  • Last Modified:

Query tables in another database

Hi,
   i have connected to database X. from this connection i would like to access tables in another database. purpose is to join tables in different database.

Regards,
Rao.
0
mohanrao
Asked:
mohanrao
  • 2
1 Solution
 
graf0Commented:
If the databases are on the same server you can just use the syntax:
dbname.owner.table
to query the table residing in another db.

If the database are on the separate servers, you have to set up one server as linked server on the other (this can but does not have to be bi-directional link), and then use the syntax:
servername.dbname.owner.table

0
 
mohanraoAuthor Commented:
yes, it is two different servers. how can i do this "Linked server setup"
0
 
graf0Commented:
Best way is to use the Enterprise Manager. Linked servers are in Security branch. If your other server is SQL Server as well, it is fairly easy to set up it as linked server.
0
 
JulianvaCommented:
Go to enterprise manager

under security - you will see linked servers

right click on linked server
click on ne linked server
dialog box will open up
in linked server - type the name of the server you want to link
click on security tab
click radio button - they will be mapped to

this is for the remote server
remote user - username
password

at the bottom

login name - enter login name - example sa
remote user - enter username - example sa
when it has successfully created a link
you will see the name of the server
under that you will see tables - nothing else

when you need to query the database from query
analyzer

servername.databasename.dbo.tablename








0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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