Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2007-11-26
6
Medium Priority
?
303 Views
Last Modified: 2012-06-27
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
Comment
Question by:Wilbat
  • 3
  • 2
6 Comments
 
LVL 12

Expert Comment

by:kselvia
ID: 20352892
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
 

Author Comment

by:Wilbat
ID: 20352960
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 20353466
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 

Author Comment

by:Wilbat
ID: 20353559
OK, what if I simply assign that user as the job's Owner?
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 20353621
Then you should be fine ... assuming that user has the authority to do whatever the job needs to do.
0
 

Author Comment

by:Wilbat
ID: 20353638
Setting the user from database B as the job's Owner seems to have fixed the problem.
Thx Scott
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

916 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