Linked Servers without using Linked Servers

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 2005 Forums

Transact-SQL (2005)

Linked Servers without using Linked Servers

Author

Topic

TommyTT
Starting Member

2 Posts

Posted - 2008-01-24 : 08:49:25

Hi,

I've got three servers, A, B and C with a database on each server containing data I want.

What I would ideally like to do is run something similar to...


SELECT Column1, Column2 FROM [SERVERA].[Database1].[dbo].[TableName]
UNION
SELECT Column1, Column2 FROM [SERVERB].[Database1].[dbo].[TableName]
UNION
SELECT Column1, Column2 FROM [SERVERC].[Database1].[dbo].[TableName]

but unfortunately we can't use linked servers.

I know you can change databases with the USE command but does anyone know a similar command for changing servers? At least then I connect dump everything into a central location and use that.

Thanks in advance
TommyTT

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-01-24 : 08:56:36

well one way is to connect to each server from .net union the data there.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-24 : 08:58:25

Make use of OPENDATASOURCE() or OPENROWSET() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

TommyTT
Starting Member

2 Posts

Posted - 2008-01-24 : 09:23:43

harsh_athalye,

OPENROWSET was exactly what I was looking for, thanks.

If I could I'd buy you a beer.

TommyTT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources