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

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...

Answer 3

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 4

Easiest way :

Click connect to server
when it asks for server name use: 192.168.X.X,1433SQLEXPRESS insted of YOURPCSQLEXPRESS

(The ip and opened port of target sql server)

Type correct username and password
Done!

友情链接