4

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.

  • 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 '15 at 13:08
  • @Kin we tried SSIS as well. We encountered same error. Sometimes it runs sometimes not. – Shiva Feb 3 '15 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 '15 at 13:22
  • @Kin, we have very good network connection with leased line. No connectivity issues. – Shiva Feb 3 '15 at 13:25
  • 1
    Just a guess, but could issues with DNS or AD cause this? – James Z Feb 3 '15 at 15:54
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
    @pavinder remote connections and firewall settings are not the issue. because job has run few times. – Shiva Feb 3 '15 at 16:28

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.