472,145 Members | 2,024 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,145 software developers and data experts.

proper Save location for SQL stored procedures

I saved a few stored procedures in SQL Server Management Studio. The default Save location, which I accepted, was C:\Documents and Settings\BobLewiston\My Documents\SQL Server Management Studio\Projects. (And yes, each stored procedure was in the form of an .sql file, and I assigned each file name root to be the same as the contained procedure's name.) But this Projects folder must be the wrong place, because my apps aren't finding these stored procedures.

I see a lot of .sql files in various places on my hard drive, but I can't find any by the names of the stored procedures listed in Management Studio's Stored Procedures folder (none of which I wrote). For that matter, I'm not even finding the Stored Procedures folder on my hard drive.

Where are stored procedures SUPPOSED to be saved?
Apr 2 '09 #1
6 12488
2,878 Expert 2GB
The stored proc that you created (typed in a text editor) can be saved anywhere you want. It's just an ordinary ASCII text file that you can store anywhere. So it'll be up to you to organize your files. If you need to create them, you can just open it via an editor, paste to a query window and execute.

Remember, once the stored is already created, you don't need to recreate it every time if there are no other changes.

--- CK
Apr 3 '09 #2

I know they can be stored anywhere. But where do I put them so that my apps will find them?
Apr 3 '09 #3
Once you have CREATEd a stored procedure it becomes part of the database.

You can see all the SPs related to your database in the Object Explorer (F8). Browse to your database, then Programmability, then Stored Procedures.

If the SP exists you should be able to run it again from a new query window by typing:

EXECUTE procedure(your procedure name)
Apr 3 '09 #4
Uncle Dickie:

I'm executing these stored procedures in SQL Server Management Studio's New Query window, which should save them, but they're not winding up in Management Studio's Stored Procedures folder.

Can anybody tell me what I'm doing wrong?
Apr 3 '09 #5
When you create the procedure, check which database you are connected to; when you have a query open the database it is going to run on will probably show in a drop down box in the query toolbar. If this is not the one you expect then change it there.

As a simple test you could try the following substituting the [database name] for the database you want to store the stored procedure in and 'table' with any valid table in that database:

Expand|Select|Wrap|Line Numbers
  2. USE [database name]
  4. CREATE PROCEDURE [dbo].[myProcedure]
  5. AS
  6. BEGIN
  7.         SELECT *
  8.         FROM table
  9. END
Once you have run that query it should give you a message such as 'Command(s) completed successfully.' but no actual result set.

If you then run:

Expand|Select|Wrap|Line Numbers
  1. EXECUTE procedure(dbo.myProcedure)
you should get everything from the table you selected. Failing that, I'm stumped!
Apr 3 '09 #6
Thanks everybody, problem solved. It turns out that although I had been told to save stored procedures via Ctrl-F5, it's actually F5. Again, thanks to all.
Apr 3 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Jarrod Morrison | last post: by
2 posts views Thread by Kent Lewandowski | last post: by
5 posts views Thread by Tim Marshall | last post: by
45 posts views Thread by John | last post: by
reply views Thread by Saiars | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.