Saving Stored Procedure

Hello,
I installed SQL Server 2005 on my PC and am using it to access a SQL
database running on a remote server. Using SQL Server Management Studio on
my PC, I connect to the remote database using SQL Server Authentication and
an account that has database owner permissions. At least I think it does -
when I go to the database, then Security / Roles / Database Roles / db_owner
,
it is listed as a role member there.
Once connected, I can change data in tables and view the stored procedures.
However, when I modify one of the stored procedures (by right clicking on it
in Object Explorer, then clicking Modify, then changing it), and then attemp
t
to save the changes, a window pops up on my screen asking me to identify the
location where I want to save the changed stored procedure. It defaults to
a
folder on my local hard drive: My Documents/SQL Server Management
Studio/Projects.
I just want to save the changes in the stored procedure on the remote
database (the master version). Shouldn't I be able to do that? If so, how?
By the way, when I click Modify to view the stored procedure, it's name, at
the top of the window where the T-SQL text is, reads: the address of the
server…SQLQuery1.sql (instead of it's actual name) - for example it reads:
100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
Thanks in advance for help.SSMS opens a Query Window for you when you select to modify the stored proce
dure. Same thing as if
you right-click the server and say "New Query". SSMS also pastes the source
code for the proc in
that query window. As with Word, for instance, a new document has some stand
ard name and when you
select "save" Word (or SSMS) ask you for how you want to name that file.
If you want to execute the ALTER PROCEDURE statement to actually change the
procedure, just execute
the code in the query window.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio o
n
> my PC, I connect to the remote database using SQL Server Authentication an
d
> an account that has database owner permissions. At least I think it does
-
> when I go to the database, then Security / Roles / Database Roles / db_own
er,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored procedures
.
> However, when I modify one of the stored procedures (by right clicking on
it
> in Object Explorer, then clicking Modify, then changing it), and then atte
mpt
> to save the changes, a window pops up on my screen asking me to identify t
he
> location where I want to save the changed stored procedure. It defaults t
o a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so, ho
w?
> By the way, when I click Modify to view the stored procedure, it's name, a
t
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it read
s:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||You don't use the Save button. Use the Execute button (or press F5). This
runs the ALTER PROCEDURE script you have modified. The Save button is for
saving scripts on your machine, not for saving changes to the database...
"conmat" <conmat@.discussions.microsoft.com> wrote in message
news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
> Hello,
> I installed SQL Server 2005 on my PC and am using it to access a SQL
> database running on a remote server. Using SQL Server Management Studio
> on
> my PC, I connect to the remote database using SQL Server Authentication
> and
> an account that has database owner permissions. At least I think it
> does -
> when I go to the database, then Security / Roles / Database Roles /
> db_owner,
> it is listed as a role member there.
> Once connected, I can change data in tables and view the stored
> procedures.
> However, when I modify one of the stored procedures (by right clicking on
> it
> in Object Explorer, then clicking Modify, then changing it), and then
> attempt
> to save the changes, a window pops up on my screen asking me to identify
> the
> location where I want to save the changed stored procedure. It defaults
> to a
> folder on my local hard drive: My Documents/SQL Server Management
> Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
> By the way, when I click Modify to view the stored procedure, it's name,
> at
> the top of the window where the T-SQL text is, reads: the address of the
> server…SQLQuery1.sql (instead of it's actual name) - for example it
> reads:
> 100.100.100.100…SQLQuery1.sql. Not sure if that's relevant or not.
> Thanks in advance for help.|||conmat (conmat@.discussions.microsoft.com) writes:
> However, when I modify one of the stored procedures (by right clicking
> on it in Object Explorer, then clicking Modify, then changing it), and
> then attempt to save the changes, a window pops up on my screen asking
> me to identify the location where I want to save the changed stored
> procedure. It defaults to a folder on my local hard drive: My
> Documents/SQL Server Management Studio/Projects.
> I just want to save the changes in the stored procedure on the remote
> database (the master version). Shouldn't I be able to do that? If so,
> how?
But you should really save the code to disk and put it under version
control. The datbase should just be viewed as a binary repository.
Of course, as long as you are only playing around, the database may do,
but it's not suitable for real application development.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you for your help.
I don't think I understand the difference between:
1) Modifying the script in a stored procedure on the database without
actually running the content / code of the procedure
versus
2) Running the actual content / code of the procedure.
If I right click on a stored procedure, choose modify, the script of the
procedure pops up in the Query Window(?), and it starts with:
ALTER PROCEDURE ____
AS
If I then modify the script, then Execute, does that just change the script
without actually running the content (I think the answer is yes, if I
understand you correctly)?
If that is so, how do I then actually execute the content - do I need to
remove the ALTER PROCEDURE___ AS, then Execute (but, how do I remove the
ALTER PROCEDURE without altering the procedure)?
I'm very new to SQL Sever, as you can tell - thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> You don't use the Save button. Use the Execute button (or press F5). Thi
s
> runs the ALTER PROCEDURE script you have modified. The Save button is for
> saving scripts on your machine, not for saving changes to the database...
>
>
> "conmat" <conmat@.discussions.microsoft.com> wrote in message
> news:8A0F10A3-9E54-41F1-9921-AA6433D15FBE@.microsoft.com...
>
>|||> 1) Modifying the script in a stored procedure on the database without
> actually running the content / code of the procedure
> versus
This changes the code that SQL Server has for the stored procedure. This
means it will change what is stored, and will change what is executed every
time anyone calls the stored procedure.

