Transferring Data w/ non linked servers

[twifosp]

I have 2 databases existing on 2 seperate servers. We'll call them A and B.

I need to get data FROM B, to A, and also base the rows FROM B on criterea from A.

In other words, I have a table on A with a list of IDs that I want. So I need the source table from B to talk to this table on A. Problem is, the servers aren't linked, and I can't link them due to my I.T. constraints.

I can't bring over the entire table from b, as it's too large to fit on A. So I need only the rows I have specific in a table on A.

How do I design a DTS package to read the rows from A, and then specify a query on B to use said rows?

 

[yksvaan]

I think it should be able to get this done with Data Driven Query Task(which is not easy), but it would mean that dts would move all of the data of B through network to the package-executing server, and check each row if it belongs to the A list. Then again, if DTS would be running on server B, it wouldn't be a problem.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[twifosp]

You say if the DTS was running on server b it wouldn't be a problem, how so?

I can probably run it on b.

 

[yksvaan]

My comment is not an issue of logic, but of resources:
Then the amount of data wouldn't have to be transmitted over the network, since it is on B. I take it that there's a lot of rows. If amount of data is not a burden to this effort, no matter where the package is run, it has no difference to the logic of data driven task.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[twifosp]

I tried setting up a data driven query and every time I give it sql to specify rows it complains of no linked servers. Not sure that is going to work.

 

[yksvaan]

You need two connections at first, one for each server.

What was the exact message?

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[twifosp]

I have both connections set up.

Same message if you tried to select to a server that isn't linked to the one you're on:

Could not find server 'server' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

 

[yksvaan]

Do you have the linked server definition on server A pointing at server B even though you can't use it ? Take it off, if it is not needed. You need valid login/password for B to make that connection in DTS, not a linked server.

I'll be back in about 10 hrs.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[twifosp]

I can't link the servers at all.

Actually I solved the problem by doing a manual OLEDB connection using OPENROWSET

 

[yksvaan]

Great, I didn't think of that option.

So you don't need to use DTS at all in this case.

Cheers