Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Creating views on multiple tables Expand / Collapse
Author
Message
Posted Thursday, May 30, 2013 5:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:06 AM
Points: 14, Visits: 17
Hi,
Is it possible to create views using multiple tables from multiple databases????
I have 4 servers which are connected remotely and the same database resides on all the servers, I want to create view by using all the databases of all the servers??
I want to do this to make the database available for the user regardless of the connection that they connect to which server?? means data must be available to users either they connect to one server or other????
your valuable guidelines and suggestions are welcome.
Thanks in advance
BILAL AHMAD
Post #1458154
Posted Thursday, May 30, 2013 5:35 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 12:28 PM
Points: 4,611, Visits: 4,067
Assuming the tables you want to expose in a single view are all the same, have you tried something like this?

CREATE VIEW BillsView
AS

SELECT field_list
FROM [server,port].database_name.dbo.table_name
UNION
SELECT field_list
FROM [server2,port2].database_name2.dbo.table_name;




Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1458156
Posted Thursday, May 30, 2013 6:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:36 AM
Points: 199, Visits: 740
hi,

in SQL you can reference to each object using a 4-part name: [Server].[Database].[Schema].[Table]
[Server] has to be registered as a Linked Server, for details have a look at books online (http://msdn.microsoft.com/en-us/library/ms190479.aspx)

Depending on the amount of data the table on the other servers contain and the kind of view you might create (using joins over several servers) the peformance might be very poor.
Depending on what you actually have on data and what you actually need there might be other solutions than views...

Post #1458173
Posted Thursday, May 30, 2013 9:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:06 AM
Points: 14, Visits: 17
Hi Bills,
Thanks a lot for your kind suggestions. Look you told me to do this, that one table from server1 union with one table from server 2. It works quite good, my I am looking for this,
that more than one table from server 1 and more than one table from server 2, is it possible?????

create view gvWDatabase
as
select * from Accounts
union
select * from Account_benefits
union
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits

Look here the first two tables Accounts and Accounts_benefits are from the server to which currently I am logging and the next two lines
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits
that two tables Accounts and Account_benefits are from the remote server Lahore which is registered as remote server.
I want to add more than one table from one server and more than one table from 2nd server, is it possible???
Please reply?
Post #1458514
Posted Friday, May 31, 2013 1:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
bilalhazrvi57 (5/30/2013)
Hi Bills,
Thanks a lot for your kind suggestions. Look you told me to do this, that one table from server1 union with one table from server 2. It works quite good, my I am looking for this,
that more than one table from server 1 and more than one table from server 2, is it possible?????

create view gvWDatabase
as
select * from Accounts
union
select * from Account_benefits
union
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits

Look here the first two tables Accounts and Accounts_benefits are from the server to which currently I am logging and the next two lines
select * from Lahore.PakistanPostOffice.dbo.Accounts
union
select * from Lahore.PakistanPostOffice.dbo.Account_benefits
that two tables Accounts and Account_benefits are from the remote server Lahore which is registered as remote server.
I want to add more than one table from one server and more than one table from 2nd server, is it possible???
Please reply?


If it works for one table from another server, it should definitely work for more than one tables as well



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1458535
Posted Friday, May 31, 2013 1:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:06 AM
Points: 14, Visits: 17
hi,
yes it works for one tables means it can access data from remote server and combine with currently logged server's table, but when i want to add two tables in view of current server and of remote server than it gives the error
Post #1458537
Posted Friday, May 31, 2013 1:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
bilalhazrvi57 (5/31/2013)
hi,
yes it works for one tables means it can access data from remote server and combine with currently logged server's table, but when i want to add two tables in view of current server and of remote server than it gives the error


What is the error you are receiving?



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1458540
Posted Friday, May 31, 2013 1:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:06 AM
Points: 14, Visits: 17
That is the error which i receives
"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
"
Post #1458548
Posted Friday, May 31, 2013 1:49 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 9, 2014 3:16 AM
Points: 450, Visits: 72
That error is correct only. You have to check whether there are equal number of columns in each of those tables. You can otherwise create CTE and post all the records in them.
Post #1458549
Posted Friday, May 31, 2013 2:07 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 1:51 AM
Points: 2,693, Visits: 4,755
bilalhazrvi57 (5/31/2013)
That is the error which i receives
"All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
"


Please check the number of columns in your SELECT's
It should be equal in all your queries that you are trying to combine in your View.



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1458555
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse