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 Monday, February 4, 2008 12:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Post #451031
Posted Monday, February 4, 2008 1:45 AM
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 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



Post #451041
Posted Monday, February 4, 2008 2:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 5, 2008 8:47 AM
Points: 15, Visits: 4
Just a thought Hugh, you might want to try posting at http://www.sageforum.co.uk

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

Post #451049
Posted Wednesday, March 5, 2008 8:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 16, 2008 6:07 AM
Points: 3, Visits: 22
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
Post #464487
Posted Wednesday, March 5, 2008 9:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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

Post #464513
Posted Wednesday, September 10, 2008 8:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, February 8, 2014 5:15 AM
Points: 1, Visits: 40
This is a brute force approach to getting all the records in from a Sage table into SQL Server.
I'm not an expert so it's not elegant either.

I'm assuming that THIS_RECORD in the Sage tables is the primary key.
Once you have created a linked server to Sage (I've called it Sage2008), then you can use
SELECT * INTO tSALES_LEDGER
FROM OPENQUERY(Sage2008, 'SELECT * FROM SALES_LEDGER')

to dump all the records into a table in SQL Server (mine is tSALES_LEDGER)

Then use a while loop to specify a value for the missing record THIS_RECORD value- i.e.
INSERT INTO tSALES_LEDGER
SELECT * FROM OPENQUERY(Sage2008, 'SELECT * FROM SALES_LEDGER WHERE THIS_RECORD = 4')

where the 4 would be increased to range from 1 to Max(THIS_RECORD)

This is the key point. If you specify which record you want, it will return it despite it being missing in the first insert.

The only problem is that I could not pass a variable to the OPENQUERY statement, so I used the EXECUTE method. Here is the code:-

DECLARE @strSQL varchar(max)
DECLARE @strSQL1 varchar(max)
DECLARE @THIS_RECORD_MAX as int
DECLARE @THIS_RECORD as int

SET @strSQL1 = 'INSERT INTO tSALES_LEDGER' + CHAR(13)
SET @strSQL1 = @strSQL1 + 'SELECT * FROM OPENQUERY(Sage2008, ''SELECT * FROM SALES_LEDGER WHERE THIS_RECORD = '
--3'')'

SET @THIS_RECORD_MAX = (SELECT THIS_RECORD_MAX FROM OPENQUERY(Sage2008, 'SELECT MAX(THIS_RECORD) AS THIS_RECORD_MAX FROM SALES_LEDGER'))
SET @THIS_RECORD = 1

WHILE @THIS_RECORD <= @THIS_RECORD_MAX
BEGIN
IF @THIS_RECORD NOT IN(SELECT THIS_RECORD FROM tSALES_LEDGER)
-- If the value is already in tSALES_LEDGER then don't add it again
BEGIN
SET @strSQL = @strSQL1 + CONVERT(varchar(max),@THIS_RECORD) + ''')'
--PRINT (@strSQL)
EXECUTE (@strSQL)
PRINT (@THIS_RECORD)
END

SET @THIS_RECORD = @THIS_RECORD + 1
END

Post #566977
Posted Monday, December 15, 2008 1:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 20, 2010 1:23 AM
Points: 16, Visits: 80
We have the same damn problem here. Our linked servers (SQL Server 2005 64bits) point to Oracle.
Did you notice that the amount of missing records is always 99?

Also, when you specify a 'where' clause inside the openquery statement, it will bring the missing record.
A colleague pointed out that is the first records that are missing. He tried "SELECT TOP 100 * FROM OPENQUERY..." and guess... 1 ROW AFFECTED!
What is this?! It's driving me crazy!
Post #619947
Posted Monday, October 10, 2011 11:43 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 14, 2014 4:18 AM
Points: 2, Visits: 46
Hi All,

I know this is an old post, but I have recently hit this problem too.
This seems to work to overcome the 99 records being lost.
The trick is to cater for the 99 records that will be lost by adding extra ones at the beginning of the table to be sent

SELECT *
FROM OPENQUERY(oracle_linked_server,
'select * from
( select a.n, b.* from
( SELECT 1 n FROM dual CONNECT BY LEVEL < 110 ) a
cross join
( select * from {oracle_table} where rownum = 1 ) b
) c
union all
select 2, d.* from {oracle_table} d
')
AS oracle_table_with_all_records
WHERE (N = 2)
Post #1188051
Posted Monday, September 9, 2013 11:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 13, 2013 3:47 AM
Points: 1, Visits: 5
To complement, this only seems to happen with regular users. Querying the same datasource with a sysadmin user results in ok queries. Still in our processes we have regular users, and can't always have powerusers to do certain work.

Its strange 1st row is returned, then 99 ommited:
SELECT * FROM OPENQUERY(linkedServerName, '
SELECT level FROM dual CONNECT BY LEVEL < 110 '
)

Thanks a lot to Geoff for his suggestion.
Since we know 1st record is retrieved and then 99 omited, so generate and request 99+ records (cross join to maintain columns for the union in 2nd step) and tag with 1,
then union that with all records and tag with 2,
and finally select all tagged with 2.

I had an issue with the brackets had to use curly brackets. So with a tweak, it worked:

SELECT *
FROM OPENQUERY(linkedServerName,
'select * from
(select a.n, b.* from
(SELECT 1 n FROM dual CONNECT BY LEVEL < 110 ) a
cross join
( select * from OracleTableOrView where rownum = 1 ) b
) c
union all
select 2, d.* from (tf_OracleTableOrView) d
')
AS oracle_table_with_all_records
WHERE (N = 2)



Cheers
P
Post #1492882
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse