How can I connect to an external database from a sql statement or a stored procedure?

Question

When running a SQL statement or a stored procedure on a database, can you connect to an external database and pull data from there?

something like:

SELECT a.UserID, b.DataIWantToGet 
  FROM mydb.Users as a, externaldb.Data as b

Answer 1

You'll need to setup a Linked Server instance. Then you can reference the external database as though it were a SQL Server database.

Answer 2

Yes, you can. You should take a look at linked servers for starters. You can also use OPENROWSET to hit them directly with no linked server.

Answer 3

Yep -- there's two methods: either use the function OPENROWSET, or use linked servers. OPENROWSET is useful for ad-hoc single statements, but if you're going to be doing this regularly, read up on linked servers as they allow you to do exactly what you've specified in your SQL Statement ... e.g.,

SELECT database.owner.table for local data
SELECT server.database.owner.table for remote data

And yes, you can mix and match to do joins twixt local and remote. Note though that you'll need to be caureul if you do joins against large tables that exist on the remote server as the query could take a long time to exexute...