INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Saving stored procedures

Saving stored procedures

(OP)
I am running SQL 2005. I have a stored procedure that I wanted to modify. When I modify it I get a message to modify an SQL procedure which I say no to, so how do I save the stored procedure?

RE: Saving stored procedures

Save it as a text file?

RE: Saving stored procedures

What does the message say? If you havent got permission then you cant save the stored procedure.

You can save a script of a stored procedure (As per previous poster) but you cannot save the stored procedure.

Dan

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

RE: Saving stored procedures

(OP)
How do I save the script then?

RE: Saving stored procedures

What do you mean by this? What is the message that you are saying no to?

Quote:


When I modify it I get a message to modify an SQL procedure which I say no to,

It sounds like you are telling us that you modify the stored procedure and then you get a message asking if you really want to modify it and you say no. Why would you say no to modifying a stored procedure when that is what you are trying to do? Sounds like there is something missing in your post and it's making your question confusing.

Are you really trying to modify an existing stored procedure or are you trying to make a copy of a stored procedure so you can work with it (like for testing)?

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875: What should I know before I post?

RE: Saving stored procedures

Quote (vba317)

How do I save the script then?

In management studio - when you have the script open, go to file save as. This will then save an SQL file which contains the code to make the stored procedure.

Your terminology is quite confusing however so this may not be what you are trying to achieve. If you want more answers you need to define what you are trying to achieve and what is causing you an issue (error message, steps taken, dont know how to do X etc).

Dan

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

RE: Saving stored procedures

(OP)
I will try to be clearer. What I am talking about is if I have a stored procedure. I make a modification. I get the message to save the procedure. A message comes up say to save the procedure as Sql1. I save the procedure as Sql1. When I close that session and open studio manager again. Go to the programmability section. The procedure was not saved.

RE: Saving stored procedures

When you execute a "Create Procedure" query, it is saved in the database at that time. You can save the code to a file if you want, but the only way to use a stored procedure is to create it and then you can run it.

Try this:

Open SQL Server Management Studio.
Connect to your user database.
Open a new query window.
Copy/paste the following code.

CODE

Create Procedure HelloWorld
AS
If DatePart(Hour,GetDate()) < 12
    Select 'Good Morning'
Else If DatePart(Hour,GetDate()) < 5 
    Select 'Good Afternoon'
Else
    Select 'Good Evening' 

Now run the code. When running it, you will not see the results of the query, but the stored procedure will be saved to the database. To run the code, press the F5 key, or click "Query" -> Execute, or click the green triangle pointing to the right.

At this point, the query is saved. Now do this...

Click the "New Query" button.
Copy/paste the following code to the window.

CODE

Exec HelloWorld 

Now run this code. You will see the results of the query (Good morning, Good afternoon, or Good Evening).

If you close the SQL Server Management Studio application and then open it again, you will be able to re-run the "Exec HelloWorld" procedure again without having to do anything extra.

In SQL Server Management Studio, you probably see an "Object Explorer" window. If you don't, click the View menu item, and then Object Explorer. Expand "Databases", Expand your user database, expand programability, expand "Stored Procedures". You should see the "Hello World" stored procedure.

You can expand the stored procedure to see the parameters. You can right click on the stored procedure modify, execute, delete, rename etc....

When you are done with this example, please do not forget to delete the HelloWorld stored procedure.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Saving stored procedures

Nice George. You forgot the part about ALTER PROCEDURE.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: Saving stored procedures

After you "save" a procedure, you need to refresh the list of stored procedures. You should see your changes after that.

RE: Saving stored procedures

djj55,

Not everyone likes to use Alter Procedure. The reason is that the meta data for it doesn't always work the way you think it should.

If you run this...

CODE

Select * From Information_Schema.Routines 

You will see a list of your stored procedures. One of the columns is "Created" and another is "LAST_ALTERED". The Last Altered column is not always correct, based on SQL version and perhaps some other things too. As a result of this, some people like to drop procedures and then create them instead of altering procedures.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: Saving stored procedures

You need to alter your procedure:

http://msdn.microsoft.com/en-gb/library/ms189762.a...

----------------------------------------
www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

RE: Saving stored procedures

The only reson I brought up alter is vba317 indicated they were modifying. The "drop" may be what vba317 needs.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close