Saving a stored procedure or a user defined function in SQL 2000

I can create stored procedures and user defined functions in the Query
Analyzer, but the only option I see for saving are to save to an external
sql file. Is there a way to save directly to the database while in Query
Analyzer?
Bill
They are normally saved by default. If you start your stored procedure, for
example, with CREATE PROCEDURE myProcname etc. and you execute that query
you have just created (or saved sort of) that myProcname Stored Procedure
into your database.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46575c07$0$16706$4c368faf@.roadrunner.com...
>I can create stored procedures and user defined functions in the Query
>Analyzer, but the only option I see for saving are to save to an external
>sql file. Is there a way to save directly to the database while in Query
>Analyzer?
> Bill
>
|||To clarify. Not "by default" you have to actually execute a query to save
it. This query is the CREATE syntax for creating the object. When you
execute that it is being saved. To modify it you would then ALTER it. If you
go to books online and search for CREATE PROCEDURE you will see more on
that.
HTH
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
> They are normally saved by default. If you start your stored procedure,
> for example, with CREATE PROCEDURE myProcname etc. and you execute that
> query you have just created (or saved sort of) that myProcname Stored
> Procedure into your database.
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46575c07$0$16706$4c368faf@.roadrunner.com...
>
|||Mike,
I tried this for a stored procedure and it did save it to my database. But
I can't get it to work for a user defined function. In Query Analyzer the
user defined function parses with no errors, but it won't create in the
database. Any thoughts?
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
> To clarify. Not "by default" you have to actually execute a query to save
> it. This query is the CREATE syntax for creating the object. When you
> execute that it is being saved. To modify it you would then ALTER it. If
> you go to books online and search for CREATE PROCEDURE you will see more
> on that.
> HTH
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:u8yZ5ixnHHA.3512@.TK2MSFTNGP06.phx.gbl...
>
|||Hi Bill
So when you execute the code that says CREATE FUNCTION ...
what happens? If you don't get any error, the function should be saved in
your database.
What makes you think it isn't created?
Perhaps you are looking for it in the Object Explorer and you didn't
refresh?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>
|||> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
What error message are you getting when you execute your CREATE FUNCTION
statement?
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>
|||Mike,
It appears the function did save into the database, although it's not
showing up under User Defined Functions. It is showing up when I view
permissions under Users for user dbo, and an error message is popping up
there as follows:
"Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not found
in the UserDefinedFunctions collection. If the name is a qualified name,
use [] to separate various parts of the name, and try again."
I can't change the name or delete the function because I can't see it in the
database. Is there a way to repair this?
Bill
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46576681$0$4901$4c368faf@.roadrunner.com...
> Mike,
> I tried this for a stored procedure and it did save it to my database.
> But I can't get it to work for a user defined function. In Query Analyzer
> the user defined function parses with no errors, but it won't create in
> the database. Any thoughts?
> Bill
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:uegc3kxnHHA.4516@.TK2MSFTNGP05.phx.gbl...
>
|||Have you refreshed the list of User Defined Functions as Kalen suggested? If
it is showing up as an object that you can assign permissions to it is in
your database.
When are you getting that error? When trying to assign permissions? Try and
close Enterprise Manager and reopen it.
Can you call the function and use it?
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46577035$0$4642$4c368faf@.roadrunner.com...
> Mike,
> It appears the function did save into the database, although it's not
> showing up under User Defined Functions. It is showing up when I view
> permissions under Users for user dbo, and an error message is popping up
> there as follows:
> "Error 21776 [SQL DMO] - the name 'test_function_getbalances' was not
> found in the UserDefinedFunctions collection. If the name is a qualified
> name, use [] to separate various parts of the name, and try again."
> I can't change the name or delete the function because I can't see it in
> the database. Is there a way to repair this?
> Bill
>
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46576681$0$4901$4c368faf@.roadrunner.com...
>
|||I did the refresh on User Defined Functions and the function is now in the
list. Before, I was getting the error message when clicking on Permissions
for the dbo user who owns the function. This went away after the refresh.
Thanks to all for your help.
Bill
"Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
> Have you refreshed the list of User Defined Functions as Kalen suggested?
> If it is showing up as an object that you can assign permissions to it is
> in your database.
> When are you getting that error? When trying to assign permissions? Try
> and close Enterprise Manager and reopen it.
> Can you call the function and use it?
> "Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
> news:46577035$0$4642$4c368faf@.roadrunner.com...
>
|||Glad to help have a great weekend.
"Bill Murphy" <wmdmurphy@.houston.rr.com> wrote in message
news:46578928$0$15089$4c368faf@.roadrunner.com...
>I did the refresh on User Defined Functions and the function is now in the
>list. Before, I was getting the error message when clicking on Permissions
>for the dbo user who owns the function. This went away after the refresh.
> Thanks to all for your help.
> Bill
>
> "Mike Walsh" <[mwalsh9815][at][gmail][dot][com]> wrote in message
> news:%2345tmcynHHA.1244@.TK2MSFTNGP04.phx.gbl...
>