Error while creating Linked server "Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server ‘LinkedServerName’"

When we create linked server for SQL server 2000 on SQL server 2005/SQL server 2008 /SQL server 2008 R2 sometimes we see errors w.r.t schema. In this blog, I will discuss on why we get this type of error and also the solution to fix the error message. There is a well know issue when…

2

Troubleshooting “Cannot create an instance of OLE DB provider”

Today I am going to blog about a frequent issue that we come across while creating linked server-: Cannot create an instance of OLE DB provider “MSDADASQL” for linked server “MyDB2” In this scenario I was creating a linked server to a DB2 server, but everything explained in this blog holds good for any linked…

9

How to verify if the issue is due to linked server or provider

When we create a  linked server to a database Other than SQL server, like Sybase , Oracle , DB2 etc ,we see some errors and we are not sure whom to contact for support. Should we contact Microsoft support or the third party support. In such an ambiguous situation we can verify ourselves, which support…

1

Executing an INSERT statement on a View in linked server

Consider this scenario where you have a linked server from one SQL server to another SQL Server. Both the SQL Servers are SQL Server 2008 SP2 on Windows 2008.   Say the two SQL Servers are Server A and Server B. Linked server from Server A to Server B is set up using SQL Native…

0

Permissions needed to set up linked server with out-of-process provider

When setting up linked server to third-party Databases, it is recommended to run the third-party provider in out-of-process mode, because when the provider is run in-process (within the same process as SQL Server), then any issues with the provider can affect SQL Server process which could also result in crashing SQL server.  To how to…

26

Linked Server Performance (Heterogeneous Databases)

Recently I was working on a problem where the scenario was as below. There are various applications connecting to SQL Server to fetch data. But the actual data was not resident on the SQL Server rather on Oracle 10g and IBM DB2 Databases. The applications were supposed to use Windows active directory for security and…

6

“ORA-12154: TNS: could not resolve the connect identifier specified” error while creating a linked server to Oracle

This is one of the most common errors while creating linked server to Oracle database. Today I will discuss the reason for this error and possible resolutions. Full error message: OLE DB provider “MSDAORA” for linked server “LINKED_ORA” returned message “ORA-12154: TNS:could not resolve the connect identifier specified”. Msg 7303, Level 16, State 1, Line…

33

Unable to execute a remote stored procedure over a linked server

I ran into an interesting situation when executing a Remote Stored Procedure on Linked Server using four part query.  Vista-3139 which is a SQL Server 2005 instance is linked to the SERV2003\SQLDUMMY which is a SQL Server 2000 named instance. When I tried to execute a stored procedure: EXEC [SERV2003\SQLDUMMY].SQLAdmin.dbo.sp_ddlevents Got the following error message. Could…

6

How to create a linked server in SQL Server 2005 for Sybase ASE 12.5 database using DataDirect’s ODBC Driver and OLEDB provider?

Linked server in SQL Server 2005 to Sybase ASE 12.5 can be created with both ODBC driver and OLEDB provider.  Below are the steps. A. Creating Linked server for Sybase ASE 12.5 from SQL Server using DataDirect Sybase Wire Protocol ODBC driver Install the DataDirect ODBC Driver for Sybase on the machine running SQL Server….

4