Solved

Modifiying an SP

Posted on 2009-05-17
10
220 Views
Last Modified: 2012-05-07
This is embarrassing to type - I am sure I must be missing something obvious.
In sql 2000 when I wanted to modify an sp (and was feeling lazy) I would right click it (EM) , choose properties alter the text and execute it.

I want to alter a SP in 2005 so I right click (SSMS) and choose modify.
First thing I notice is that the actual SP itself is in quotes and is a parameter for dbo.sp_executesql
So (inside the quotes) I make my change. Very simple, all I need to do is add an oder by.
I run it and all seems fine, but the data is not ordered by anything. I revisit the SP (modify) and my changes are gone.
If I re-type the changes and run the alter SP statement by itself then all is fine.

What is the purpose of this right click modify command if it's not going to make/save my changes?
0
Comment
Question by:QPR
  • 6
  • 3
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24407648
>So (inside the quotes) I make my change
once you made your changes, you have to complile that sp by pressing F5, otherwise the changes made on the sp wont remain there. I hope you missed to do that
0
 
LVL 29

Author Comment

by:QPR
ID: 24407661
I click "execute"
Command(s) completed successfully.

I close the window and am asked if I want to save sqlquery01.sql and I say no.
I reopen the SP and my changes are not there.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24408028
are you reopening the sp through management studio?  can you post the code that you're running to alter your proc?  
0
 
LVL 29

Author Comment

by:QPR
ID: 24408070
yes everything via SSMS
In the example below, I can delete order by id desc.
Then I will execute and it will say all good.
I run the SP (from the web front end) and the order by will still be in effect.
Go back to SSMS and the orderby is still part of the sql


USE [Intranet]

GO

/****** Object:  StoredProcedure [dbo].[GetCDEMannounce]    Script Date: 05/18/2009 10:15:14 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCDEMannounce]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @statement = N'ALTER procedure [dbo].[GetCDEMannounce]

as

select FullItem

from dbo.CDEMAnnounce

where StartDate <= getDate()--dateadd(d,-1,getDate())

	and ExpireDate > getDate()

order by id desc' 

END

GO

GRANT EXECUTE ON [dbo].[GetCDEMannounce] TO [public]

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24408353
run this instead.

ALTER procedure [dbo].[GetCDEMannounce]
as
select FullItem
from dbo.CDEMAnnounce
where StartDate <= getDate()--dateadd(d,-1,getDate())
      and ExpireDate > getDate()
order by id desc

GO
GRANT EXECUTE ON [dbo].[GetCDEMannounce] TO [public]

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 29

Author Comment

by:QPR
ID: 24408777
yep, I do... by ignoring all the other text in the modify dialogue and just selecting/running the actual alter procedure statement.

But why?
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 500 total points
ID: 24408785
because that is how it should be done...I wish I knew why MS decided it was a better idea to generate dynamic SQL when you use an IF object exists clause...you can go to tools, options, and turn that option off.  If you don't use the option to drop the object if it exists, it doesn't use the dynamic sql
0
 
LVL 29

Author Comment

by:QPR
ID: 24408856
Can I clarify unless I'm missing something...
I navigate to the SP in SSMS, right click and choose modify.
Amend the text and then execute. Get the message that the command ran successfully.
But in reality the changes were not made/saved and the changed have not propogated to the DB?
0
 
LVL 29

Author Comment

by:QPR
ID: 24408868
sorry the "but why" was not directed at your uiggestion, it was directed at why would MS give me this "modify" option that doesn't appear to work.
0
 
LVL 29

Author Closing Comment

by:QPR
ID: 31582425
that works (changing the options) thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Why does this keep coming up NULL? 2 44
How to print the sql query 18 51
SQL Select * from 6 36
Following an example - removing duplicate strings 4 50
There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

910 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

16 Experts available now in Live!

Get 1:1 Help Now