Linked server does not return all rows

Question

Linked server does not return all rows

steve-204902

SSC Eights!

Points: 867
More actions
January 6, 2006 at 8:59 am

#167575

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

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

steve-204902 SSC Eights! Points: 867 More actions · Answer 2

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

Ian Yates SSCoach Points: 19738 More actions · Answer 3

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.

steve-204902 SSC Eights! Points: 867 More actions · Answer 4

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

Ian Yates SSCoach Points: 19738 More actions · Answer 5

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 6

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

steve-204902 SSC Eights! Points: 867 More actions · Answer 7

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

Regards,

Steve

steve-204902 SSC Eights! Points: 867 More actions · Answer 8

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

Hugh-629119 SSC Journeyman Points: 90 More actions · Answer 9

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

steve-204902 SSC Eights! Points: 867 More actions · Answer 10

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

Hugh-629119 SSC Journeyman Points: 90 More actions · Answer 11

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

steve-204902 SSC Eights! Points: 867 More actions · Answer 12

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

russ lunn Old Hand Points: 387 More actions · Answer 13

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

steve-204902 SSC Eights! Points: 867 More actions · Answer 14

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