asked on

SQL Server 2000 Replication Config error 2812

Windows Server 2003
SQL Server 2000 on both servers

Attempting to configure replication from server A to Server B using publishing configuration wizard.

Using Enterprise Manager and we received error 2812: Could not find stored Procedure 'msdb.dbo.sp_add_jobstep_internal'.

Following the analysis of general Microsoft help which pointed to a user/rights problem I used the Query Analyser, login with 'sa' and executed sp_adddistributor . I basicly received the same error. I also executed sp_help looking for the specifice procedure - not found.

Is it possible the sp is really not there. All help is appreciated.

Thanks,

imran_fast

Did you successfull configure the distributor or no?
You have to configure distributor then publisher and then subscriber and make sure the user on publisher has access on the subscriber.
Did you check on your server inside msdb database on bot server that this procedure is there or no?
if not use this code to create it

CREATE PROCEDURE dbo.sp_add_jobstep_internal
@job_id UNIQUEIDENTIFIER = NULL, -- Must provide either this or job_name
@job_name sysname = NULL, -- Must provide either this or job_id
@step_id INT = NULL, -- The proc assigns a default
@step_name sysname,
@subsystem NVARCHAR(40) = N'TSQL',
@command NVARCHAR(3201) = NULL, -- We declare this as NVARCHAR(3201) not NVARCHAR(3200) so that we can catch 'silent truncation' of commands
@additional_parameters NTEXT = NULL,
@cmdexec_success_code INT = 0,
@on_success_action TINYINT = 1, -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
@on_success_step_id INT = 0,
@on_fail_action TINYINT = 2, -- 1 = Quit With Success, 2 = Quit With Failure, 3 = Goto Next Step, 4 = Goto Step
@on_fail_step_id INT = 0,
@server NVARCHAR(30) = NULL,
@database_name sysname = NULL,
@database_user_name sysname = NULL,
@retry_attempts INT = 0, -- No retries
@retry_interval INT = 0, -- 0 minute interval
@os_run_priority INT = 0, -- -15 = Idle, -1 = Below Normal, 0 = Normal, 1 = Above Normal, 15 = Time Critical)
@output_file_name NVARCHAR(200) = NULL,
@flags INT = 0 -- 0 = Normal, 1 = Encrypted command (read only), 2 = Append output files (if any), 4 = Write TSQL step output to step history
AS
BEGIN
DECLARE @retval INT
DECLARE @max_step_id INT

SET NOCOUNT ON

-- Remove any leading/trailing spaces from parameters
SELECT @step_name = LTRIM(RTRIM(@step_name))
SELECT @subsystem = LTRIM(RTRIM(@subsystem))
SELECT @server = LTRIM(RTRIM(@server))
SELECT @database_name = LTRIM(RTRIM(@database_name))
SELECT @database_user_name = LTRIM(RTRIM(@database_user_name))
SELECT @output_file_name = LTRIM(RTRIM(@output_file_name))

-- Turn [nullable] empty string parameters into NULLs
IF (@server = N'') SELECT @server = NULL
IF (@database_name = N'') SELECT @database_name = NULL
IF (@database_user_name = N'') SELECT @database_user_name = NULL
IF (@output_file_name = N'') SELECT @output_file_name = NULL

-- Check authority (only SQLServerAgent can add a step to a non-local job)
EXECUTE @retval = sp_verify_jobproc_caller @job_id = @job_id, @program_name = N'SQLAgent%'
IF (@retval <> 0)
RETURN(@retval)

EXECUTE @retval = sp_verify_job_identifiers '@job_name',
'@job_id',
@job_name OUTPUT,
@job_id OUTPUT
IF (@retval <> 0)
RETURN(1) -- Failure

-- Default step id (if not supplied)
IF (@step_id IS NULL)
BEGIN
SELECT @step_id = ISNULL(MAX(step_id), 0) + 1
FROM msdb.dbo.sysjobsteps
WHERE (job_id = @job_id)
END

