use more than one connection - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

use more than one connection

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-10-07 : 04:13:48

i am sorry of my english.

i create a query in "Execute SQL Task"-SSIS, that use of tables from 2000 ,and 2005,how can i do it?

Is It exist something like that :
[Connection].dbo.[DB_Name].[Table_Name])-i use it in JOIN.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 04:17:11

you mean joining tables from two different server? for that you need to bring both the servers data to single server. (use a staging table in one server and get others data in it). then use the second server as connection for sql task and just join staging table and other table to get your output.
sequence will be as follows
1.create staging table in server2
2.dump data from server1 table to staging in server2
3.use sql task with connection to server2 and join staging table and table in it.

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-10-07 : 05:02:40

i don't mean to that,i have a lot of tables,so i need to bring all the data?
i want just to connect to other tables in second server with join.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 05:12:54

quote:
Originally posted by inbs

i don't mean to that,i have a lot of tables,so i need to bring all the data?
i want just to connect to other tables in second server with join.

using sql task you can connect to only one server at a time which is determined by connection. were you trying to loop through servers to get data from all?

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-10-07 : 05:51:56

i dont understand you,i know that "sql task " it is for one connection.and this id my problem,i dont know how to loop,

this is example:
I work on SERVER_1

SELECT a.XX,b.YY
FROM [SERVER_1].[DB_1].dbo.[Table_1] a Join [SERVER_2].[DB_2].dbo.[Table_2] b On a.AA=b.BB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 06:05:12

quote:
Originally posted by inbs

i dont understand you,i know that "sql task " it is for one connection.and this id my problem,i dont know how to loop,

this is example:
I work on SERVER_1

SELECT a.XX,b.YY
FROM [SERVER_1].[DB_1].dbo.[Table_1] a Join [SERVER_2].[DB_2].dbo.[Table_2] b On a.AA=b.BB

thats what i told. you cant do like above unless you've added the SERVER_2 as a linked server to SERVER_1. thats why i asked you to create a staging table for Table_2 (say Table_2_Staging) on SERVER_1 in [DB_1]. then first populate data from Table_2 to Table_2_Staging by means of data flow task .then use sql task with connection to server_1 and use like below

SELECT a.XX,b.YY
FROM dbo.[Table_1] a Join dbo.[Table_2_staging] b On a.AA=b.BB

inbs
Aged Yak Warrior

860 Posts

Posted - 2008-10-07 : 07:10:07

ok,but i dont want to add data to server.cause it isnt neccessary except to JOIN.

i can handle with one table but i expect that will be more than one.

do you say that only choise that i have is to transfer the table from server_2 to server_1?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 07:24:56

quote:
Originally posted by inbs

ok,but i dont want to add data to server.cause it isnt neccessary except to JOIN.

i can handle with one table but i expect that will be more than one.

do you say that only choise that i have is to transfer the table from server_2 to server_1?

Only choice if you want this to be done by SQL task.
Alternatively you could use data flow and add two OLEDB sources one to SERVER_1.[DB_1].table1 and other to SERVER_2.DB_2.Table_2 and use merge join task to join data based on columns AA & BB. then use joined data and populate any table or file using OLEDB destination or flat file destination.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-07 : 10:16:47

An alternative to using a linked server is to use OPENROWSET and a DSN. All you need is to set up the DSN on the server you are querying from. The advantage of this method is you can connect to different types of databases also (I am using it to connect to an Ingres database from SQL Server). You can use the results of the query as a derived table which you can join to another table as any normal table on your current server (or another derived table querying a table on a different server. You can then run it directly as a query / stored proc / job without hassle of SSIS.

SELECT *
FROM OPENROWSET('MSDASQL','DSN_NAME';'username';'password',
'select * FROM xxx.xxxxx ') AS a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources