September 10, 2008 at 8:36 am
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
December 15, 2008 at 1:25 pm
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!
October 10, 2011 at 11:43 am
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)
September 9, 2013 at 11:29 am
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
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply