Error in SSMS while scheduling jobs
I am trying to set up a job which populates data to a table after executing stored procedure.
DECLARE @return_value int EXEC @return_value = [dbo].[sp_WarehouseAttendance_New] SELECT 'Return Value' = @return_value
But I keep getting this error as below.
The OLE DB Provider "SQLNCLI10" for linked server "sql10" reported an error. Execution terminated by the provider because a resource limit was reached. [SQLSTATE 42000] (Error 7399) Cannot fetch the rowset from OLE DB provider "SQLNCLI10" for linked server "sql10".. [SQLSTATE 42000] (Error 7421) OLE DB provider "SQLNCLI10" for linked server "sql10" returned message "Query timeout expired". [SQLSTATE 42000] (Error 7412). The step failed.
I have tried including DBCC freeproccache before stored proc but it doesnt help.
Please let me know how this can be avoided.
I followed these steps:
To set a time limit for remote queries
1.In Object Explorer, right-click a server and select Properties.
2.Click the Connections node.
3.Under Remote server connections, in the Remote query timeout box, type or select a value from 0 through 2,147,483,647 to set the maximum number seconds for SQL Server to wait before timing out.