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

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

Three Table Join in SQL Server Database

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
Favorable
Asked:
Favorable
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
AnuTijiCommented:
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
 
FavorableAuthor Commented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
hspoulsenCommented:
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
 
DrewKjellCommented:
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
 
FavorableAuthor Commented:
Thanks to everyone.
0
 
FavorableAuthor Commented:
Thanks to everyone.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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