Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Linked server does not return all rows Expand / Collapse
Author
Message
Posted Friday, January 6, 2006 8:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2008 8:47 AM
Points: 15, Visits: 4
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

Post #248925
Posted Friday, January 6, 2006 10:52 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Thursday, July 10, 2014 1:34 PM
Points: 6,623, Visits: 1,855
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #248993
Posted Friday, January 6, 2006 11:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2008 8:47 AM
Points: 15, Visits: 4
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

Post #249025
Posted Monday, January 9, 2006 10:00 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
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.


Post #249492
Posted Friday, January 13, 2006 1:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2008 8:47 AM
Points: 15, Visits: 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

Post #250753
Posted Monday, January 16, 2006 7:21 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
Thanks for following up with a solution - nice to know how ppl work around things


Post #250934
Posted Monday, January 16, 2006 10:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:47 AM
Points: 1,388, Visits: 6,265
What servicepack are your sql servers running at? SP3 or SP4?
Post #251021
Posted Monday, January 16, 2006 10:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2008 8:47 AM
Points: 15, Visits: 4
SP3 - I'll stick SP4 on there and see if it makes a difference!


Regards,

Steve

Post #251031
Posted Monday, January 16, 2006 1:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2008 8:47 AM
Points: 15, Visits: 4
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

Post #251076
Posted Sunday, February 3, 2008 2:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 4, 2008 2:21 AM
Points: 2, Visits: 3
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
Post #450962
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse