June 8, 2023 at 3:10 pm
Replacing an old server with a newer one. A job that runs fine on the old, refuses to work on the new.
Old 2012, new 2019. Both servers (VM) have the same proc, memory, network and disk space. The job calls a procedure on the sql side, that calls procedures on a linked Oracle server. Linked server tests fine and queries fine for everything else. I have checked provider settings between old and new and they are the same (allow inprocess enabled).
When the job starts it immediately gets a PREEMPTIVE_COM_DATA error message, then it proceeds to beat the CPU until I kill it.
After digging down, it seems that when it goes to read a view that is an openquery select statement to the Oracle Database using DISTINCT is the cause. You can query that table fine, add where clause etc and it returns instantly. But once you add the DISTINCT to it (even running manually) as soon as you press go for the query it will flash the OLEDB wait type then immediately pops the PREEMPTIVE_COM_DATA wait type and will never finish(I have left it overnight just to be sure).
I am stumped other than there might be an issue with the newer version of SQL Server or Oracle driver.
June 9, 2023 at 4:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 12, 2023 at 3:41 pm
Replacing an old server with a newer one. A job that runs fine on the old, refuses to work on the new. Old 2012, new 2019. Both servers (VM) have the same proc, memory, network and disk space. The job calls a procedure on the sql side, that calls procedures on a linked Oracle server. Linked server tests fine and queries fine for everything else. I have checked provider settings between old and new and they are the same (allow inprocess enabled).
When the job starts it immediately gets a PREEMPTIVE_COM_DATA error message, then it proceeds to beat the CPU until I kill it.
After digging down, it seems that when it goes to read a view that is an openquery select statement to the Oracle Database using DISTINCT is the cause. You can query that table fine, add where clause etc and it returns instantly. But once you add the DISTINCT to it (even running manually) as soon as you press go for the query it will flash the OLEDB wait type then immediately pops the PREEMPTIVE_COM_DATA wait type and will never finish(I have left it overnight just to be sure).
I am stumped other than there might be an issue with the newer version of SQL Server or Oracle driver.
Have you attempted to re-write the query to eliminate the use of DISTINCT?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply