Saving Procedures and Views to remote server

I am using SQL Server Management Studio and Visual Studio 2005 and I am a newbie.

Once connected to a remote server and database I am able to use SQL Server Management Studio to modify a table and save it. When I modify a stored procedure or create a new one and attempt to save it I get my local dialog box to save to my system with a new name “SQLQuery6.sql.” How do I cause the procedure to be saved to the remote database? If anyone can help this or where I can find it I will appreciate it. Thanks in advance.

When you 'SAVE' the contents of a Query window, you are making a file copy of the contents -so you are asked where to save that file.

If you want to 'install' your stored procedure or VIEW on a SQL Server, be sure the query window is connected to the Server you want, and then just EXECUTE the [CREATE PROCEDURE...] code.

|||

Thank you

Does this also apply to modifying a stored procedure?

|||

Yes, it applies to modifying a stored procedure as well. Just execute your "alter procedure ..." statement on the SQL Server you want to update.

Jarret

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...
>

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?
BillThey 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@.roadrunne
r.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 i
n 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@.roadrunne
r.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 i
n 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/pr...oads/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 fo
und
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 i
n 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@.roadrunne
r.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...
>

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?
BillThey 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...
>>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
>|||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...
>> 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
>>
>|||> 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...
>> 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...
>>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
>>
>>
>|||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...
>> 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...
>>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
>>
>>
>|||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...
>> 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...
>>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
>>
>>
>|||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...
>> 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...
>> 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
>>
>>
>>
>|||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...
>> 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...
>> 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...
>>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
>>
>>
>>
>>
>|||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...
>> 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...
>> 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...
>> 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
>>>
>>
>>
>>
>>
>>
>sql

Save Table Definitions to Text File?

I don't know much about SQL Server yet and am going to start writing some
stored procedures. I thought the best way to reference the table names and
columns would be writing their definitions out to a text file like a report.
Any comments in this regard?
<%= Clinton GallagherIt looks like running exec sp_help <table-name> will work out but a nicely
formatted report of a database and its schema would be helpful to learn how
to do.
<%= Clinton Gallagher
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:Oud%23G%23kYFHA.3572@.TK2MSFTNGP12.phx.gbl...
>I don't know much about SQL Server yet and am going to start writing some
>stored procedures. I thought the best way to reference the table names and
>columns would be writing their definitions out to a text file like a
>report.
> Any comments in this regard?
> <%= Clinton Gallagher
>|||If you want to build something on your own I suggest using the data from
SP_help and inserting that into a temporary table to use it further:
CREATE Table #TableDefs
(
Here goe the columns you need for sp_help
)
INSERT INTO #TableDefs
EXEC SP_HELP YourTableName
--<Further code to get though the Data to format it in some way>
Do you have Visio ? There are some nice build in feature for reengineering
your database. There are other programs out there which make reports of your
database.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> schrieb im
Newsbeitrag news:ODc18SlYFHA.2796@.TK2MSFTNGP09.phx.gbl...
> It looks like running exec sp_help <table-name> will work out but a nicely
> formatted report of a database and its schema would be helpful to learn
> how to do.
>
> <%= Clinton Gallagher
>
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:Oud%23G%23kYFHA.3572@.TK2MSFTNGP12.phx.gbl...
>|||Not bad ideas. Thanks for your comments Jens.
It takes time but using sp_help allowed me to copy and paste to a text file
and use tabs and so on to layout and organize a reference document that I
find useful to refer to while coding. The basics such as table name, column
name, type, length, and nullability is all I find I need.
I forgot all about Visio which I do have but as I recall Visio will generate
all or nothing results. We'll see... So many tools, so little time :-)
<%= Clinton Gallagher
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ezHvX4nYFHA.3040@.TK2MSFTNGP14.phx.gbl...
> If you want to build something on your own I suggest using the data from
> SP_help and inserting that into a temporary table to use it further:
> CREATE Table #TableDefs
> (
> Here goe the columns you need for sp_help
> )
> INSERT INTO #TableDefs
> EXEC SP_HELP YourTableName
> --<Further code to get though the Data to format it in some way>
> Do you have Visio ? There are some nice build in feature for reengineering
> your database. There are other programs out there which make reports of
> your database.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> schrieb im
> Newsbeitrag news:ODc18SlYFHA.2796@.TK2MSFTNGP09.phx.gbl...
>|||Hi
If you want to script the tables fully the easiest way it to use the
scripting options in Enterprise Manager or Query Analyser, but it sounds
like everything you may need could be already in the
INFORMATION_SCHEMA.COLUMNS view.
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'MyTable'
ORDER BY ORDINAL_POSITION
John
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:Co%le.976$k43.342@.tornado.rdc-kc.rr.com...
> Not bad ideas. Thanks for your comments Jens.
> It takes time but using sp_help allowed me to copy and paste to a text
> file and use tabs and so on to layout and organize a reference document
> that I find useful to refer to while coding. The basics such as table
> name, column name, type, length, and nullability is all I find I need.
> I forgot all about Visio which I do have but as I recall Visio will
> generate all or nothing results. We'll see... So many tools, so little
> time :-)
> <%= Clinton Gallagher
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:ezHvX4nYFHA.3040@.TK2MSFTNGP14.phx.gbl...
>