| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2009-09-01 : 09:20:10
|
Okay guys, I'm about ready to claw out my eyeballs!  I've written a stored procedure. Now I simply want to compile it onto the database. The only option that I see, though, is to Execute. But this would execute the commands which I don't want.How can I simply compile the procedure onto the server?Thank you. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-09-01 : 09:54:06
|
| Once the stored peocedure is created, it is saved to the database as an object. There is no such thing as compiling a stored procedure. Once it is run for the first time, a query plan will be created for it.Unless you are encrypting the stored proc, there is noting else to do once the CREATE PROCEDURE statement has been run. |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-01 : 09:55:02
|
| Script the code and execute it. LikeCreate procedure.....MadhivananFailing to plan is Planning to fail |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2009-09-01 : 10:02:24
|
| Well I need to set this procedure to run as an automated daily process. So tell me if I'm correct here. I will have to execute it manually the first time, and this will save it onto the database? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-09-01 : 10:07:48
|
quote:
Originally posted by fralo
Well I need to set this procedure to run as an automated daily process. So tell me if I'm correct here. I will have to execute it manually the first time, and this will save it onto the database?
The procedure will be saved. If you want to excute it regularly make use of jobMadhivananFailing to plan is Planning to fail |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-09-01 : 10:08:30
|
| No, not at all. If you have run the script (as in the CREATE PROCEDURE script), then it is already in the database. You can see this by going to Management Studio and looking in the Programability -> Stored Procedures tree under your database.If you haven't run this script, just run it. It will not run the code, but will parse the code to check for any errors. Once the procedure appears in the tree, then you can schedule a job by going to the SQL Agent and creating a new job to simply Exec that procedure. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-01 : 10:09:51
|
The procedure code is stored in the database when you create it. It is compiled or recompiled as needed upon execution. If the procedure is never run, it is never compiled.To make it run on a daily schedule, you create a SQL Agent job and set the schedule. See Books Online under "jobs".edit: yeah, what they said  |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2009-09-01 : 10:16:41
|
quote:
Originally posted by RickD
If you haven't run this script, just run it. It will not run the code, but will parse the code to check for any errors.
I believe this clears up my confusion. I've written the code and now want to save it onto the database. If I click 'Execute', it WILL NOT execute my code, just simply save it onto the database and check for syntax errors. This is what I meant by 'compiling' it, even if there is no such thing as you've said. I only want the code within it to be 'executed' (i.e. data manipulation) once I schedule it. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-09-01 : 10:23:31
|
| Yeah, thats what you'll get, as long as you have the following structure:CREATE PROCEDURE <procname><Variables>AS<your code here>GO |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2009-09-01 : 10:27:22
|
| Thanks so much for all of your help. |
|
|
|
|
Compiling stored proc?