CODEWITHSUNDEEP
sqlsql-serverdatabaset-sqllinked-server
Matt
Matt

Reputation: 5595

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

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

Upvotes: 9

Views: 49185

Answers (4)

Disco4uf
Disco4uf

Reputation: 147

Easiest way :

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

(The ip and opened port of target sql server)

  • Type correct username and password
  • Done!

Upvotes: -2

Callie J
Callie J

Reputation: 31296

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

Upvotes: 4

Scott Ivey
Scott Ivey

Reputation: 41568

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.

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332571

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

Upvotes: 13

Related Questions