Amend SP in SQL 2005 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Amend SP in SQL 2005

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 09:29:34

Please could someone tell me how to modify a sp in 2005. I r/h click on the sp, choose modify and do my changes, but when I save it it saves it under a different name.......

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-28 : 09:36:13

Script the SP, make changes and compile it for changes to take effect.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 09:39:09

COuld you tell me how I do that please. I have used SQL2000 and now have had my db transferred to 2005 and am at a loss of how to do what were simple tasks.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-28 : 09:42:55

Check here how to generate script: http://msdn2.microsoft.com/en-us/library/ms178078.aspx

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 09:46:16

I r/h click on the sp and this appears in r/h pane. Are you saying I cannot just change 101 to 103 in the CONVERT and save it as it was previously called ?

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER Procedure [dbo].[spRMU_CountNoDailyUsers]

AS
SELECT CONVERT(varchar, Log_DateTime, 103) AS Date_Logged_In, Log_Username as Username, COUNT(Log_Username) AS No_Logins
FROM tblUserLog
Where Log_Printed =0
GROUP BY CONVERT(varchar, Log_DateTime, 103) , CONVERT(varchar, Log_DateTime, 101), Log_Username
ORDER BY CONVERT(varchar, Log_DateTime, 101) desc, No_Logins desc
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-28 : 09:48:43

Yes you can do that, but what exactly you mean by "saving of SP"? You will have to comile the SP by pressing F5 for the changes to take effect.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Pinto
Aged Yak Warrior

590 Posts

Posted - 2008-01-28 : 09:54:37

On SQL 2000 all I did was check syntax and click OK or Apply. So, I now have to press F5 is that correct ?

Sara Karasik
Starting Member

10 Posts

Posted - 2008-01-28 : 16:46:01

or you can just click on the red exclamation point or execute.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-29 : 04:12:56

Make use of Query Analyser to do changes

sp_helptext 'procedure_name'

would scipt out the procedure

Copy it to QA
change to alter procedure...
make changes
Select content
Press F5

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources