November 10, 2011 at 3:39 pm
While I have achieved a "solution" to this issue, I am keen to understand just what has happened. So the purpose of this post revolves around "enlightenment" rather than "solution".
In a nutshell, I would make changes to a stored procedure, execute it, and test it. The next day I would look at it again to continue the development, and the previous day's changes had gone.
When this first happened, I had made some changes on a Friday, and found them missing on the Monday. Working from the principle that "a good workman never blames his tools", I thought I must have done something silly when I closed down on the Friday afternoon. I therefore took more care when I closed down on Monday evening.
On Tuesday morning, I re-opened the stored proc to find it had reverted back to the previous week again. My first thought then was that somebody was fiddling around with my development server and had restored from backup. A check of the server event logs turned up nothing, so rather than tackle anyone without proof, I decided to be patient and set about creating some. I added a new table and a new stored to the database I had been developing on, and added a text file into the folder in which the db files are stored.
On Wednesday morning I found all those objects in place and unchanged, but my stored procedure had once again reverted to the previous week's version. There are only two of us who work on that server and I checked with my colleague to make sure he was not playing a practical joke (by now my sense of humour would have been strained) or doing anything which could have this effect. No joy. Google searching turned up nothing although I'm sure there will be something out there if I use the correct combination of words.
Before continuing my development on Wednesday I dropped the stored procedure and re-created it note with the same name). When I finished work that day, I again took particular care when I closed down. Before logging off completely, I re-opened Management Studio and made a final check. The stored proc was OK.
Thursday morning left me with the same result - stored procedure reverted to the previous week and me none the wiser as what might be going on. Before closing down for the day, I not only saved my script back to its file (my normal practice) but I pasted the code for it into the dummy stored procedure (called sp_Bob) I had created earlier in the week. I then dropped the stored proc I had been developing.
Today, Friday, I found that the dropped stored procedure had stayed dropped (I would have been really worried if it had somehow re-created itself) and the code I had pasted into sp_Bob as I had left it.
My conclusion from all this is that SQL Server had somehow tangled itself in relation to the stored proc - maybe something stuck in tempdb?? I will create a totally new sp today in the hope that it will remain intact on Monday.
If anyone has had a similar experience or has some wisdom to offer, I would appreciate a reply post.
I am using the 2008 tools from my desktop PC (Winwoes 7) to maintain a MSSQL 2008 (64bit; SP1) server (Windows Server 2008).
Thanks.
November 10, 2011 at 3:55 pm
Sounds like the proc definition has been scripted in a job or something. SQL Server won't overwrite unless you tell it to. You could set up a DDL trigger and record exactly when your object is changing and by what account.
November 10, 2011 at 5:07 pm
I agree, it does sound like it has found its way into a job or similar scheduled task, but nothing like that exists on this server.
As far as the DDL trigger is concerned, its good advice and I had already put it place this morning before I made my post (forgot to include that in my summary). I just wish I had thought about it sooner. Having dropped the offending sp, I suspect I have also dropped my problem - at least I hope so. I wouldn't like to think of the impact of this problem showing up on all the sps I maintain - that is a scary thought.
Thanks for the post.
Cheers
November 11, 2011 at 12:58 am
Well I can understand the scenario & your worries / frustration as well. I have experienced it. I don’t remember exact code that caused the issue BUT here is something to understand.
CREATE PROCEDURE P1
AS
..
..
..
ALTER PROCEDURE P2
AS
..
..
..
Can you guess what’s wrong above? ‘GO’ statement.
CREATE PROCEDURE P1
AS
..
..
..
GO
ALTER PROCEDURE P2
AS
..
..
..
In first case, Procedure P1 alters P2 every time it execute because accidently P2’s definition is attached to P1.
GO statement prevent this in second case. Alternatively, you may add BEGIN and END in SP definition (as best practice).
CREATE PROCEDURE P1
AS
BEGIN
..
..
..
END
GO
ALTER PROCEDURE P2
AS
BEGIN
..
..
..
END
November 11, 2011 at 1:27 am
Object changes are recorded in the default trace, so the details of when the change happened should already be available if the the default trace files haven't rolled over yet.
November 11, 2011 at 1:48 am
Dev (11/11/2011)
Well I can understand the scenario & your worries / frustration as well. I have experienced it. I don’t remember exact code that caused the issue BUT here is something to understand.
CREATE PROCEDURE P1AS
..
..
..
ALTER PROCEDURE P2
AS
..
..
..
Can you guess what’s wrong above? ‘GO’ statement.
CREATE PROCEDURE P1AS
..
..
..
GO
ALTER PROCEDURE P2
AS
..
..
..
In first case, Procedure P1 alters P2 every time it execute because accidently P2’s definition is attached to P1.
GO statement prevent this in second case. Alternatively, you may add BEGIN and END in SP definition (as best practice).
CREATE PROCEDURE P1AS
BEGIN
..
..
..
END
GO
ALTER PROCEDURE P2
AS
BEGIN
..
..
..
END
Dev
I don't think this can be an explanation, since ALTER PROCEDURE is not allowed in a procedure definition. See the Limitations and Restrictions section on this page:
http://msdn.microsoft.com/en-us/library/ms187926.aspx
John
November 11, 2011 at 2:11 am
Dev (11/11/2011)
Well I can understand the scenario & your worries / frustration as well. I have experienced it. I don’t remember exact code that caused the issue BUT here is something to understand.
CREATE PROCEDURE P1AS
..
..
..
ALTER PROCEDURE P2
AS
..
..
..
Can you guess what’s wrong above?
CREATE PROCEDURE P1
AS
SELECT 1;
ALTER PROCEDURE P2
AS
SELECT 2;
Msg 156, Level 15, State 1, Procedure P1, Line 5
Incorrect syntax near the keyword 'PROCEDURE'.
I've seen that done with DROP, but it can't be done with ALTER or CREATE as neither is permitted within a procedure definition. A proc with one in simply won't create.
GO statement prevent this in second case. Alternatively, you may add BEGIN and END in SP definition (as best practice).
GO and BEGIN ... END are not synonymous (and personally I never wrap my procedures in an unnecessary and unrequited BEGIN.. END. It just adds dead weight.)
CREATE PROCEDURE P1
AS
BEGIN
SELECT 1;
END
ALTER PROCEDURE P2
AS
BEGIN
SELECT 2;
END
This still throws the same error.
The thing with BEGIN and END is you can put them around any code block, not just around procedures or around IF... ELSE statement blocks. This is perfectly permitted, but insane.
CREATE PROCEDURE HardToRead
AS
BEGIN
SELECT 1;
BEGIN
SELECT 2;
BEGIN
SELECT 3;
BEGIN
SELECT 4;
BEGIN
SELECT 5;
END
END
END
END
END
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 2:23 am
ProofOfLife (11/10/2011)
My conclusion from all this is that SQL Server had somehow tangled itself in relation to the stored proc - maybe something stuck in tempdb??
Not how things work. The ACID property Durability applies to objects as well as data (objects are just data in a system table). Once the transaction commits (and for a create or alter procedure that's once the statement completes), the change is part of the permanent database state,
I've seen things like this reported on forums many times. It always turns out that there's a job running, a colleague altering, a backup being restored, the person looking in the wrong DB or on the wrong server.
Default trace will give you lots of nice info including (iirc) host name, login name, date and time. That should enable you to start tracking this down.
p.s. Source Control. Just because.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 11, 2011 at 2:34 am
I started my post with this.
I don’t remember exact code that caused the issue BUT here is something to understand.
I agree on the limitations of CREATE PROCEDURE. On the same time I stick to my statements because it is something I faced / resolved myself last year. I don’t have access to source code (different project / customer) right now but I would try to reproduce it once again on my end.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply