Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There's a Stored Procedure in my database which keeps restoring to the old one, I Alter the stored procedure using Management Studio, however some time later, unexpectedly, the stored procedure changes to the one before Alter! This has happened several times during past hours!

I am certain that nowhere inside the program or manually I change the Stored Procedure. I checked all Agent Jobs, There's only one Maintenance job for hourly backup, and another one for Replication, however Replication Schema Changes is set to false, and Stored Procedures are not defined in the list of Publication Articles

Is there any other possible reason for this behavior? Do I need to manually set a CHECKPOINT in the SQL Server database?

share|improve this question
7  
You probably want a DDL trigger for this. Here's one written by some guy who probably doesn't know much. –  JNK May 30 '12 at 16:24
    
is this Stored Procedure auto generated from an application, may be when this application update its self, it regenerate the stored procedure –  AmmarR May 30 '12 at 20:50
    
The OP indicates the issue is resolved in the comments here. It was replication. –  Martin Smith May 31 '12 at 7:39
    
Thank you everyone, It was the Publication database that kept changing my Stored Procedure whenever Replication Agent is running! as I mentioned here Thanks again –  Kamyar Nazeri May 31 '12 at 17:36

3 Answers 3

A common problem when developers involves the schema that SQL Server assumes when creating or altering an object. This is especially likely in the following circumstances:

  1. The developer has recently been removed from the db_owner role The developer is working on a server that they normally don't.
  2. They are likely to see the problem on a production server (where their permissions are tightly controlled) rather than a development server (where they might be a member of db_owner or even sysadmin).

This problem is often described by developers as "SQL Server is using the old version of my proc". What really happens is that the developer is modifying a copy of the procedure and executing a different copy of the procedure.

The quick way out of this is to ensure that you are providing a schema for the stored procedure with your drop, create, alter and execute statements. In SQL Server, the vast majority of the time, that schema is "dbo". In other words, prefer this:

alter procedure dbo.foo
as
select 1
go 
exec dbo.foo

and avoid this:

alter procedure foo
as
select 1
go 
exec foo
share|improve this answer

My guess would be that the procedure you are changing isn't suppose to be getting changed and someone else is changing it back.

Seeing as how the only way that a stored procedure can be changed is by running at ALTER PROCEDURE statement, that's probably what's happening.

share|improve this answer

open up your SQL profiler, do your changes to the stored procedure, and leave it open. and check all processes that run, you should be able to know which process is hitting your store procedure and accordingly take action

share|improve this answer
3  
You just want to capture all statements to track it down, using the Profiler GUI? This could bring a busy system to its knees. A DDL trigger pointed at ALTER_/DROP_/CREATE_PROCEDURE is a much more targeted and pain-free tracking mechanism IMHO. –  Aaron Bertrand May 30 '12 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.