7

I am using SQL Server 2012. I have scheduled a job to run every 1 hour, with 1 step calling 1 simple Stored Procedure. The Stored Procedure migrates around 1K data from production server to our local server. Linked server has been established successfully. The query used in Stored procedure looks like below.

SELECT ID,Col1,Col2,Col3,Col4,Col5 into #temp from [111.111.111.111].ProdServer.dbo.SourceTable

Insert into Localtable(ID,Col1,Col2,Col3,Col4,Col5)
SELECT ID,Col1,Col2,Col3,Col4,Col5
FROM #temp where ID NOT IN (select ID from Localtable)

enter image description here

The job execution sometimes takes 25-30 secs which will successfully executed. Sometimes it takes 48-50 seconds which results in failure. Please refer failure message below and other details in above screenshot.

Failure Message:

Executed as user: NT AUTHORITY\NETWORK SERVICE. Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQLSTATE 42000] (Error 53) OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "111.111.111.111" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". [SQLSTATE 01000] (Error 7412). The step failed.

More Details

  • The table is locked by nobody and no queries run on it.
  • In Linked Server Properties, both Query Timeout and Connection Timeout has been set to 0.
  • In Server properties remote query Timeout is also set to 600 Secs.

As last option I tried to change Connection Timeout Settings from its default value 15 to 6000 or higher. I could not achieve it. Please suggest solution to execute my scheduled jobs successfully even it takes 48 minutes or slight more.

Right Click on server > Click on properties > Click view connection properties on left pane under connection - to see Connection Timeout Property

1 new observation: The job is failing if it takes 48 seconds or more to run.

5
  • Instead of using linked server, I would suggest you to use SSIS. It is well suited for your case and has much better error logging and flexibility. You can schedule the SSIS package using SQL agent job.
    – Kin Shah
    Feb 3, 2015 at 13:08
  • @Kin we tried SSIS as well. We encountered same error. Sometimes it runs sometimes not.
    – Shiva
    Feb 3, 2015 at 13:13
  • 1
    Worth checking with your network team about connection issues. You are just moving 1K records which is not that much.
    – Kin Shah
    Feb 3, 2015 at 13:22
  • @Kin, we have very good network connection with leased line. No connectivity issues.
    – Shiva
    Feb 3, 2015 at 13:25
  • 1
    Just a guess, but could issues with DNS or AD cause this?
    – James Z
    Feb 3, 2015 at 15:54

2 Answers 2

6

I am here answering my own question. After so many observations and experiments with searching over the internet, I found solution to this issue. You need to run this simple query

USE Yourdatabase;
GO
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO

The setting takes effect immediately without restarting the server.

Please refer Configure the remote query timeout Server Configuration Option and sp_configure.

0

Right click the server, open the Server Properties, navigate to Connections and ensure that Allow remote connections to this server is checked.

Also, check the firewall settings.

1
  • 2
    @pavinder remote connections and firewall settings are not the issue. because job has run few times.
    – Shiva
    Feb 3, 2015 at 16:28

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