Solved

Three Table Join in SQL Server Database

Posted on 2011-09-16
7
214 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 142

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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 …

831 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