Expired subscription clean up failures - error 208

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Expired subscription clean up failures - error 208

Author

Topic

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-10 : 08:47:53

starting on friday night the above job has started failing due to the error "Executed as user: dbo. Invalid object name 'sysmergepublications'. [SQLSTATE 42S02] (Error 208). The step failed."

Now i have checked the sp that is called by the job and have narrowed the problem down to the following sp call sp_MScleanup_conflict_table.

If i call this sp as a dbo it fails. if i run the code within the sp itself then everything runs through fine. (posted below). can anyone help? I'm sure it must be something to do with permissions but i cant think what, plus nothing has changed since friday night, when it last ran [Sad]

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

BEGIN

declare @pubid uniqueidentifier
declare @conflict_retention int
,@conflict_table sysname
,@cmd nvarchar(4000)
,@tranpubid int

/*
** Security Check
*/
declare @retcode int
EXEC @retcode = dbo.sp_MSreplcheck_publish
IF @@ERROR <> 0 or @retcode <> 0
print (1)

--
-- merge cleanup
--
if exists (select * from sysobjects where name = 'sysmergepublications')
begin
declare PC CURSOR LOCAL FAST_FORWARD for select DISTINCT pubid, conflict_retention
from sysmergepublications where LOWER(publisher)=LOWER(@@SERVERNAME) and publisher_db=db_name() and conflict_retention>0
open PC
fetch PC into @pubid, @conflict_retention
while (@@fetch_status<>-1)
begin
exec @retcode = sp_MScleanup_conflict @pubid, @conflict_retention
if @@ERROR<>0 or @retcode<>0
begin
close PC
deallocate PC
print (1)
end
fetch next from PC into @pubid, @conflict_retention
end
close PC
deallocate PC
end
--
-- all done
--
print 0
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-10 : 16:11:23

database source for the jobstep?

--------------------
keeping it simple...

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-09-10 : 22:56:50

Is replication still working? Anyone made change to the job?

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-11 : 04:04:01

quote:
Originally posted by jen

database source for the jobstep?

--------------------
keeping it simple...

Database source is master

Rmiao - Everything is still working fine replication side, so most steps must be seeing the table! Just not this step.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 05:45:10

shouldn't it be msdb?

--------------------
keeping it simple...

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-11 : 06:57:41

quote:
Originally posted by jen

shouldn't it be msdb?

--------------------
keeping it simple...

The sp which is called by the job "sp_expired_subscription_cleanup" is stored on master.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 07:01:06

interesting

--------------------
keeping it simple...

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 07:05:12

http://msdn2.microsoft.com/en-us/library/aa260435(sql.80).aspx

--------------------
keeping it simple...

AnimalMagic
Starting Member

28 Posts

Posted - 2007-09-11 : 09:35:53

quote:
Originally posted by jen

http://msdn2.microsoft.com/en-us/library/aa260435(sql.80).aspx

--------------------
keeping it simple...

Not sure what your trying to tell me there?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-11 : 12:21:50

the error says invalid object...
you get this when sql server can't retrieve the object information because it might be in a different location? if the database of the jobstep is master perhaps you need to check that stored procedure? where are the objects located?

if you run the stored procedure on the master database as owner of the job... if it's sa then you run it with sysadmin privilege, if it's another user, use that user to run it

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources