NullifyNetwork

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')