2

I have a job that runs for multiple regions across the globe. Depending on what region is running and what time zone it's in, the job might not run. It uses GOTO to get to the end and reports that it was skipped due to time and completes.

This runs as expected with no issues.

The Issue: there are times when a linked server farther down the proc is under really heavy load. We know about it and cannot avoid it - so we don't query the linked server at this time.

But, we're still getting an error as if it's queried:

Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI11" for linked server returned message "Unspecified error". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI11" for linked server returned message "Query timeout expired". [SQLSTATE 01000] (Error 7412). The step failed.

Are linked servers validated when a proc starts causing this schema check that times out?

2

This is a guess, but I would try enabling lazy schema validation for the linked server under heavy load:

EXEC master.dbo.sp_serveroption @server=N'Linked_Server_name', @optname=N'lazy schema validation', @optvalue=N'true'

If this option is set to false, the default value, SQL Server checks for schema changes that have occurred since compilation in remote tables. This check occurs before query execution. If there is a change in the schema, SQL Server recompiles the query with the new schema.

If this option is set to true, schema checking of remote tables is delayed until execution. This can cause a distributed query to fail with an error if the schema of a remote table has changed between query compilation and execution.

Source

As to only check the linked server metadata when querying the linked server itself.

Another workaround could be using dynamic sql statements to query the linked server

DECLARE @SQL nvarchar(max)
IF (Linked Server not under heavy load = true)
BEGIN
SET @SQL = 
N'SELECT * FROM OPENQUERY(LinkedServer,''SELECT * FROM master.sys.databases'')'
EXEC SP_EXECUTESQL @SQL
END
|improve this answer|||||
  • Dynamic SQL is an awesome suggestion - I should have thought of that! Delaying the check, while a good one, would cause headaches down the road - I can already feel them coming on! – Tony Basallo Dec 5 '19 at 14:37
  • @TonyBasallo Thank you for the feedback :) – Randi Vertongen Dec 5 '19 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.