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
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
Ive also tried copying the 'sp_start_job' and 'sp_verify_job_identifiers
EXEC sp_start_job @job_name = 'Sync_Juris_matter_updates
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.
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
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
Ive also tried copying the 'sp_start_job' and 'sp_verify_job_identifiers
EXEC sp_start_job @job_name = 'Sync_Juris_matter_updates
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.
ASKER
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.
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.
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.
ASKER
OK, what if I simply assign that user as the job's Owner?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Setting the user from database B as the job's Owner seems to have fixed the problem.
Thx Scott
Thx Scott
EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Juris_matter_updates
does it work interactively in query analyzer?