December 8, 2004 at 2:12 pm
How can you modify a Stored Procedure?
The one I need to change is for Replication, in the Distribution database, sp_MSadd_merge_agent, and I want to change @retryattempts = 10, to 20. I am doing merge replication and have it set to 1 sec poll time. When the computer is rebooted, I have replication timeout. (UserID=43881)
December 8, 2004 at 4:27 pm
Let me start by saying that it is seriously ill-advised to modify system stored procedures. And I'm not very familiar with replication, so I don't know what the possible impacts are in this situation. One impact that is always the case with modifying system objects is that if you reinstall, upgrade, install a service pack, etc on your SQL installation, the changes you make will be lost.
That being said, to modify a stored procedure, open query analyzer, find the stored procedure that you wish to modify in the object browser, right click and select "script object to new window as..." and then "alter". In your case, I believe "alter" will be unavailable, so you will have to select "create". You would then make the required changes, and execute the script. In your case one of the required changes would be to change the create statement to alter. If you need syntax, you can script a user stored procedure as alter and copy that. However, I'm not sure that it will let you do an alter on that procedure since "alter" is disallowed from the scripting options. You may have to delete the procedure and then "Create" it.
I gotta restate what a bad idea I think this is, though!
Steve
December 9, 2004 at 12:30 pm
I agree with Steve that this is a very bad idea. One reasonable alternative might be to create a new procedure with a different name but with the same code as the system stored proc, and make your modifications to that instead. That way you are protected against the vendors future changes.
December 9, 2004 at 1:25 pm
Joseph,
I thought about that too. In fact, I've done it more than once. I wasn't sure that it would work in this situation though, because the procedure is part of replication. You'd have to find where the procedure is being called and change that as well.
Steve
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply