I have a SQL database of stock items and I want to update the price of these items from Sage Line 50 accounts.
I have used ODBC to setup a linked server to Sage and am usng the following query to update the SQL table:
Set SALES_PRICE = sage.SALES_PRICE
from STOCK as sql , openquery(SAGE, 'select STOCK_CODE, SALES_PRICE from STOCK') as sage
where sql.STOCK_CODE = sage.STOCK_CODE
This query seemed to work OK but I noticed it wasn't updating all records so I ran:
Select * from openquery(SAGE, 'select * from STOCK')
And realised it was only return 1983 rows when there should have been 2082 stock records. I then ran a query to select some of the missing stock items individually and they all came out ok but again when I did select * they were missing.
I then created a transform data task in DTS and imported all 2082 records sucessfully into SQL so the problem just seems to be selecting all records from the linked server.
Does anyone have any idea what the problem is or can anyone advise me how to run the same update prices query using a DTS package as DTS seems more reliable than the linked server?
I know I could delete the stock table and re-import all the stock records from Sage but as this takes over 30 seconds and as multiple users may be accessing the stock table frequently then I'd prefer to just run an update on the existing stock table rather than delete it and do a full re-import of stock using a data transformation task.