What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Modifiying an SP
Posted on 2009-05-17
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?
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?
[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
3
10 Comments
>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
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
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.
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.
are you reopening the sp through management studio? can you post the code that you're running to alter your proc?
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
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]
run this instead.
ALTER procedure [dbo].[GetCDEMannounce]
as
select FullItem
from dbo.CDEMAnnounce
where StartDate <= getDate()--dateadd(d,-1,ge
and ExpireDate > getDate()
order by id desc
GO
GRANT EXECUTE ON [dbo].[GetCDEMannounce] TO [public]
ALTER procedure [dbo].[GetCDEMannounce]
as
select FullItem
from dbo.CDEMAnnounce
where StartDate <= getDate()--dateadd(d,-1,ge
and ExpireDate > getDate()
order by id desc
GO
GRANT EXECUTE ON [dbo].[GetCDEMannounce] TO [public]
yep, I do... by ignoring all the other text in the modify dialogue and just selecting/running the actual alter procedure statement.
But why?
But why?
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
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?
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?
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.
Featured Post
What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I've encountered valid database schemas that do not have a primary key. For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis. However, occasionally due to user error or a scheduled task…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month3 days, 15 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.