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

OLEDB Vs ODBC Which is the best driver for connecting Oracle from SQL Server using Linked Server or DTS packages

What is difference between OLEDB and ODBC: OLE DB is not intended to be a replacement for ODBC. ODBC, or Open Database Connectivity, is a well-established standard for connecting to relational databases. It has been extended a little bit to connect to Excel spreadsheets and text files, but for the most part; it is designed for connecting to relational databases. However, OLE DB can access relational databases as well as nonrelational databases. Typically, your data is stored all over the corporation. There is data in your mail servers, directory services, spreadsheets, and text files. OLE DB allows SQL Server to link to these nonrelational database systems. For instance, if you want to query, through SQL Server, the Active Directory on the domain controller, you couldn't do this with ODBC, because it's not a relational database. However, you could use an OLE DB provider to accomplish that.OLEDB is much faster than ODBC so it is suggested to use OLEDB in your connections.

Comparison of response time of drivers from SQL Server to Oracle(in Seconds)
From the above data we can understand that Oracle provider for OLE DB is the best driver! But still even after selecting this driver we will get unknown errors in Linked Servers...

How to Transfer or Move or Script or Migrate Linked Servers Settings in SQL Server

Below are some methods available to perform this task

1) Restore Master DB which will create linked servers automatically (Risky in Cluster Environment).
2) Use the exe from http://www.codeproject.com/KB/database/ScriptLinkedServers.aspx to script all settings except password.
3) Try hacking method of copying the password from source syslogins table to remote server from http://www.sqlservercentral.com/Forums/Topic165118-5-1.aspx
4) Use below sample script to get the server name, provider details but no security settings.

set nocount on select 'exec master..sp_addlinkedserver @server = ''' + srvname + ''', @srvproduct =', + '''' + srvproduct + ''', @provider =', + '''' + providername + ''', @datasrc = ' , '''' + datasource + '''' from sysservers

TIMEOUT - SQL Server Timeout Parameter - Complete Information Guide on timeout

I was given an assignment to understand timeout parameter in SQL Server for one of our business need, So I gathered some details which I'm posting here....

Linked Server Timeout Parameters



Connection Timeout
The "Connection Timeout" is used to specify the maximum length of time the local server should wait to obtain a connection to the linked server SQL Server instance. If "0" (zero) is specified for this option then the server option "remote login timeout" (discussed below) is used. By default the server option default is 20 seconds for the "remote login timeout".

Query Timeout
The "Query Timeout" option is used to specify the length of time a linked server process will be allowed to run before it times out. When this option is set to "0" (zero) then the server "remote query timeout" is used. The "remote query timeout" (discussed below) value defaults to 600 (10 minutes).

Remote login timeout
Number of seconds to wait before returning from a failed attempt to log in to a remote server. For example, if you are attempting to log in to a remote server and that server is down, remote login timeout ensures that you do not have to wait indefinitely before your computer ceases its attempts to log in.

The remote login timeout option affects connections to OLE DB providers made for heterogeneous queries. The default setting for remote login timeout is 20 seconds. A value of 0 allows for an infinite wait.

T-SQL Code to Configure
Exec sp_configure N' remote login timeout (s)', 1000

Remote query timeout
How long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.

For heterogeneous queries, remote query timeout specifies the number of seconds (initialized in the command object using the DBPROP_COMMANDTIMEOUT rowset property) that a remote provider should wait for result sets before the query times out. This value is also used to set DBPROP_GENERALTIMEOUT if supported by the remote provider. This will cause any other operations to time out after the specified number of seconds.

For remote stored procedures, remote query timeout specifies the number of seconds that must elapse after sending a remote EXEC statement before the remote stored procedure times out.

T-SQL Code to Configure
Exec sp_configure N'remote query timeout (s)', 1000

Query wait
Use the query wait option to specify the time in seconds (from 0 through 2147483647) that a query waits for resources before timing out. If the default value of -1 is used, or if –1 is specified, then the time-out is calculated as 25 times of the estimated query cost.

In Microsoft SQL Server, memory-intensive queries (such as those involving sorting and hashing) are queued when there is not enough memory available to run the query. The query times out after a set time calculated by SQL Server (25 times the estimated cost of the query) or the time specified by the nonnegative value of the query wait.
If used incorrectly, can hide other errors related to deadlocking
Will not stop/cancel blocking issues

T-SQL Code to Configure
Exec sp_configure 'query wait', 5

LOCK_TIMEOUT
Will timeout the executing command after N milliseconds if it is waiting for locks.
Typically called at the top of in stored procedures, along with set nocount
Is not Pandora’s box for solving deadlock issues
Check value via select @@LOCK_TIMEOUT
Over linked servers, test carefully. You may find that the 4-part naming doesn’t not work, worst still setting the value before using OPENQUERY may also not work. If you experience this problem, try this syntax:
select * from openquery([myremoteserver], ‘set lock_timeout 1000 select col1 from myremotetable’)

Hope this post answers your question on

1. What is timeout or what is connection timeout or what is login timeout.

2. How to configure SQL Server Connection timeout, Login Timeout.

3. How SQL Server timeout option works.....

Installing Oracle 9i Client 64-bit on WINTEL 64bit Server throws error "The image file ....\setup.exe is valid, but is for a machine type other than."

Today we faced this issue, When we installed Oracle 9i Client (64-bit edition) on Windows 2003 SP1 64-bit (x64) Server, It throwed the below error

Error Message:
The image file ....\setup.exe is valid, but is for a machine type other than the current machine

Root Cause:
Oracle9i Database Release 2 Enterprise/Standard/Personal/Client Edition for Windows XP 2003/Windows Server 2003 (64-bit) version is only certified on the Itanium platform (IA64) so it will not work for x64 Platform.

