Trouble Shooting for Error 7311 and Error 7412

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: ‘XXX-XXXXX’ completed on \\SQL001

JOB RUN:            ‘XXX-XXXXX’ was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 10 minutes, 1 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Morning).  The last step to run was step 1 (YYYYY_YYY).

Solution

Start SSMS, expand SQL Server Agent – Jobs, right click on the job ‘XXX-XXXXX’, select “View History” from the right-click menu.

The job runs daily, 60% succeeded, 40% failed. For the failed jobs, there are this error message in the log”

“Cannot obtain the schema rowset “DBSCHEMA_TABLES_INFO” for OLE DB provider “SQLNCLI10” for linked server “SQL002”. The provider supports the interface<c/> but returns a failure code when it is used. [SQLSTATE 42000] (Error 7311)  OLE DB provider “SQLNCLI10” for linked server “SQL002” returned message “Unspecified error”. [SQLSTATE 01000] (Error 7412)  OLE DB provider “SQLNCLI10” for linked server “SQL002” returned message “Query timeout expired”. [SQLSTATE 01000] (Error 7412).  The step failed.,00:10:00,16,7412,,,,0″

Since it sometimes works properly, sometimes not, so adjusting the parameter for remote query timeout Server Configuration Option of the linked server may fix the issue. The default value is 600 (seconds). I changed it to 3000 (seconds), resolved the issue. It can be done by two methods:

Method 1 Using SQL Server Management Studio

  • In Object Explorer, right-click the linked server and select Properties.
  • Click the Connections node.

Under Remote server connections, in the Remote query timeout box, type 3000.

 

Method 2 Using T-SQL

USE Database1;

GO;

EXEC sp_configure ‘remote query timeout’, 3000 ;

GO RECONFIGURE ;

GO

 

Advertisements

Published by

Zhihua Wang

Zhihua Wang is a SQL Server Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s