2005 Linked Server - Erratic Query Results

[osjohnm]

Hi all

Quick background, clientsite was using SQL2000 and we had a DTS package which imported data from DB2/AS400 and then updated data in SQL2000. Something went haywire on server and can no longer access package.

Clients solution is to upgrade to 2005 within 2 weeks.

Clients didn't install SSIS on testserver so I attempted to use a linked server. Used the IBM Client Access drivers and ODBC dsn already created. (Our web app connects straight to DB2/AS400 via this connection)

I was able to connect and query data from the linked server using the 4-part syntax.

eg. select * from x.x.x.x

This returned +/- 53 records from the table. The table contains 643 records in DB2 (couldn't confirm this, AS400 vendors not playing ball)

Execute the same query and it would just hang there. Cancel and execute again and returns 53 records.

If I did the following:

insert into x
select x from x.x.x.x

It would insert 643 records. Did the same with another table which inserted 2740 records but query this table via linked server and you only get 89 records.

I'm not aware of any limits on data returned via linked servers in 2005?

Have I missed something?

Thanks

 

[mrdenny]

There are no limits on returning data. I'm not sure where the truncating of the data would be happening. SQL 2005 does still support DTS packages. They are located under Managment > Legacy > Data Transformation Services

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com