Linked server does not return all rows

  • 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:

    Update STOCK

    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.

    Thanks.


    Regards,

    Steve

  • What happens when you perform a four-part naming convention on the select like SELECT * FROM SAGE.<database>.<owner>.STOCK ? Same # of rows?

    K. Brian Kelley
    @kbriankelley

  • Hi Brian,

    I can do SELECT * FROM SAGE...STOCK and it again returns only 1983 rows. There doesn't seem to be anything in common with the records it misses out and as I said if I add WHERE STOCK_CODE = and specify one of the missing records it comes up OK so I'm confused!

    One more thing that I've increasingly noticed is I often get an error when trying new queries in query analyzer:

    [OLE/DB provider returned message: Row handle referred to a deleted row or a row marked for deletion.]

    and have to restart SQL Server and then the query will run OK. I have used Sage ODBC drivers just with Access for a few years and they can be rather slow but always brought up the records I wanted. One thing I am wondering about is the fact that as far as SQL server is concerned there is no primary key on the Sage tables so I'm wondering if that could be affecting things.

    DTS transformations work perfectly though so they must use the ODBC connection differently. I just don't know if there's any way of running my price update query within a DTS package.


    Regards,

    Steve

  • Perhaps the SAGE ODBC drivers are not very friendly for use in a threaded server environment?  You could create a DTS task that imported the data into some staging table and then have DTS run some SQL script which does your UPDATE statement joining against the staging table.  Then have your script or DTS drop/clear the staging table.

  • Thanks Ian, Sage's ODBC has always been a bit quirky and from what I've read Sage blame Microsoft and Microsoft blame Sage!

    I'd already tried running a data transformation task and importing the data into temp tables but or just over 2000 rows it took 30 - 40 seconds to complete and multiple users would be running it frequently which wasn't acceptable to the users so I've eventually ended up with a data diven query task in DTS that updates the SQL table using a join with the live Sage tables.

    Updates take around 1 or 2 seconds now so we can live with that.


    Regards,

    Steve

  • Thanks for following up with a solution - nice to know how ppl work around things

  • What servicepack are your sql servers running at? SP3 or SP4?

  • SP3 - I'll stick SP4 on there and see if it makes a difference!


    Regards,

    Steve

  • Installed SP4 and querying the linked server still does not return all the rows.

    I'll stick to my data driven query in DTS - seems like a cleaner solution anyway.


    Regards,

    Steve

  • Hi

    I have just come up with this problem when using a linked server to Sage Line 50 V12. If anyone has found a solution it would be great if you could let me know.

    Thanks

    Hugh

  • Hi Hugh,

    I ran out of time trying to solve the problem and ended up setting up a few DTS packages to import/update SQL tables which I created as a mirror image of the Sage tables and then my application uses these tables so there's no need to have all the linked server stuff going on. These DTS packages run every 15 minutes so it's not exactly live and wouldn't suit every application but for what I needed it has worked fine.


    Regards,

    Steve

  • Hi Steve

    Thanks for getting back to me.

    I will give Sage a call today and see if they have done anything about this.

    Thanks for the advice regarding DTS. I guess this will work for us as well, so at least I can get something working.

    Regards

    Hugh

  • Just a thought Hugh, you might want to try posting at http://www.sageforum.co.uk[/url]

    There's quite a few guys that do Sage development hang out there and at least one of them used to be a developer at Sage.


    Regards,

    Steve

  • Hi,

    Did you get anywhere with this,

    I am trying to get sqlserver 2005 to import sales_ledger from sage

    and it only returns 512 of 613 records. no explanation why, no errors.

    by the way, Excel manages to read all 613 records, using msquery.

    so im assuming its an issue with SQL Servers openrowset command??

    Regards,

    Russ

  • I still use DTS to copy the data from Sage into copies of the Sage tables that I created.

    It does import all the records though but I am using SQL 2000 rather than 2005 as my system has been running for a good few years.

    If it's any help Microsoft Access has trouble linking to Sage data if you specify one of the Sage columns as a primary key and this can cause it not to pull any records out. I've never found found if this was the fault of Access or the Sage ODBC drivers or just behaviour by design.


    Regards,

    Steve

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply