April 17, 2011 at 6:48 pm
Hi All,
I have been getting this error now for sometime. I would appreciate any feedback on the issue.
I can reinitialize my subscriptions each morning and they run till midnight when they fail.
My SQL Servers are both SQL Server 2005 with SP3. This was a new replication database created on the new production server. I really do not want to have to uninstall a production server and reinstall. Two other servers were setup the same time and do not have the missing parameter in the stored procedure. They were all built the same way. The only restore of DB was the Database we publish from.....
I get the following error on my log reader once I setup subscriptions to my Publisher. My Publisher and Distributor are the same server.
'Procedure or function sp_MSadd_logreader_history has too many arguments specified.'.
After reading a few articles online I find that my stored procedure "sp_MSadd_logreader_history" in fact is missing one value when I compare it to my test server replication, which is running fine and another production server which is fine.
This is on my Production Server
CREATE PROCEDURE sys.sp_MSadd_logreader_history
(
@agent_id int,
@runstatus int,
@comments nvarchar(4000),
@xact_seqno varbinary(16) = NULL,
@delivery_time int = 0, -- Current delivery time (milliseconds)
@delivered_transactions int = 0, -- Running total of session
@delivered_commands int = 0, -- Running total of session
@delivery_latency int = 0, -- Current latency
@log_error bit = 0,
@perfmon_increment bit = 1,
@update_existing_row bit = 0,
@do_raiserror bit = 1
)
AS
BEGIN
This is on my Test Server
CREATE PROCEDURE sys.sp_MSadd_logreader_history
(
@agent_id int,
@runstatus int,
@comments nvarchar(4000),
@xact_seqno varbinary(16) = NULL,
@delivery_time int = 0, -- Current delivery time (milliseconds)
@delivered_transactions int = 0, -- Running total of session
@delivered_commands int = 0, -- Running total of session
@delivery_latency int = 0, -- Current latency
@log_error bit = 0,
@perfmon_increment bit = 1,
@update_existing_row bit = 0,
@do_raiserror bit = 1,
@updateable_row bit = 1 -- used to override history verbose level to decide
-- whether the row being added can be updated by another.
)
AS
BEGIN
A couple of questions.......
1. How can the stored procedures be different for replications between what we thought were Identically built SQL Servers ?
2. If I script the Stored Procedure that is working on my test server or other production server and run an Alter SP on my live server will this bring my log reader back in line?
3. If I run the script how will that affect my current replication publications and subscriptions.
4. Can I restore the Replication database on my other Production box to the one that is not working and then re-setup my publications etc....
Appreciate any feedback
Cheers
Angie :w00t:
April 20, 2011 at 7:13 am
I'm having this problem too, but unfortunately I have not found any good resolutions yet.
The problem seems to stem from your database not being upgraded properly because either your database was replicated from a 2000 server or because your MachineName and SQL Server Instance name had different cases (one lower case, the other upper case) and the Service Pack didn't apply the patch correctly.
Apparently logread.exe is calling sp_MSadd_logreader_history with 13 arguments but your systems sp only has 12 arguments.
I have read from a knowledge base article (dont remember which) that if you apply SP3 that it might fix the problem. Other than that, re-installing the SQL instance seems to be the only other fix.
I have not tried to install SP3 yet and re-installing SQL is not an option for me. Still looking for a solution.
April 20, 2011 at 8:59 am
Are the compatibility levels the same for both databases? Even though you have the same version the compatibility level may change what is available in some functionality.
Let me know.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
April 20, 2011 at 2:16 pm
Hi all, thanks for your replies.
Appreciate hearing that I am not the only one on earth having this issue.
I may try the SP3 again but will test it out.
I did go through a rename process on that server but I did not have replication installed. I installed it after..... but I have done that with another server built around the same time and renamed it and then installed replication and it seems ok.
Might just move my replications to the server that works for the moment and go from there.....
Will let you know if the SP3 works.
Cheers
Angie :w00t:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply