May 27, 2019 at 11:42 am
Hi all,
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.
Please help me, I'm desperate. I appreciate it.
May 27, 2019 at 6:26 pm
Just a guess here but if it takes 15 minutes to run on the actual target DB there is a good chance your linked server connection is hitting a time out threshold. Depending on the driver many connections have a time out built in. Check your ODBC connections on the server. Also I wouldn't be doing a 'SELECT *' over the network like that. You will likely experience some serious network waits depending on the size of the expected result set. Try the same query with a specific record first and see what comes back.
May 28, 2019 at 8:02 am
the query runs for about 15 min and gives results.
And you expect 900k rows, that's gonna take a while, so you probably get a network timeout.
You could also minimize the transferred data to count the number of rows, for test.
select [NumberOfRows] = count(1) from ....
Later replace * with a list of needed columns.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply