December 8, 2005 at 8:27 am
Hi there, I have this situation:
I want to select data from a Sybase server based on criteria found in a table on a SQL Server. I have made an ODBC(or OLE DEB) connection to Sybase and a SQL Server connection. I cannot use a Data Transfer task(I think), because the tables to join are in 2 different databases. I can write SQL for it, if I could create a Linked Server connection, but security settings prevent me from doing that. I can connect from my desktop to the Sybase server and to the SQL Server, I cannot connect from the SQL Server to the Sybase server.
Is there a way, within a DTS package, to create something like:
INSERT INTO SQLSERVERDestTable
SELECT Field1, F2,...
FROM SQLSERVERTable1 T1, SybaseTable2 T2
WHERE T1.Field1 = T2.Field1
At the moment I think I have to use ASCII to export and then import the data.
Is that the only way in this situation?
Greetz,
Hans Brouwer
December 9, 2005 at 5:52 am
You can access either of the server using ADODB and trusted connection using activeX. Dump the required data in the staging tables using adoDB connection and then do simple insert once the data is on the required server and clear staging tables.
VK
December 12, 2005 at 8:20 pm
Hans, you are joining 2 tables from 2 different dbms you have to used linked server. I don't think there is another way.
one way you can get around....
if the sybase table is not too big...use data pump to copy data into temp table in sqlserver, then query your join.
December 13, 2005 at 2:09 am
Tnx for the replies. I am currently using tmpTables to put all needed tables in 1 DB. I was just wondering if it could be done in another way.
Greetz,
Hans Brouwer
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply