473,575 Members | 3,401 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

proper Save location for SQL stored procedures

93 New Member
I saved a few stored procedures in SQL Server Management Studio. The default Save location, which I accepted, was C:\Documents and Settings\BobLew iston\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 12736
ck9663
2,878 Recognized Expert Specialist
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
BobLewiston
93 New Member
ck9663:

I know they can be stored anywhere. But where do I put them so that my apps will find them?
Apr 3 '09 #3
Uncle Dickie
67 New Member
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
BobLewiston
93 New Member
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
Uncle Dickie
67 New Member
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
  1.  
  2. USE [database name]
  3.  
  4. CREATE PROCEDURE [dbo].[myProcedure]
  5. AS
  6. BEGIN
  7.         SELECT *
  8.         FROM table
  9. END
  10.  
  11.  
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
BobLewiston
93 New Member
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

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

Similar topics

3
6576
by: Jarrod Morrison | last post by:
Hi all Im relatively new to using stored procedures and im not sure if it is possible to do what I am trying to do so any help here is greatly appreciated. I am using the variable @MachineName which is obviously the local machine name mainly in this procedure. What is loop through from the first character of the variable to the last and use...
11
10736
by: jrefactors | last post by:
I want to know the differences between SQL Server 2000 stored procedures and oracle stored procedures? Do they have different syntax? The concept should be the same that the stored procedures execute in the database server with better performance? Please advise good references for Oracle stored procedures also. thanks!!
2
2804
by: scott | last post by:
Hi, Just wondering what sort of problems and advantages people have found using stored procedures. I have an app developed in VB6 & VB.NET and our developers are starting to re-write some of the code in stored procedures (im advocating encryption of them). When deploying an application however stored procedure seem to add another level of...
2
9215
by: Kent Lewandowski | last post by:
hi all, Recently I wrote some stored procedures using java jdbc code (admittedly my first stab) and then tried to implement the same within java packages (for code reuse). I encountered problems doing this. I wanted to implemented a generic "Helper" class like this: /** * Helper
5
3465
by: Tim Marshall | last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with interest and on reading the post describing Lauren Quantrell's SmartTree, I've run into something I don't understand: Stored Procedures. I thought stored pricedures were an Oracle/MS SQL Server thing and don't know how they work with Access Jet. I've looked at some of...
2
3322
by: Eli | last post by:
Hi all We currently have a strange problem with calling a Stored Procedure (SQL Database) in our C# Project. The only error I get is "System error" which says a lot :) Background: We have several stored procedures to Insert and update datas in our SQL database. Some stored procedures are smaller (insert datas in only one table) and some...
45
3383
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
11
3402
by: peter | last post by:
I am trying to get a SQL stored procedure to use user maintained MQT implicitly which raises questions on when they are used or not used. In theory you would expect the stored procedure to pick up the MQT at the time it is bound on the creation of the static SQL. This raises the question on how you stop it or start it using a MQT as there is...
3
4343
by: evenlater | last post by:
I have an Access application on a terminal server. Sometimes my users need to export reports to pdf, rtf or xls files and save them to their own client device hard drives. They can do that right now the way I have this set up, but it's confusing and slow. When they browse for a place to save the reports, they see all of the drives on the...
0
7845
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
1
7868
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8143
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6515
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5664
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3778
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2286
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1382
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1107
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.