Solved

Stored Proc Contents Missing

Posted on 2008-10-16
13
268 Views
Last Modified: 2012-08-13
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???
0
Comment
Question by:H-SC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
13 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 500 total points
ID: 22732172
mmm... nop, somebody deleted your code... sorry... it just not happens.   SQL Server didn't make that
0
 
LVL 1

Author Comment

by:H-SC
ID: 22732297
jorge_toriz,
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
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22732351
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?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 1

Author Comment

by:H-SC
ID: 22732392
yes this is all that I see when I request modify script.
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22732410
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_name'
0
 
LVL 13

Expert Comment

by:AielloJ
ID: 22732421
Does any other user have privileges to create or modify stored procedures in your database or schema?
0
 
LVL 1

Author Comment

by:H-SC
ID: 22732455
It has a create date of 2008-10-14 15:53:15.527
and modified date of 2008-10-16 11:32:19.167
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22732496
Well.. the modified date is just lower that the time you have encountered problems?
0
 
LVL 1

Author Comment

by:H-SC
ID: 22732525
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
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22732587
Soooo weird... this date (modify_date) in sys.objects view only updates when you modify the structure of your object
0
 
LVL 1

Author Comment

by:H-SC
ID: 22732652
oh well, looks like I am going to be re-typing my sql!

many thanks for your time
0
 
LVL 15

Expert Comment

by:jorge_toriz
ID: 22732701
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
0
 
LVL 1

Author Comment

by:H-SC
ID: 22732722
ok will do that.

many thanks again for your suggestions
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question