Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Error: @job_name ('sync_juris_matter_update_with_rwdb') does not exist

I am trying to update certain fields in the table of one database (database A) when changes are made to a different database's table (database B).  
Presently I am trying to create a trigger on the table in database B that will start a SQL Agent job with my desired T-SQL language to do the required changes in database A.  But Im getting the error "@job_name ('sync_juris_matter_update_with_rwdb') does not exist".  Ive double checked the job name spelling, even copied and pasted the job name into the trigger.
Here is the trigger Im running:

CREATE TRIGGER [Sync Matter updates with RWdb] ON [dbo].[Matter_Log]
FOR INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'

Ive also tried copying the 'sp_start_job' and 'sp_verify_job_identifiers' SPs into the database A's SP repository and then modifying the trigger's EXEC statement to:

EXEC sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'

but still get the same error.  Can anyone lend a hand on what Im doing wrong?

FYI, I tried creating a trigger in the table of database B to directly update the table in database A.  However, database B is the database of a 3rd party vendor's application and has it's own username account with an unknown password.  This username account is tied to the dbo server login.  Therefore I got a permission error when the trigger would run because database B uses dbo with the sa username account and there is apparently no way to specify alternate credentials to use in T-SQL.
0
Wilbat
Asked:
Wilbat
  • 3
  • 2
1 Solution
 
Ken SelviaRetiredCommented:
If you connect as the user doing the update on Matter_Log and execute

EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Juris_matter_updates_with_RWdb'

does it work interactively in query analyzer?
0
 
WilbatAuthor Commented:
I cannot connect as the user doing the update on Matter_Log.  All updates would be done via the 3rd part app I mentioned and the app makes all changes via a username who's password is unknown.
0
 
Scott PletcherSenior DBACommented:
The user doing the UPDATE does not have authority to the job: since it can't "see" the job, the job does not exist (as far as that user is concerned).

From BOL under "sp_start_job":
Permissions
A user who is not a member of the sysadmin role can use sp_start_job to start only the jobs he/she owns.


This can be tricky to resolve, since SQL will likely use the proxy account.  And you likely have not set up a proxy account and even if you did you would not likely have given it 'sa' authority, which you don't really want to do anyway.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
WilbatAuthor Commented:
OK, what if I simply assign that user as the job's Owner?
0
 
Scott PletcherSenior DBACommented:
Then you should be fine ... assuming that user has the authority to do whatever the job needs to do.
0
 
WilbatAuthor Commented:
Setting the user from database B as the job's Owner seems to have fixed the problem.
Thx Scott
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now