Solved

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

Posted on 2007-11-26
6
299 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 69

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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 

Author Comment

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

Accepted Solution

by:
Scott Pletcher earned 500 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

627 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