?
Solved

Three Table Join in SQL Server Database

Posted on 2011-09-16
7
Medium Priority
?
218 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

770 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