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

Error in initializing provider. ORA-12154: TNS:could not resolve service name while connecting to Oracle from SQL Server in SSAS, SSRS, DTSExec 64-bit

Error:
SQL 2005 64bit Installed on Windows 2003 64bit Connecting to Oracle 10g 64 bit using Oracle 10g client 64bit throws below errors while connecting from SSIS or Visual Studio or Reporting Services or Analysis Services

Error 1: Error in initializing provider. Catastrophic failure

Error 2: Error in initializing provider. ORA-12154: TNS:could not resolve service name

Error 3: Not all properties can be set. Window Handle (BAD VALUE)

Root Cause:
64-bit Microsoft OS install's 32-bit applications into the following location
"C:\Program Files (x86)\..."

Same 32-bit application when installed in 32-bit OS the typical location will be
"C:\Program Files\..."

Notice that there is an extra bracket in the path, This causes an existing networking bug to occur in Oracle Client driver where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle Database.

Solution:
We can use the Short Name Naming option available in Windows to overcome this problem. There is also another option of copying the "Visual Studio Folder" to C:\ and then design the package but it is not suggested!

Below image shows you what is short name path for Visual studio folder:


Sol 1. Create two batch files in C:\ with contents as
"start /B "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"" - Name it as "Start BIDS.bat"

"start /B "C:\Progra~2\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE" "C:\Progra~2\Microsoft SQL Server\90\Tools\binn\VSShell\Common7\IDE\SqlWb.exe"" - Name it as "Start Mgmnt. Studio.bat"

I assume that your visual studio is installed in C:\. You can replace devenv.exe with dtsexec.exe or whichever exe like TOAD which is creating this connectivity issue...

Now double click the batch file to open the visual studio and now if you test the connection, It will work perfectly!

Instead you can also use NTFS Junction Point to create a short path like C:\VS to point to C:\Program Files(x64)\Microsoft Visual Studio 8\Common7\IDE\) and then use C:\VS\devenv.exe here in the batch script. Download NTFS Junction Point

(OR)

Sol 2. If you have access to metalink then you can download below patches
5059238 - Patch for (x86) issue
http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=5059238

4751549 - "opatch"
http://updates.oracle.com/ARULink/PatchDetails/process_form?patch_num=4751549

Also once you have created the package and when you trying to schedule it, you will get the same error... So what you can do is copy the command line from the job created and then create a new job will below parameters and replace the command line in the below sytax with command line copied from created job
xp_cmdshell 'C:\PROGRA~2\MICROS~2\90\DTS\Binn\DTExec.exe ....command line'

How to get shortnames for Windows Directory?
Go to CMD and then for whichever the folder you want the shortname type
DIR PROGR*.* /x
This will give you the lengthy name and short name for the folders!

Comment here incase you are still unable to resolve this issue!

Links:
1. The Adventures of SSIS and Oracle in 64bit Land..!
2. Oracle Drivers on an x64 Box for SSIS Development
3. Connecting to Oracle on 64-bit (x64) machine
4. Configuring Connection from MS SQL 2005 (x64) to Oracle 10g (x64) on Windows 2003 Server (x64)
5. How To Troubleshoot an ASP-to-Oracle Connectivity Problem(Some of steps are general)
6. Workaround for Oracle Connectivity Issues with SSIS on X64
when deploying Package developed in 32-bit

IDBInitialize::Initialize returned 0x80004005 Error while running query against linked server to Access or Excel

Are you receiving below error when you run a Sql Server Job which connects to MS Access or Excel through Linked Server?

"Executed as user: . OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed."

Don't worry you have come to the right place!

To resolve this issue you need to first understand how Linked Server in SQL Server works.

Whenever a query is executed against linked server, SQL Server creates a temporary DSN for linked server in the TEMP Directory of the login using which SQL Server Agent Service is running.

For Example:

If Sakthi\SQLUser is the login account configured for SQL Server Agent, then by default the TEMP directory for this user will be C:\Documents and Settings\SQLUser\Local Settings\Temp

So SQL Server will create a temporary DSN here while performing Linked Server Operations, Now if you don't have permission to access this folder and when you are trying to execute a query against linked server then you will receive the error message stated above.

Solution:

1.Log on to the computer(Remote Desktop Login) by using the SQL Server start up account. (Ex:Sakthi\SQLUser)
2.Create a folder named Temp in the operating system installation directory.(Ex:C:\Temp)
3.Permit full access to a non-administrator account on the Temp folder. (i.e Add "Everyone" and give full permission)
4.Set the value of the TEMP and TMP user variables of the SQL Server startup account to the newly created Temp folder. To do so, follow these steps:
a. Right-click My Computer, and then click Properties.
b. Click the Advanced tab, and then click Environmental Variables.
c. In the User variables for Logon User list, click TEMP, and then click Edit.
d. In theVariable Value box, type C:\Temp as the location of the new Temp folder, and then click OK.
e. Repeat steps c and d to set the value of the TMP variable.
f. Click OK two times.
5.Log off, and then log on to the computer by using SQL Server startup account.
6.Restart the SQL Server services.

Now if you run the job or query which is creating this issue you will get result as SUCCESS!