sql job timeout

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
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SStoryCommented:
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.
Kevin CrossChief Technology OfficerCommented:
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.
SStoryCommented:
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
johnnyg123Author Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnnyg123Author Commented:
None of the posted solutions was the correct answer
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.