SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked server does not return all rows


Linked server does not return all rows

Author
Message
steve-204902
steve-204902
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 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
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (39K reputation)

Group: Moderators
Points: 39116 Visits: 1917
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
steve-204902
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 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
Ian Yates
Ian Yates
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7196 Visits: 445
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
steve-204902
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 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
Ian Yates
Ian Yates
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7196 Visits: 445
Thanks for following up with a solution - nice to know how ppl work around things



Jo Pattyn
Jo Pattyn
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11651 Visits: 10173
What servicepack are your sql servers running at? SP3 or SP4?
steve-204902
steve-204902
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 4
SP3 - I'll stick SP4 on there and see if it makes a difference!


Regards,

Steve
steve-204902
steve-204902
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 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
Hugh-629119
Hugh-629119
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search