May 6, 2009 at 2:33 pm
In the middle of my transaction replication, the distributor agents continued errors on:
Could not find stored procedure 'sp_MSint_t_ElecConnData'
Command attempted:
if @@trancount > 0 rollback tran (Transaction sequence number: 0x000001D700008478000100000000, Command ID: 2)
Error messages:
Could not find stored procedure 'sp_MSins_t_ElecConnData'. (Source: MSSQLServer, Error number: 2812)
Get help: http://help/2812
Could not find stored procedure 'sp_MSins_t_ElecConnData'. (Source: MSSQLServer, Error number: 2812)
Get help: http://help/2812
I'm not so familiar with these errors.
Can someone help and advice on this?
Thanks,
Edwin
May 6, 2009 at 3:27 pm
Usually this happens when you set up transactional replication with no Snapshot of Data (manual synchronization). When you do that, the Publisher marks the table to be replicated, the distributor gets the commands but the Subscriber does not create the replication stored proc.
This happens in SQL 2000 but not confirmed in SQL 2005. To fix this, you have to run sp_scriptpublicationcustomprocs with the Publication name as parameter. This will generate a Stored proc script. (make sure your Output for your SSMS is set to more than 8600 so that the full text is shown on the message window). Copy this script and run it in the subscriber.
Please test i9t in a test environment if you can before you do this in your Production.
-Roy
May 6, 2009 at 4:19 pm
Hi Roy,
Thanks for hints. What did you mean by no Snapshot of Data (Manual synchronization).
How to overcome this problems for future setup? Did I need to setup snapshot of data (auto synchronization)?
Enclosed is my setup scripts with the following env:
1.Publisher (SQL Server 2000 SP3)
2.A Remote Distributor (SQL Server 2008)
3.A Subscriber (SQL Server 2008) in non-trusted environment.
-- Adding the transactional publication
-- Adding the transactional publication
exec sp_addpublication @publication = N'Xxxxx_Site',
@restricted = N'false', @sync_method = N'concurrent',
@repl_freq = N'continuous',
@description = N'Transactional publication of database ''xxxxData'' from Publisher ''xxxxServer''.',
@status = N'active', @allow_push = N'true', @allow_pull = N'true',
@allow_anonymous = N'false', @enabled_for_internet = N'false',
@independent_agent = N'true', @immediate_sync = N'false',
@allow_sync_tran = N'false', @autogen_sync_procs = N'false',
@retention = 0, @allow_queued_tran = N'false',
@snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false',
@ftp_port = 21, @ftp_login = N'anonymous', @allow_dts = N'false',
@allow_subscription_copy = N'false', @add_to_active_directory = N'false',
@logreader_job_name = N'xxxxServer-xxxxData-32'
GO
exec sp_addpublication_snapshot @publication = N'xxxxx_Site',
@frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0,
@frequency_subday = 8, @frequency_subday_interval = 1,
@active_start_date = 0, @active_end_date = 0,
@active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@snapshot_job_name = N'xxxxServer-xxxxData-xxxxData-33'
GO
I execute sp_scriptpublicationcustomprocs @publication = ‘xxxxPublicationName’ and purge the result in file. Then I execute the scripts one step at a time in subscriber db instance. On certain step, run into errors: Procedure sp_MSins_t_ElecConnData, Liine 2 Incorrect syntax near the keyword ‘AS’ etc.
If I am planning to re-setup my transaction replication, would please advise how to overcome this issue?
Thanks in advance.
Edwin
May 7, 2009 at 12:29 pm
Did you make sure that you set the Buffer length of your output to a high amount like 8000 Characters? Seems like the whole scripts was not generated from the error you are giving.
Just out of Curiosity, when you set up replication, how did you transfer the data across to the subscriber? Did you do a restore of your Publisher DB? That would help me when telling you what you have to do.
-Roy
May 7, 2009 at 1:32 pm
I am using the initial snapshot.
May 8, 2009 at 8:08 am
On certain step, run into errors: Procedure sp_MSins_t_ElecConnData, Liine 2 Incorrect syntax near the keyword ‘AS’ etc.
The Error indicates that the Replication "ins" ( insert procedure for table t_ElecConnData) is missing at subscriber.
My guess is that when you got that error the stored procedure was not created on the subscriber.
If you are using initial snapshot you don't have to script the procedures out unless you are doing something custom within them.
* Noel
May 8, 2009 at 9:15 am
I never seen those errors when my subscribers was running in SQL Server 2005 SP2.
May 8, 2009 at 9:19 am
Did you find "sp_MSins_t_ElecConnData" on the subscriber?
* Noel
November 29, 2010 at 3:02 pm
Noel et al.,
I hate to bring this topic back from the graveyard, but I am experiencing this same issue with a delete script (MSdel) and I was wondering if this ever came to a resolution. I do have the SP on the subscriber DB and have even dropped and recreated it out of curiosity, to no avail. I seem to have the same issues as the OP, where I was replicating from SS2000 to a SS2005 distributor/subscriber. My Infrastructure Manager has decided to decommission the old SS2005 machine in lieu of a new SS2008 distributor/subscriber, but I'm not having any luck keeping the replication running.
Patrick
November 30, 2010 at 5:58 am
The sp that it says is missing, where is it? Is it in your user DB that is being replicated or is it in your Master DB? Did you change the SQL service and Agent account by any chance?
-Roy
November 30, 2010 at 6:39 am
Roy,
The SP that says it's missing is in the subscriber DB. I don't believe that I changed the service or agent account, but I have knocked out the entire DB and had it recreate again and in the process, trying to set things back up from memory. What would be a good way to double check those logins? How do I find the logins that I have set up? Should I just go back to the Publisher DB and make sure those logins have the right permissions?
Please forgive me if some of these questions are amateurish. The DBA at my company went on medical leave and I've had to teach myself a lot of what he was doing.
Patrick
November 30, 2010 at 7:09 am
Here are some basic questions.
Is the Distributor shared by the publisher or subscriber or is the publisher, subscriber and distributor on the separate servers?
The distributor should have have Sysadmin rights on the subscriber. Log reader agent should have sysadmin rights on the publisher.
-Roy
November 30, 2010 at 7:58 am
Roy,
Checked all of that out and it looked fine, but everything had frozen due to the HDD filling up. I purged that to free up about 500 GB, then started everything again, but now like I believe you figured, the log reader says it can not connect to the distributor. What did I do now?
Patrick
November 30, 2010 at 8:07 am
Under what account does your SQL Server Agent run? Is this a domain account? Is this account a Sysadmin on the distributor?
-Roy
November 30, 2010 at 8:12 am
It is both of those things. 🙂 It is a domain that is sysadmin on both the publisher and the distributor/subscriber (since they're both on the same machine)
Viewing 15 posts - 1 through 15 (of 39 total)
You must be logged in to reply to this topic. Login to reply