[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Three Table Join in SQL Server Database

Posted on 2011-09-16
7
Medium Priority
?
220 Views
Last Modified: 2012-08-14
Here is my scenerio, I have three separate Windows 2008 Servers not clustered and are running sql and each has the following:

Server A: Database supporting Web application with the following:
                TableName: Manager
                     ManagerID
                     ManagerName


Server B: User databases
                 TableName: Manager
                     ManagerID
                     ManagerName


Server C: User databases
                  TableName: Manager
                     ManagerID
                     ManagerName


Now the question is this----I want to create a View on Server A with the Manager data, having a three table joins, so the Web App can always query the view on Server A without having to touch the tables on other instances, definately the view is going to be three table joins, how can this be achieve without having to set up a Linked Server???

Remember, I'm only retrieving data from one table each.





0
Comment
Question by:Favorable
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 336 total points
ID: 36548654
that is not a "join", but a UNION ALL for the query.

you can do that without a linked server, but still you need to query the "other server", with OPENROWSET() function which defines the connection string that you would use in a linked server scenario.
q: why not using linked servers?
0
 
LVL 4

Assisted Solution

by:AnuTiji
AnuTiji earned 332 total points
ID: 36548684
Hi

To connect to different database server instead of using linked server, the following query can be used

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=server name;User ID=user_id;Password=Password'
         ).database_name.dbo.table_name
0
 

Author Comment

by:Favorable
ID: 36548751
Remember, these are differents Servers, and the View will be created one time on the Web Application database on Server A and anytime the View is query on server A it will retrieve similar data from Server B, and Server C.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 3

Accepted Solution

by:
hspoulsen earned 1000 total points
ID: 36548914
Create a linked server on ServerA for each of the servers you want to connect to.

CREATE VIEW ManagerSrvB as
   select * from ServerB.databasenamehere.dbo.Manager
go
and
CREATE VIEW ManagerSrvC as
   select * from ServerC.databasenamehere.dbo.Manager
go

and then a view that unions all tables and views together.

CREATE VIEW TotalManager as
   select ManagerID, ManagerName from Manager
   Union all
   select ManagerID, ManagerName from ManagerSrvB
   Union all
   select ManagerID, ManagerName from ManagerSrvC
go

It will not be really fast, so if you have a lot of rows that you want to pull over, or if you want to do it very often, it might be better to copy changes to the ManagerSrvB table into a new table on ServerA.
It uses a bit of disk space, and you have to be able to detect updates and deletes, but you can index the new table and get fast results.

Best regards,
Henrik Staun Poulsen
Stovi Software

0
 
LVL 9

Assisted Solution

by:DrewKjell
DrewKjell earned 332 total points
ID: 36548972
To do what you want there is no way to not use a Linked server on Server A that connects to an instance of both Server B and Server C.  If you wanted to do a one time load into a table on Server A from Server's B and C that is a possibility, however if you want all the information to stay in sync then you will need the data all stored on one server or you will need to create linked servers.

If there is something I'm not understanding about your request please respond to the thread, or if there is anything that can be clarified.

HSPoulsen has laid out the steps for you to accomplish what you have asked.

Drew
0
 

Author Comment

by:Favorable
ID: 36892292
Thanks to everyone.
0
 

Author Closing Comment

by:Favorable
ID: 36892306
Thanks to everyone.
0

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

656 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