May 30, 2013 at 5:32 am
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
May 30, 2013 at 5:35 am
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;
May 30, 2013 at 6:03 am
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...
May 30, 2013 at 9:23 pm
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?
May 31, 2013 at 1:07 am
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 🙂
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 31, 2013 at 1:12 am
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
May 31, 2013 at 1:15 am
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?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 31, 2013 at 1:39 am
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.
"
May 31, 2013 at 1:49 am
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.
May 31, 2013 at 2:07 am
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.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 31, 2013 at 3:23 am
if i have 3 tables in my 2 different databases each having different number of columns, then I have to create 3 views to combine all of them???? or i can do this by creating single view?????
May 31, 2013 at 3:27 am
You can bring all of them using a single view. But the number of columns across the select statement should be the same.
May 31, 2013 at 3:42 am
bilalhazrvi57 (5/31/2013)
if i have 3 tables in my 2 different databases each having different number of columns, then I have to create 3 views to combine all of them???? or i can do this by creating single view?????
-- This will not work
SELECTCol1, Col2, Col3 -- 3 Columns selected here
FROMTable1
UNION ALL
SELECTCol1, Col2 -- 2 Columns selected here
FROMTable2
-- It has to be like below
SELECTCol1, Col2, Col3 ... Coln -- n number of Columns selected here
FROMTable1
UNION ALL
SELECTCol1, Col2, Col3 ... Coln -- n number of Columns selected here
FROMTable2
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
May 31, 2013 at 4:33 am
Ed Wagner (5/30/2013)
Assuming the tables you want to expose in a single view are all the same, have you tried something like this?
This is where this comment comes into play. When you UNION SELECT statements together to form a single return table, you must return the same structure for each one. In other words, query the same columns. You can use subqueries and CTEs to get around this, but the return columns from a single SELECT statement must match in both name and data type.
May 31, 2013 at 11:35 am
thanks to all of you brother
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply