June 24, 2004 at 4:06 pm
I am executing the sql statement against the oracle link server
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SELECT * FROM OPENQUERY(MyOracle, "SELECT * FROM vw_some WHERE ID IN (
'1',
'2',
'3',
'4')
")
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
If i give less id's it works fine but if i give more ID list (like 1000) i get the following error
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'SELECT * FROM OPENQUERY(MyOracle, "SELECT * FROM vw_some WHERE ID IN (
'1',
'2',
'3',.
am i doing wrong?. Please help me if you have any ideas.
Thanks,
June 25, 2004 at 6:54 am
Instead of quoted identifiers try changing using regular single quotes and remove from around the numbers the qutes altogether and see what happens.
June 25, 2004 at 10:38 am
I am not sure, but there could be a small limit on the size of the query in OPENQUERY, such as 255 characters. Try putting the Where id in () outsize the openquery() function to test this. EG
SELECT * FROM OPENQUERY(MyOracle, "SELECT * FROM vw_some") WHERE id in ('1', '2', ....)
Regards
Peter
June 28, 2004 at 6:06 pm
Just for a laugh see if there is a limit in the different syntax
SELECT * FROM MyOracle..SchemaOwner.VW_SOME WHERE DI in (blahblahbalh..
Stick to Upper Case too....
DB
The systems fine with no users loggged in. Can we keep it that way ?br>
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply