Solved

Three Table Join in SQL Server Database

Posted on 2011-09-16
7
215 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
7 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 84 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 83 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 3

Accepted Solution

by:
hspoulsen earned 250 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 83 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql 2014,  lock limit 5 37
ServiceCenter IR Query Expressions 1 41
performance query 4 32
Filtering characters in an SQL field 2 16
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In this article I will describe the Copy Database Wizard 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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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