Solved

Stored Proc Contents Missing

Posted on 2008-10-16
13
222 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
  • 6
  • 6
13 Comments
 
LVL 15

Accepted Solution

by:
jorge_toriz earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

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

Expert Comment

by:jorge_toriz
Comment Utility
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
Comment Utility
Does any other user have privileges to create or modify stored procedures in your database or schema?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:H-SC
Comment Utility
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
Comment Utility
Well.. the modified date is just lower that the time you have encountered problems?
0
 
LVL 1

Author Comment

by:H-SC
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
ok will do that.

many thanks again for your suggestions
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now