SQL Server Agent Weirdness

Peter Nolan 21 Reputation points
2020-08-25T11:44:20.827+00:00

Hello Running SQL Server version Microsoft SQL Server 2017 14.0.3281.6 (X64)

I have a query which when run in SSMS finishes exactly when I want it to, what it does it goes to a different server and checks if a job has completed ether successfully or unsuccessfully - basically I just want to know when it finishes.

select * from XXXX.msdb.dbo.sysjobhistory jh
join XXXX.msdb.dbo.sysjobs_view jv on jh.job_id = jv.job_id
where jv.name = 'XXXX'
and jh.step_name = '(job outcome)'
and run_date = cast(datepart(YY, getdate()) as varchar(4)) +
right('0' + cast(datepart(MM, getdate()) as varchar(3)), 2) +
right('0' + cast(datepart(DD, getdate()) as varchar(3)), 2))
BEGIN
WAITFOR DELAY '00:00:15'
END

I just want to stress again in SSMS it completes as expected.

I then put the code as step one in a job, with all the correct things such as on completion go to next step on error - error and so on. The problem is that step itself never completes.

Could someone in the community please tell me what the issue is ? This being a fairly new install of SQL Server I think there maybe a config option I need to choose.

Thanks for Reading

Peter

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions
0 comments
Accepted answer
  1. Tom Phillips 17,716 Reputation points
    2020-08-25T11:53:00.85+00:00

    What you describe is always a rights issue. When you run the TSQL from SQL Agent it runs under the Agent service account, not the user account. You are using a linked server, and most likely the SQL Agent account does not have rights to the other server.

    I suggest you create a proxy account with proper rights to the linked server.

    https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-sql-server-agent-proxy?view=sql-server-ver15#:~:text=A%20SQL%20Server%20Agent%20proxy%20account%20defines%20a%20security%20context,proxy%20to%20the%20job%20step.

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. tibor_karaszi@hotmail.com 4,301 Reputation points
    2020-08-25T12:16:07.877+00:00

    Did you check for blocking and locks held?

    0 comments
  2. Peter Nolan 21 Reputation points
    2020-08-25T13:02:34.337+00:00

    Thanks for the reply.

    There was no blocking on either the source or target server. The issue is somehow for this one server that it cannot seem to end while loops if it is a step within a job. Although that is the more important one I do have another instance of a job being having a while loop that was just specific for that server - apologies for not posting earlier as I have just remembered it.

    In that job it was doing almost the same thing except it all internal to the same server and that was not completing either.

    I have also attempted to program RETURNS into the code again without success.

    0 comments
  3. Peter Nolan 21 Reputation points
    2020-08-25T16:53:26.013+00:00

    I think I might of found the solution if it works then I will post it tomorrow, FYI my apologies to Tom and Tibor in that I had a copy and paste fail, however I will detail all of that on Wednesday.

    0 comments
  4. Peter Nolan 21 Reputation points
    2020-08-26T09:42:13.383+00:00

    Thanks for you help Tom you were correct.

    Why I made the mistake is that the account it was running on had Data Reader on the msdb database, and more importantly no error messages were coming back so I 'assumed' that it was all working.

    I then gave the account temp sysadmin access and it worked.

    Seems I have some schooling to do :)

    0 comments