> 2) Running the actual content / code of the procedure.
This merely runs the code, once.
Consider:
CREATE PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 1;
END
GO
When you run this code, it creates a procedure. It does not run the SELECT
statement. When you *EXECUTE* the stored procedure:
EXEC dbo.foo;
You will see a single-column resultset with the value 1. Just like you
would have seen if you ran the code within the procedure, by itself:
SELECT a = 1;
Now, if you run this:
ALTER PROCEDURE dbo.foo
AS
BEGIN
SELECT a = 2;
END
GO
Again, it does not run the SELECT statement, it changes the script that SQL
Server has stored for the procedure. So the next time someone executes the
stored procedure, they will get a single-column resultset with the value 2.

> If that is so, how do I then actually execute the content
Well, if you have already run the ALTER and you are happy with it, then like
above:
EXEC dbo.foo;

> I'm very new to SQL Sever, as you can tell - thanks again for your help.
I think there is plenty of material out there that you should familiarize
yourself with.
http://www.aspfaq.com/2423|||Hey Guys,
The Orginal question was how to save a modified stored procedure.
I read all your reponses but none addressed the issue.
I have a similar problem
Can anyone just simple show how to save a modified sp in sql server managem
ent studio.
Thanks|||> The Orginal question was how to save a modified stored procedure.
Save where? Do you want to create a file containing the TSQL code used to cr
eate that procedure? Or
do you want to execute the ALTER PROCEDURE ... command so you do modify that
procedure according to
the changes you have made?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"sportrx" <sportrx.24qulq@.mail.codecomments.com> wrote in message
news:sportrx.24qulq@.mail.codecomments.com...
> Hey Guys,
> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
> I have a similar problem
> Can anyone just simple show how to save a modified sp in sql server
> management studio.
> Thanks
>
> --
> sportrx
> ---
> Posted via http://www.codecomments.com
> ---
>|||> The Orginal question was how to save a modified stored procedure.
> I read all your reponses but none addressed the issue.
That's because the original question remains unclear.

> Can anyone just simple show how to save a modified sp in sql server
> management studio.
What does "save a modified sp" mean? Do you want to apply the changes to
the database, save the script in a .PRC or .SQL file, check the changes into
source control, ...?
If you run an ALTER PROCEDURE script, that will apply the changes to the
stored procedure in the current database. If you want to save the script to
a file, click the Save icon. Those are two *very* different actions.
A