We have a Visual Cron job that uses a Visual Cron SQL connection to run a SQL 2008 Stored Procedure that searches SQL database A for records 3 days old or less and puts them into SQL database B.
We have 1 Visual Cron server in our environment and this is the only job using a Visual Cron SQL connection. Also this the 1st and only SQL Stored Procedure job on this server. It uses the original VCron job we setup 2 months ago - we have not deleted and recreated the job to try to resolve the problem.
And no other Visual Cron jobs run when this job is running.
And the Visual Cron client is not installed on any other server/workstation.
The Stored Procedure job has a trigger to run as soon as another procedure we'll call Procedure A, that runs a command line which starts another program to read the data into SQL database A.
These 2 procedures run 3x a day.
All the servers in this process run Windows Server 2003 x64 standard, R2, SP2.
The stored procedure completes without error when run from SQL.
Originally these databases were on 2 separate servers but now reside on a single server. There error started before database B was moved onto the SQL server with database A.
The procedure had been up 2 months without a problem and about 3 weeks ago we started receiving the following error about once a week 2-5 minutes into the stored procedure:
Error in task sql: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
We added a 15 minute wait condition between the successful exit of Procedure A and the Stored Procedure and the error still occurred.
Can you please look into this issue and advise as to a solution?
Thank you.