Solved

sql job timeout

Posted on 2014-01-06
5
843 Views
Last Modified: 2014-01-18
We had have a job that executes a stored proc that has been working fine
(the stored proc executes a query that involves a table on a linked server)

The job started failing saying there was a schema change

I was out of the office and a colleague said he redid the linked server connection (not sure why) which got rid of that error but now there is a time out error


What is strange is that if the stored proc executed by job  is manually executed it runs fine but when scheduled in the job it times out.  Not sure what the difference would be
0
Comment
Question by:johnnyg123
  • 2
  • 2
5 Comments
 
LVL 25

Expert Comment

by:SStory
ID: 39759781
My initial thought is that in a query browser there is no timeout, but _PERHAPS_ scheduled jobs make a connection to the server like vb apps do with a timeout in the connection string or something.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39759785
Check permissions.  Maybe the user the job runs as does not have the same permissions on the newly created linked server.  i.e., make sure the new setup matches what you had before.
0
 
LVL 25

Expert Comment

by:SStory
ID: 39759787
Oh, here we go:
Shutdown time-out interval (in seconds)

    Specifies the number of seconds that SQL Server Agent waits for jobs to complete before shutting down. If the job is still running after the interval specified, SQL Server Agent forcefully stops the job.

source: http://msdn.microsoft.com/en-us/library/ms189727.aspx
0
 

Accepted Solution

by:
johnnyg123 earned 0 total points
ID: 39777464
After much research I figured it out

Long story short ...the issue occurred because of the change in linked server

I was getting a sql infinite compile message

Solution was to install service pack 2 for sql server 2008 r2 and problem went away
0
 

Author Closing Comment

by:johnnyg123
ID: 39790469
None of the posted solutions was the correct answer
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question