so, We have a AS400, wich contains live data from our stores. Here we work on SQL Server, but we need access to the live data. So we want to transfer data every night from the 400 to one of our databases in SQL Server. So we made a Linked Server, wich works fine. But there is, somewhere, a buffer that wont let us transfer a big load of data at the same time. for example
SELECT * FROM OpenQuery(MyLinkServer, 'SELECT * FROM SYNTAXPF."NU.POHDR"')
this returns 862 rows.
SELECT * FROM OpenQuery(MyLinkServer, 'SELECT PHDIVX FROM SYNTAXPF."NU.POHDR"')
this returns all 1719 rows from the table.
So there must be a buffer between the 2 servers, but i cant seem to fin how to remove it, or at least make it bigger. I had to create a stored wich 5 tables from the 400 and put the information into 1 table in SQL Server, but there is an awsome load of records in these 5 tables, so i was forced to make a cursor wich loops on suppliers, and this takes aver a half hour to execute, since we have close to 5000 suppliers, but we only receive stock from like 2-3% of them, so my loop execute a heck of a lot of select statement that are actually useless, and that takes a lot of time to execute. i would prefer to remove that stupid buffer, and simply make one select that will retur all the rows from the 5 tables. it would take like 3 minutes to execute...
Is this question related to Integration Services? If you are using Integration Services to transfer data, you don't need to use Linked Server, you can connect SSIS to AS400 directly and move data to SQL Server.
If this is not related to SSIS (as you did not mention SSIS in the question), please repost it to correct forum, I think SQL Data Access is the best for Linked Server questions: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=87&SiteID=1
|||well, it is not related to SSIS, but, I wont repost somwhere else as I found the solution bu myself. It was an option i havent seen at first, abd now it works well. thanks anyway
No comments:
Post a Comment