Search code examples
sqlsql-serverlinked-serveropenquery

Why is the openquery returning 0 rows on SQL server but has 900k rows in the target Server?

Need help to figure ut why the remote query I execute on the server return 0 rows , but the same query returns over 900k rows in the target DB.

the string is less 8000 characters long so I won't post it here. but this is the sctructure basically:

declare @SQL varchar(MAX);
declare @D varchar(15);
declare @Per varchar(15);
declare @NextPer varchar(15);
declare @NextYPer varchar(15);
set @D = N'01-JUN-2019'
set @Per = N'2020004';
set @NextYPer = N'2021004'
set @NextPer = N'2020005'
set @SQL = N' SELECT  ...... '
set @SQL = N'select * from openquery ([LK1], "'+@SQL+'")';
execute( @SQL);
print @SQL;

Note: the linked server works and is used on other openqueries with shorter strings successfully. I tried using EXECUTE (@SQL) AT and I still get 0 rows. When i exexute the print output directly on the Oracle DB , the query runs for about 15 min and gives results.

Solution

  • Thanks all for the input.

    The root cause is simply the format of the Date parameter, which didn't run correctly on the linked server. All I had to do is change my query to use this:

    SO_BOOK_DATE < to_date(''@D'' , ''DD-MON-YYYY'')

    instead of

    SO_BOOK_DATE < ''@D'' .