Stored Proc Contents Missing
I created a large stored proc a couple of days ago and it had been running fine and updating my tables as intended. I noticed this morning, that my data was not being updated via the proc and went into sql 2005 management studio right clicked on the procedure under stored procedures, .. clicked modify and noticed that the proc had only 10 lines of system generated code with the name of my procedure in it, but none of the sql that I wrote for this procedure. what has happened, am I missing something???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Let me clarify my ideas... did your stored procedure code gone away?... is that the only code that you see when you request a modify script?
ASKER
yes this is all that I see when I request modify script.
Check this query to see the last time your stored procedure was modified:
SELECT name, create_date, modify_date
FROM sys.objects WHERE name = 'your_stored_procedure_nam
SELECT name, create_date, modify_date
FROM sys.objects WHERE name = 'your_stored_procedure_nam
Does any other user have privileges to create or modify stored procedures in your database or schema?
ASKER
It has a create date of 2008-10-14 15:53:15.527
and modified date of 2008-10-16 11:32:19.167
and modified date of 2008-10-16 11:32:19.167
Well.. the modified date is just lower that the time you have encountered problems?
ASKER
I noticed that as well. When I have the sp open and click on execute it updates the modified date that is probably the reason. I ran the sp in its current condition and it updated the modified date
Soooo weird... this date (modify_date) in sys.objects view only updates when you modify the structure of your object
ASKER
oh well, looks like I am going to be re-typing my sql!
many thanks for your time
many thanks for your time
Yep, somebody modified your stored procedure... I can bet for that
Implement a DLL Trigger to audit your changes, catch the group DDL_DATABASE_LEVEL_EVENTS
Implement a DLL Trigger to audit your changes, catch the group DDL_DATABASE_LEVEL_EVENTS
ASKER
ok will do that.
many thanks again for your suggestions
many thanks again for your suggestions
ASKER
here is what I see when I click modify.... It also will execute successfully
USE [MYDATABASE]
GO
/****** Object: StoredProcedure [dbo].[MYSP] Script Date: 10/16/2008 11:16:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[MYSP]
as
set nocount off