-- Check parameters
EXECUTE @retval = sp_verify_jobstep @job_id,
@step_id,
@step_name,
@subsystem,
@command,
@server,
@on_success_action,
@on_success_step_id,
@on_fail_action,
@on_fail_step_id,
@os_run_priority,
@database_name OUTPUT,
@database_user_name OUTPUT,
@flags,
@output_file_name
IF (@retval <> 0)
RETURN(1) -- Failure

-- Get current maximum step id
SELECT @max_step_id = ISNULL(MAX(step_id), 0)
FROM msdb.dbo.sysjobsteps
WHERE (job_id = @job_id)

BEGIN TRANSACTION

-- Update the job's version/last-modified information
UPDATE msdb.dbo.sysjobs
SET version_number = version_number + 1,
date_modified = GETDATE()
WHERE (job_id = @job_id)

-- Adjust step id's (unless the new step is being inserted at the 'end')
-- NOTE: We MUST do this before inserting the step.
IF (@step_id <= @max_step_id)
BEGIN
UPDATE msdb.dbo.sysjobsteps
SET step_id = step_id + 1
WHERE (step_id >= @step_id)
AND (job_id = @job_id)

-- Clean up OnSuccess/OnFail references
UPDATE msdb.dbo.sysjobsteps
SET on_success_step_id = on_success_step_id + 1
WHERE (on_success_step_id >= @step_id)
AND (job_id = @job_id)

UPDATE msdb.dbo.sysjobsteps
SET on_fail_step_id = on_fail_step_id + 1
WHERE (on_fail_step_id >= @step_id)
AND (job_id = @job_id)

UPDATE msdb.dbo.sysjobsteps
SET on_success_step_id = 0,
on_success_action = 1 -- Quit With Success
WHERE (on_success_step_id = @step_id)
AND (job_id = @job_id)

UPDATE msdb.dbo.sysjobsteps
SET on_fail_step_id = 0,
on_fail_action = 2 -- Quit With Failure
WHERE (on_fail_step_id = @step_id)
AND (job_id = @job_id)
END

-- Insert the step
INSERT INTO msdb.dbo.sysjobsteps
(job_id,
step_id,
step_name,
subsystem,
command,
flags,
additional_parameters,
cmdexec_success_code,
on_success_action,
on_success_step_id,
on_fail_action,
on_fail_step_id,
server,
database_name,
database_user_name,
retry_attempts,
retry_interval,
os_run_priority,
output_file_name,
last_run_outcome,
last_run_duration,
last_run_retries,
last_run_date,
last_run_time)
VALUES (@job_id,
@step_id,
@step_name,
@subsystem,
@command,
@flags,
@additional_parameters,
@cmdexec_success_code,
@on_success_action,
@on_success_step_id,
@on_fail_action,
@on_fail_step_id,
@server,
@database_name,
@database_user_name,
@retry_attempts,
@retry_interval,
@os_run_priority,
@output_file_name,
0,
0,
0,
0,
0)

COMMIT TRANSACTION

-- Make sure that SQLServerAgent refreshes the job if the 'Has Steps' property has changed
IF ((SELECT COUNT(*)
FROM msdb.dbo.sysjobsteps
WHERE (job_id = @job_id)) = 1)
BEGIN
-- NOTE: We only notify SQLServerAgent if we know the job has been cached
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @job_id)
AND (server_id = 0)))
EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = N'J',
@job_id = @job_id,
@action_type = N'U'
END

-- For a multi-server job, remind the user that they need to call sp_post_msx_operation
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @job_id)
AND (server_id <> 0)))
RAISERROR(14547, 0, 1, N'INSERT', N'sp_post_msx_operation')

RETURN(0) -- Success
END

GO

imran_fast

hi,
did you get your solution?

SamSystems1

ASKER

Yes - I ended up rebuilding the Windows Server with SQL Server, thanks.

ASKER CERTIFIED SOLUTION

GranMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial