Agent Job to Oracle Linked Server Runs Infinitely - Not Failing - No Results - Solution

Today we had a issue reported from one of the development server that a T-SQL Code when placed inside a job runs for infinite time but the same Query runs fine when executed from the Management Studio.

When I checked the code it was just a simple SELECT INTO Query which fetches record from Oracle and inserts into SQL Server.

Since this query works fine from Management Studio then it is prooved that Linked Server Connectivity is fine and also when we test linked server it succeded.

Solution:
Existing Linked Server was created with "Oracle OLEDB Provider" OraOLEDB.Oracle so for testing purpose we created a linked server with MSDAORA (Microsoft Provider) and found that Job is running fine.

We were able to Session details in Oracle when we use MSDAORA but there is no session details atleast when we use OraOLEDB.Oracle.

With this it is confirmed that connectivity has not yet started from SQL Server to Oracle.. So got doubt on provider (Plan came for reinstalling) but when we checked the settings of the provider which gave the rootcause... And it was.... "Allow InProcess" was not selected :(

So after selecting this and restarting SQL Server once Job started working fine and issue resolved!!!!

For your Reference:


Incase you want to try it in T-SQL (Particularly for SQL Express):
/* Set up Allow In Process on SQL 2005 */
EXEC master.dbo.sp_MSset_oledb_prop N'OraOLEDB.Oracle', N'AllowInProcess', 1