How to fix the error can not obtain the schema rowset DBSCHEMA_TABLES_INFO?

Question

I'm trying to correct some errors on an SSIS job. That number of errors was 12 and know I got to 5

I am getting the following error message, which is in French.

[OLE DB Source [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Impossible d'obtenir l'ensemble de lignes du schéma "DBSCHEMA_TABLES_INFO" pour le fournisseur OLE DB "SQLNCLI10" du serveur lié "serveur3". Le fournisseur prend en charge l'interface, mais retourne un code d'erreur lorsqu'elle est utilisée.". [SSIS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC0202009

The error message translates to the following in English.

[OLE DB Source 1] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Can not obtain the schema rowset" DBSCHEMA_TABLES_INFO "for OLE DB provider" SQLNCLI10 "for linked server" server3. "Provider supports the interface, but returns a failure code when it is used. ". [SSIS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute stage and returned error code 0xC0202009.

How do I fix the error message?

Answer 1

Translated error message:

The error message that you added in the comment was in French. Using Google Translate, the error message translates to the following:

[OLE DB Source 1] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Can not obtain the schema rowset" DBSCHEMA_TABLES_INFO "for OLE DB provider" SQLNCLI10 "for linked server" server3. "Provider supports the interface, but returns a failure code when it is used. ". [SSIS.Pipeline] Error: component "OLE DB Source" (1) failed the pre-execute stage and returned error code 0xC0202009.

• The message implies that you are probably trying to query a Linked Server using SQL Server Native Client 10.0. I am going to guess that the linked server is pointing to SQL Server 2000 based on the searches on web.

Check the following:

Read the below Microsoft Connect website bug report on how to fix your issue.

Unable to query linked SQL Server 2000

Quote from website:

The issue here seems to be that the instcat version on your SQL 2000 server is out-of-date. Following the steps in KB 906954 (http://support.microsoft.com/kb/906954) should fix that.

Other links:

Other links that might help to resolve your issue:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI10" for linked server "XXXXXX"

The links suggest creating the below stored procedure in master database and granting Execute permission to appropriate user account.

USE master;
GO

CREATE PROCEDURE sp_tables_info_rowset_64
        @table_name SYSNAME
    ,   @table_schema SYSNAME = NULL
    ,   @table_type nvarchar(255) = NULL
AS 
    DECLARE @Result INT 
    SET @Result = 0
    EXEC @Result = sp_tables_info_rowset @table_name, @table_schema, @table_type
GO

To run the procedure (one time only is needed)

USE master
GRANT EXEC ON sp_tables_info_rowset_64 TO PUBLIC

Steps to create a data source to connect to SQL Server instance from SSIS 2008 R2

Here are the steps to create a Data Source to connect to an SQL Server instance using Business Intelligence Development Studio (BIDS) 2008 R2.

On the BIDS project solution, right-click on the folder Data Sources and click New Data Source...

On the Data Source Wizard, click New... to create a new connection.

On the Connection Manager dialog, perform the following steps:

• Select an appropriate Provider based on the SQL Server version that you would like to connect to. You will see only the providers that are installed on the machine.
• For SQL Server 2000, select Microsoft OLE DB Provider for SQL Server
• For SQL Server 2005 - 2008 R2, select Native OLE DB\SQL Server Native Client 10.0

• For SQL Server 2012, select Native OLE DB\SQL Server Native Client 11.0

• Enter the Server name or Instance name, for example MachineName\InstanceName

• Select Windows authentication or SQL Server Authentication depending on how you would like to connect to the SQL Server instance. If you choose SQL Server Authentication, enter a valid user name and password.

• Click Test Connection to make sure that the credentials are valid.

• Select a database to connect.

• Click OK

Click Next on Data Source Wizard. On the final step, give a proper name to the Data Source like OLEDB_AdventureWorks. OLEDB denotes the connection type and AdventureWorks denotes the database name. Click Finish.

The newly created data source OLEDB_AdventureWorks will show up under the Data Sources folder in the SSIS project.

To add the data source to the package, right-click on the Connection Manager tab that is displayed at the bottom of an SSIS package and then click New Connection From Data Source...

Select Data Source dialog will display all the available data sources on the SSIS project. Pick the ones that you would like to add to the package and click OK.

The new data source will appear on the package connection manager. You can now use the connection manager in your package tasks.

Other option:

Instead of creating data source, you can also directly create a Connection manager on the package itself. You right-click on the Connection Manager tab that is displayed at the bottom of an SSIS package and then choose the appropriate connection that you would like to create. This connection manager will be visible only to the package and not to the other packages in the SSIS project solution.

Word of advice:

• Read the FAQ on how to post a question with enough information for others to answer.

• Do not add the error messages in the comments. It is very hard to read and understand the message.

• Always edit your question to explain the issue in detail or to add more information.

• Translate the error message if it is not in English.