Solved

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

Posted on 2007-11-26
6
289 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 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
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)

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 the fundamental information of how to create a table.

785 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