Script to check SQL Server Linked Server Status

 

It leverages on the system stored procedure sys.sp_testlinkedserver

SET NOCOUNT ON; DECLARE @LinkName NVARCHAR (128), @retval INT, @msg varchar(300) IF (SELECT COUNT (srvname) FROM sys.sysservers WHERE srvname <> @@SERVERNAME) = 0 RETURN DECLARE srvname INSENSITIVE CURSOR FOR         (SELECT srvname FROM sys.sysservers WHERE srvname <> @@SERVERNAME) FOR READ ONLY OPEN srvname     FETCH NEXT FROM srvname INTO @LinkName WHILE @@FETCH_STATUS = 0 BEGIN     BEGIN TRY         EXEC @retval = sys.sp_testlinkedserver @LinkName     END TRY         BEGIN CATCH         IF @LinkName IS NULL RETURN         SET @retval = SIGN(@@ERROR)         IF @retval <> 0         BEGIN           SET @msg = 'Unable to connect to the Linked server : ' + @LinkName           RAISERROR (@msg, 16, 2 )         END     END CATCH     FETCH NEXT FROM srvname INTO @LinkName     END        CLOSE srvname DEALLOCATE srvname

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

 

How to connect to Oracle Database from SQL Server instance and query the Oracle Database?

Linked Server is the solution for this.
A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

A linked server definition specifies the following objects:

· An OLE DB provider : is a DLL that manages and interacts with a specific data source

· An OLE DB data source : identifies the specific database that can be accessed through OLE DB

Basics of Linked Server Configuration:

Advantages of Linked Server:

· The ability to access data from outside of SQL Server.

· The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

· The ability to address diverse data sources similarly.

Pre-requisite

· Install Oracle Client

· Configure tnsnames.ora

Example

STG21.SIVA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = oraDBsrvr.siva.com)(PORT = 60000)) ) (CONNECT_DATA = (SERVICE_NAME = stg01.siva) )

Validate tnsnames.ora entry

On Command Prompt Tnsping STG21.SIVA

Notes:

Here we are going to use Microsoft ODBC for Oracle as the OLE DB Provider.
The STG21.SIVA is going to be OLE DB Data Source pointing to the Oracle Database STG01.SIVA

Open up ODBC

Select System tab

Click Add

Select Microsoft ODBC for Oracle

Click Finish

Click OK, OK

Connect to SQL Server Management Studio

Right click select new Linked Server

Click OK

Test Connection

Right click select Test connection

Validation:

Just run the sample query

exec ('select user, sysdate from dual') at STG21_SQLSVR54

Output look like;

USER SYSDATE SQLSVR07 2012-09-25 01:37:02.000

Oracle Linked Server issue

Today I got the error below on the SQL Server job it runs a SSIS Package.

Error:
Msg 7302, Level 16, State 1, Server USIVA007, Line 1
Cannot create an instance of OLE DB provider "OraOLEDB.Oracle" for linked server
"SYS01_ORACLE01"

Resolution:
  Strange but recreating of the Linked Server connection, resolved the issue.

Reference:
Techniques to Debug Connectivity Issues to an Oracle Server Using the ODBC Driver and OLE DB Provider