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 [184.108.40.206].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)
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.
Executed as user: NT AUTHORITY\NETWORK SERVICE. Named Pipes Provider: Could not open a connection to SQL Server . [SQLSTATE 42000] (Error 53) OLE DB provider "SQLNCLI10" for linked server "220.127.116.11" returned message "Login timeout expired". [SQLSTATE 01000] (Error 7412) OLE DB provider "SQLNCLI10" for linked server "18.104.22.168" 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.
- 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.