I am attempting to set up Distribution on SQL 2005 using the wizard.
All goes well until the actual script fires off... this line:
use master
exec sp_adddistributor @.distributor = N'NAVSOLDEV\SQLSERVER2005', @.password = N''
GO
Causes this error:
Msg 22538, Level 16, State 1, Procedure sp_MSrepl_check_job_access, Line 155
Only replication jobs or job schedules can be added, modified, dropped or viewed through replication stored procedures.
The rest of the script fails due to this line... Now, I am fair at SQL, but I am no DBA. I did some searching and found one post that seemed to have the same problem, and the gentleman solved it himself, but did not explain how lol.
Any help greatly appreciated!
Dave Borneman
What user account are you using?
Only members of the sysadmin fixed server role can execute sp_adddistributor.
Gary
|||I am using Windows Authentication... but knowing that I will play around. Will post here if that fixes the problem.
Dave
|||I logged in with sa, which is a member of sysadmin.
You mentioned the "fixed" server role... Is the "fix" something I have to apply manually, or would it have been done with SP1 or SP2?
Thanks!
Dave
|||Login as 'sa' should work. You shouldn't have to do anything extra.
Gary
|||well, then it would appear that it might not be a permissions issue.
Anyone have any other ideas?
Dave
To further troubleshooting, you can open SQL profiler. Choose template TSQL_SPs to enable line by line profiling and try to call sp_adddistributor again. You should be able to see where the error message is thrown by searching error code 22538.
Peng
|||I dont know how much of this trace you need to see... its kinda ugly when copied in to a text window.
<code>
SP:StmtStarting IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE name = @.job_name collate database_default and
UPPER(originating_server collate database_default) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
1 master 528826079 sp_MScreate_replication_checkup_agent NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.523
SP:StmtStarting EXEC @.retcode = sys.sp_MSdrop_repl_job @.job_name = @.job_name
1 master 528826079 sp_MScreate_replication_checkup_agent NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.523
SP:Starting EXEC @.retcode = sys.sp_MSdrop_repl_job @.job_name = @.job_name
1 master 919003015 sp_MSdrop_repl_job NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.553
SP:StmtStarting SELECT @.job_found = 0
1 master 919003015 sp_MSdrop_repl_job NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.553
SP:StmtStarting EXEC @.retcode = sys.sp_MSrepl_check_job_access @.id = @.job_id,
@.step_uid = @.job_step_uid,
@.name = @.job_name,
@.err_not_found = 0,
@.job_found = @.job_found output
1 master 919003015 sp_MSdrop_repl_job NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.553
SP:Starting EXEC @.retcode = sys.sp_MSrepl_check_job_access @.id = @.job_id,
@.step_uid = @.job_step_uid,
@.name = @.job_name,
@.err_not_found = 0,
@.job_found = @.job_found output
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting SELECT @.category_id = NULL,
@.subsystem = NULL,
@.database = NULL,
@.job_found = 0
-- Retrieve the JOB Category and Owner ID
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting SELECT @.owner_sid = owner_sid,
@.category_id = category_id
FROM msdb.dbo.sysjobs
WHERE job_id = @.id
OR name = @.name
-- Verify that the job was found
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting IF @.@.ROWCOUNT < 1
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting SELECT @.job_found = 1
-- Retrieve the JOB Subsystem and Database
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting SELECT @.subsystem = sjs.subsystem,
@.database = sjs.database_name
FROM msdb.dbo.sysjobs as sj
JOIN msdb.dbo.sysjobsteps as sjs
ON sj.job_id = sjs.job_id
WHERE (sj.job_id = @.id
OR sj.name = @.name)
AND sjs.step_uid = ISNULL(@.step_uid, sjs.step_uid)
AND sjs.step_id = ISNULL(@.step_id, sjs.step_id)
AND ((sj.category_id = 10
AND sjs.subsystem = N'Distribution')
OR (sj.category_id = 14
AND sjs.subsystem = N'Merge')
OR (sj.category_id = 13
AND sjs.subsystem = N'LogReader')
OR (sj.category_id = 15
AND sjs.subsystem = N'Snapshot')
OR (sj.category_id = 19
AND sjs.subsystem = N'QueueReader')
OR (@.category_id IN (18, 16, 11, 12, 20, 17)
AND sjs.subsystem = N'TSQL'))
-- if current user is the owner of the job then all we can
-- skip straight to ensuring this is a replication job... if
-- not then we need to do some more verification before hand
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting IF @.owner_sid != SUSER_SID()
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting IF @.category_id IN (10, 13, 14, 15, 19)
AND NOT (@.database = DB_NAME()
AND IS_MEMBER('db_owner') = 1)
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting IF @.category_id IN (18, 16, 11, 12, 20, 17)
AND IS_SRVROLEMEMBER('sysadmin') != 1
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting IF NOT (@.category_id = 10
AND @.subsystem = N'Distribution')
AND NOT (@.category_id = 14
AND @.subsystem = N'Merge')
AND NOT (@.category_id = 13
AND @.subsystem = N'LogReader')
AND NOT (@.category_id = 15
AND @.subsystem = N'Snapshot')
AND NOT (@.category_id = 19
AND @.subsystem = N'QueueReader')
-- 18 = REPL-Alert Response
-- 16 = REPL-Checkup
-- 11 = REPL-Distribution Cleanup
-- 12 = REPL-History Cleanup
-- 20 = Replication
-- 17 = REPL-Subscription Cleanup
AND NOT (@.category_id IN (18, 16, 11, 12, 20, 17)
AND @.subsystem = N'TSQL')
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
SP:StmtStarting RAISERROR(22538, 16, -1)
1 master 326665389 sp_MSrepl_check_job_access NAVSOLDEV\SQLSERVER2005 54 2007-03-06 19:20:11.570
</code>
I stopped at the raiserror - I assume anything after that is useless. Let me know if you need more above it... none of it seems to me to be anything obvious for causing that error.
Dave
|||The profiler output is enough.
Just from the profiler output, sounds like you have lingering job left on your machine, either from previous replication or something else. Could you check if you have a job with name like "Replication agents checkup"? If you find this job, also check the entry for this job at msdb.dbo.sysjobs and msdb.dbo.sysjobsteps.
Peng
|||Yes, that job exists, and has entries in those tables.
I disabled the job and re-ran the wizard, to the same effect.
Will deleting the rows in those tables that related to that job fix this? I hesitate to delete stuff from the master tables lol. This instance has never had replication enabled... so it shouldnt be "left over" from something... unless the install put it in there for some reason?
Dave
|||Ok, great news.
Deleting or disabling the job is not enough. I had to also manually delete the entries in sysjobs, sysjobsteps and sysjobschedules in the msdb table. This fixed the install issue 100%.
Thanks all that helped with this!!
|||Hey frostfire,
I am running into the same problem here with SQL2005, SP1. I see you have solved the problem by deleting the entries of the replication job. The same job does exist on my server and I'd like to give it a try, but am wondering that if you need to recover the job since it is setup by the initial installation.
Thanks for any advise !
nbtecha