Solution:
Oracle Database 10g Release 2 (10.2.0.1.0) for Microsoft Windows (x64) ( which can be patched to 10.2.0.2.0 ) will work for x64 Architecture or Revert the OS to 32-bit!

For more information & compatiable platform visit https://metalink.oracle.com (it requires product support identifier)

Monitor Any Linked Server From SQL 2005

Though SQL Server 2005 has a sp_TestLinkedServer stored procedure to test linked server, It will not work out when the linked server is excel or access, so for this I have written a script which will work perfectly for any type of linked Server... Check this link for complete article else take the below code for immediate solution!


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

------------------------------------------------------------------------------------------------------------------
--- Name : uspMonitor_LinkedServer_2005
--- Author : Sakthi
--- Release Date : 05/03/2008
--- Revision : O
--- Revision History:
------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[uspMonitor_LinkedServer_2005]
AS
BEGIN
DECLARE @ServerName VARCHAR(255)
DECLARE @msg VARCHAR(500)
DECLARE @status INT ;SET @status = 0

-- Get the list of Linked Servers
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT name FROM sys.servers WHERE is_linked=1 AND name NOT IN ('') --Not in Clause can be used to exclude some Test Linked Servers

OPEN Lnkd_srvr_Crsr
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
-- The Below query will list the tables available in the linked server. Even it works for Excel
BEGIN TRY
EXEC master..sp_tables_ex @ServerName
SET @status = @@ROWCOUNT
END TRY

-- Checking Status and Sending Alert Mail to Team
BEGIN CATCH
SET @msg= 'Error from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME + ' ' + error_message()
Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
SET @status = 1
END CATCH

IF @status = 0 BEGIN
SET @msg= 'No Recordset Returned from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME
Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
END
SET @status = 0 -- Setting the Status Variable back to 0 for Resetting Error Trapped
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
END
CLOSE Lnkd_srvr_Crsr
DEALLOCATE Lnkd_srvr_Crsr

END

Monitor Linked Server in SQL 2000

Since there are no scripts available in net for monitoring Linked Servers, I wrote a script and published in SQLServerCentral.com. You can check the article from this link for detailed report on the script. Also I have published the script below for immediate usage!

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

------------------------------------------------------------------------------------------------------------------
--- Name : uspMonitor_LinkedServer
--- Author : Sakthi
--- Release Date : 05/03/2008
--- Revision : O
--- Revision History:
------------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.[uspMonitor_LinkedServer]
AS
BEGIN


DECLARE @ServerName VARCHAR(255)
DECLARE @ServerType VARCHAR(50)
DECLARE @Dummy VARCHAR(100)
DECLARE @msg VARCHAR(500)
DECLARE @hr INT
DECLARE @sql INT
DECLARE @Error bigint
DECLARE @status INT ;SET @status = 0

-- Get the list of Excel and Access Linked Servers Since the Testing Method is different here
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT srvname FROM master..sysservers WHERE isremote=1 AND srvproduct NOT LIKE 'SQL Server%' AND srvname NOT IN ('')

OPEN Lnkd_srvr_Crsr
FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Reading a sample Table Name to Dummy Variable from Excel sheet to ensure Connectivity
SELECT TOP 1 @Dummy = TABLE_NAME from master.dbo.SYSREMOTE_TABLES (@ServerName)

-- Checking Status and Sending Alert Mail to Team
IF @@ROWCOUNT = 0 BEGIN
SET @msg= 'Error in Getting Data from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME

Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
END
SET @status = 0 -- Setting the Status Variable back to 0 for Resetting Error Trapped

FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
END
CLOSE Lnkd_srvr_Crsr
DEALLOCATE Lnkd_srvr_Crsr

-- Get the list of SQL Linked Servers Since the Testing Method is different here
DECLARE Lnkd_srvr_Crsr CURSOR FAST_FORWARD FOR
SELECT srvname FROM master..sysservers WHERE isremote=1 AND srvproduct LIKE 'SQL Server%' AND srvname NOT IN ('')
OPEN Lnkd_srvr_Crsr

FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
WHILE @@FETCH_STATUS = 0
BEGIN

-- Performing a login and check the status of Server(SQL) to ensure Connectivity

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @sql OUTPUT
---IF @hr<>0 EXEC sp_OAGetErrorInfo @sql -- Used while testing procedure to check error msg
EXEC @hr = sp_OASetProperty @sql ,'LoginSecure','True'
EXEC @hr = sp_OASetProperty @sql ,'LoginTimeout',10
EXEC @hr = sp_OAMethod @sql,'Connect',null,@ServerName
EXEC @hr = sp_OAGetProperty @sql ,'Status',@status OUTPUT
EXEC @hr = sp_OAMethod @sql,'DisConnect',null
EXEC @hr = sp_OADestroy @sql

-- Checking Status and Sending Alert Mail to Team
IF @status <> 1 BEGIN
SET @msg= 'Timeout in Getting Data from linked server ' + @ServerName + ' Configured at ' + @@SERVERNAME
Exec [MONITOR].master.dbo.xp_smtp_sendmail @from= 'Linked_Server_Alert@Company.com' ,@from_name= @@SERVERNAME ,
@to='Team@Company.com',
@priority = 'HIGH', @subject=@msg,
@type= 'text/plain', @server = 'mailserver.company.com'
Print 'Mail Sent' + @ServerName
END
print @status
SET @status = 0 -- Setting the Status Variable back to 0 for Resetting Error Trapped

FETCH NEXT FROM Lnkd_srvr_Crsr INTO @ServerName
END
CLOSE Lnkd_srvr_Crsr
DEALLOCATE Lnkd_srvr_Crsr


END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

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!