December 3, 2009 at 1:14 pm
Hello Room,
I setup SQL Server native transaction replication with a remote distributor in non-trusted environment.
SQL Server 2000 SP3 ---> Publishers
SQL Server 2008 SP1 ----> A remote Distributor
SQL Server 2008 SP1 ----> Subscribers.
The initial snapshot was completed 100 percent.
The distributor agent failed with the following error:
Could not find procedure 'sp_MSins_dbot_tableName'
On my publisher, I manually generate the following scripts:
1. exec sp_scriptinsproc @artid = article id;
2. exec sp_scriptdelproc @artid = article id;
3. exec sp_scriptmappedupdproc @artid = article id;
Apply those script on the subscriber.
However, those stpes did not fix my problems.
Can anyone please help on this?
Thanks a lot.
Edwin
December 3, 2009 at 8:46 pm
when you ran the ddl scripts on the publisher did they get created in the correct Schema?
That's the one thing I can think of.
~Craig
Craig Outcalt
December 4, 2009 at 6:43 am
I checked the sp by executing:
use Publisher database;
go
exec select * from sysarticles where
(
ins_cmd = ''sp_MSins_dbot_TurbDataGeneric' or upd_cmd = 'sp_MSins_dbot_TurbDataGeneric' or del_cmd = 'sp_MSins_dbot_TurbDataGeneric'
)
It returned a empty record.
Also, I checked on Subscriber database, there are:
dbo.p_MSdel_dbot_TableName_css and
dbo.p_MSdel_dbot_TableName_css
In addition, the "t_TableName" is exited in Publisher.
The last_sync_status = 5 and The
last_sync_summary = The process could not connected to distributor.
Please help and advise?
Thanks a lots for your help.
Regards,
Edwin
December 4, 2009 at 10:02 am
This problem is usually due to naming, either schema or replication procedure.
Are you 100% sure the names of the procedures specified in sysarticles of the publication databases matches the procedures in the subscription database?
both schema, and procedure name?
If they do exist correctly, my suggestion would be to drop the article, drop the subscription to that article, refresh subscriptions, add the article, refresh the subscriptions again, take a snapshot (force invalidate old one) and distribute.
~Craig
Craig Outcalt
December 4, 2009 at 10:06 am
One thing I forgot to mention was to make sure the login/user that's doing the inserts/updates/deletes has the default schema of dbo. That would also cause the issue because replication doesn't actually call dbo.sp_MS* by fully qualified name. They assume the default schema is dbo.
and make sure the updating user has exec access to those procedures!!!:w00t:
Craig Outcalt
December 4, 2009 at 10:29 am
have you checked the security access of distributor agent....
also have you created a custom stored procedures for replication insert. "sp_MSins_dbot_tableName"
try to create this sp at subsriber.
HTH
Vinay
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
December 4, 2009 at 10:41 am
I checked on my SQL Server 2000 Publisher database and the parameters are:
Artid = 5
del_cmd = CALL sp_MSdel_dbot_TableName
ins_cmd = CALL sp_MSins_dbot_ElecConnData
upd_cmd = MCALL sp_MSupd_dbot_ElecConnData
name = t_TableName
I generated those above procedures by executing script on Publisher database:
exec sp_scriptinsproc @artid = articleid;
exec sp_scriptdelproc @artid = articleid;
exec sp_scriptmappedupdproc @artid = articleid;
then, I run 3 stored procedures on Subscriber database
1.CREATE PROCEDURE [CALL sp_MSins_dbot_TableName]
2.CREATE PROCEDURE [CALL sp_MSdel_Dbot_TableName]
3.CREATE PROCEDURE [MCALL_sp_MSupd_dbot_TableName]
Three stored procedures created in Subscriber database (SQL Server 2008).
Any fixes to resolve this issues without drop the articles and run the initial snapshot?
Thanks a lot for your advise and help,
Edwin
December 4, 2009 at 11:17 am
try sp_scriptpublicationcustomprocs
http://support.microsoft.com/default.aspx?scid=kb;EN-US;299903
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
December 4, 2009 at 11:26 am
Hello Vinay,
Yes. I tried with sp_MSins_dbot_TableName, sp_MSdel_dbot_TableName and sp_MSupd_dbot_TableName and applied those in Subscribers.
I was setup the replication using SQL authentication.
execute sp_helppullsubscription @publisher = ‘Publisher server’,
@publisher_db = ‘Database name’;
Go
Publisher = ‘Publisher Server name’
Publisher database = ‘Publisher database’
Publication = ‘Publication Name’
Independent_agent = 1
Subscription type = 1
Dirstibution_agent = ‘Distribution Agent name xxxxxxxx’
Publisher_security_mode = 1
Dritribution_login = ‘SQL login User’
Distribution password = ‘SQL Login User password’
Distribution_security_mode = 0
Distribution_security_mode = 0 indicated (Window Authentication)…not sure why?
December 4, 2009 at 11:39 am
what happens in SQL for you to get the error message:
Could not find procedure 'sp_procname'
it's always security, either you're specifying the wrong schema, or you don't have exec permission.
I'd say try to log in as the distributor account and see if you see the stored procs on the subscription DB.
Craig Outcalt
December 4, 2009 at 12:18 pm
Did you refer to the SQL authentication login did not have privilege on Publisher’s table.
The SQL login all ready assigned “System Admin” and “db owner” on Publisher database.
When I run the initial snapshot, it completed 100 percent. 30 tables copied over to subscriber.
In addition, the sp_addarticle all ready included three parameters:
exec sp_addarticle @publication = N'Publication_Name,
@ins_cmd = N'CALL sp_MSins_dbot_TableName',
@del_cmd = N'CALL sp_MSdel_dbot_TableName',
@upd_cmd = N'MCALL sp_MSupd_dbot_TableName'
GO
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply