Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AntonJ
Starting Member
2 Posts |
 Posted - 2006-01-06 : 06:54:07
|
| Is it possible to include tables from two connections in the one query ?I am trying to execute an SQL task. The Sql in question needs to access tables from two different databases, both SQL server but existing in different instances. The problem I have is that you are only able to specify a single connection in the properties box and the SQL then assumes the tables come from that connection.Specifying the full db string, eg. [conection].[dbo].[tablename] doesn't work in that an error is displayed when you try to save the query.Anton |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2006-01-06 : 07:00:40
|
Hi Anton - Have you tried using open rowset in your sql command.Here is an example of useSELECT mt1.*FROM dbo.MyTableServer1 mt1 INNER JOIN OPENROWSET('SQLOLEDB','Server2';'Logon';'password', 'SELECT * FROM DBName.dbo.MyTableServer2') AS mt2ON mt1.Col1 = mt2.Col1Duane.  "It's a thankless job, but I've got a lot of Karma to burn off." |
 |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-01-06 : 07:07:34
|
| Can also use OPENDATASOURCEand OpenQuery if both the servers are linkedCheck BOL for more info on these |
|
|
|
AntonJ
Starting Member
2 Posts |
Posted - 2006-01-06 : 10:33:31
|
| The OPENROWSET works brilliantlyHowever there is a further complication in that I need to be able to parameterize the server, database and password. I have been able to do this ok for the rest of my package but how to do this is eluding me.Any ideas welcome.Anton |
|
|
|
|
|
|
Execute Query across 2 connections