Query Multiple Servers and Create a single View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Multiple Servers and Create a single View

Author

Topic

mshsilver
Posting Yak Master

112 Posts

Posted - 2014-12-10 : 08:20:54

Hi there,
I have several SQL 2008 servers and on one of those servers I want to create a view that runs multiple queries that connects to the other SQL 2008 servers and pulls that data all into one view. The SQL query structures are exactly the same.

Below is a query that I have been managed to run a query connecting to two server but I can’t create a view from one database.

Any ideas would be appreciated.

Thanks for looking.

--- Server 1
:CONNECT 2.2.2.2,port -U sa -P pass

SELECT
[CONTACTID]
,[WCE_UID]
,[WCE_DISPLAYNAME]
,company
,EMAILADDRESS
,[WCE_ALIAS]
,[WCE_NAMED]
,WCE_LOCKED
,[LASTLOGIN]
FROM [AppServer2_Sys].[dbo].[wces_users] users
join ATSolutions_Site..wce_contact ats on users.CONTACTID = ats.uniqueid
where (WCE_LOCKED is null or WCE_LOCKED = '')

union all

SELECT
[CONTACTID]
,[WCE_UID]
,[WCE_DISPLAYNAME]
,company
,EMAILADDRESS
,[WCE_ALIAS]
,[WCE_NAMED]
,WCE_LOCKED
,[LASTLOGIN]
FROM [AppServer2_Sys].[dbo].[wces_users] users
join bitesize_site..wce_contact bit1 on users.CONTACTID = bit1.uniqueid
where (WCE_LOCKED is null or WCE_LOCKED = '')

GO

---- SERVER 2

:CONNECT 3.3.3.3,port -U sa -P PASSWORD!

SELECT
[CONTACTID]
,[WCE_UID]
,[WCE_DISPLAYNAME]
,company
,EMAILADDRESS
,[WCE_ALIAS]
,[WCE_NAMED]
,WCE_LOCKED
,[LASTLOGIN]
FROM [AppServer6_Sys].[dbo].[wces_users] users
join Avalon_Site..wce_contact AV on users.CONTACTID = AV.uniqueid
where (WCE_LOCKED is null or WCE_LOCKED = '')

union all

SELECT
[CONTACTID]
,[WCE_UID]
,[WCE_DISPLAYNAME]
,company
,EMAILADDRESS
,[WCE_ALIAS]
,[WCE_NAMED]
,WCE_LOCKED
,[LASTLOGIN]
FROM [AppServer6_Sys].[dbo].[wces_users] users
join BoxCoUK_Site..wce_contact bx on users.CONTACTID = bx.uniqueid
where (WCE_LOCKED is null or WCE_LOCKED = '')

GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-10 : 12:49:44

You'll want to research distributed views: http://technet.microsoft.com/en-us/library/ms188299%28v=sql.105%29.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources