You can do this using the OPENROWSET function in MS SQL Server, so:
SELECT * FROM OPENROWSET('MSDASQL', 'ConnectionString', 'SELECT * FROM mytable')
Which is great in a view for data consolidation - but even better is to remove the need for a connection string by connecting just once!
You can do this using sp_addlinkedserver and sp_addlinkedsrvlogin. Requirements will change depending on what you want to connect to - if you need help with a particular one feel free to e-mail me.
You can then simply use the new 'server' you just set up as so:
SELECT * FROM OPENQUERY(servername, 'SELECT * FROM mytable')Permalink