copy stored procedure from dbase 1 to dbase2

Hi just wondering if there is an easy way to copy stored procedures from one
dbase to another on the same server, possibly using query analyzer, or vs.net
server explorer? There are around 100 procedures so hoping do not have to do
each one individually. Thanks.
--
Paul G
Software engineer.You could use Enterprise Manager to script all stored procedures to a file.
You could then execute that file within your other database.
If you have your stored procedures stored within a version control system
you could simply grab the most recent files and execute them within the
appropriate database. Source control is nice because you can look back at
the changes of the stored procedure. You can see what changed, who changed
it, and why. You can also roll back to a previous version if necessary. If
you don't store your stored procedures within source control I encourage you
to do so.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> Hi just wondering if there is an easy way to copy stored procedures from
one
> dbase to another on the same server, possibly using query analyzer, or
vs.net
> server explorer? There are around 100 procedures so hoping do not have to
do
> each one individually. Thanks.
> --
> Paul G
> Software engineer.|||Thanks for the response. I have enterprise manager but am not too familiar
with it, not even sure how to connect to the desired database. Also have
visual source safe on my machine, not sure if vss would have to be on the
server.
Paul.
"Keith Kratochvil" wrote:
> You could use Enterprise Manager to script all stored procedures to a file.
> You could then execute that file within your other database.
> If you have your stored procedures stored within a version control system
> you could simply grab the most recent files and execute them within the
> appropriate database. Source control is nice because you can look back at
> the changes of the stored procedure. You can see what changed, who changed
> it, and why. You can also roll back to a previous version if necessary. If
> you don't store your stored procedures within source control I encourage you
> to do so.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > Hi just wondering if there is an easy way to copy stored procedures from
> one
> > dbase to another on the same server, possibly using query analyzer, or
> vs.net
> > server explorer? There are around 100 procedures so hoping do not have to
> do
> > each one individually. Thanks.
> > --
> > Paul G
> > Software engineer.
>|||EM:
connect to the server
expand the databases tab
right-click a database that you want to script the stored procedures for
choose All Tasks and Generate SQL Script
Click the "show all" button
put a checkmark in the "all stored procedures" checkbox
The options tab allows you to create one file or one file per object
hit preview (or ok)
VSS is more of a client side tool. We create stored procedures using Query
Analyzer. We save each stored procedure to a text file of the same name
(with a .sql extension) and then we check these into VSS. To modify a
stored procedure we check it our of VSS, open the file within Query
Analyzer, make the change, test it, save the file, recreate the stored
procedure (Ctrl-E), and check the file back into VSS.
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> Thanks for the response. I have enterprise manager but am not too
familiar
> with it, not even sure how to connect to the desired database. Also have
> visual source safe on my machine, not sure if vss would have to be on the
> server.
> Paul.
> "Keith Kratochvil" wrote:
> > You could use Enterprise Manager to script all stored procedures to a
file.
> > You could then execute that file within your other database.
> >
> > If you have your stored procedures stored within a version control
system
> > you could simply grab the most recent files and execute them within the
> > appropriate database. Source control is nice because you can look back
at
> > the changes of the stored procedure. You can see what changed, who
changed
> > it, and why. You can also roll back to a previous version if necessary.
If
> > you don't store your stored procedures within source control I encourage
you
> > to do so.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > Hi just wondering if there is an easy way to copy stored procedures
from
> > one
> > > dbase to another on the same server, possibly using query analyzer, or
> > vs.net
> > > server explorer? There are around 100 procedures so hoping do not
have to
> > do
> > > each one individually. Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >|||thanks for the additional information, just wondering how to connect to the
database with EM? paul.
"Keith Kratochvil" wrote:
> EM:
> connect to the server
> expand the databases tab
> right-click a database that you want to script the stored procedures for
> choose All Tasks and Generate SQL Script
> Click the "show all" button
> put a checkmark in the "all stored procedures" checkbox
> The options tab allows you to create one file or one file per object
> hit preview (or ok)
> VSS is more of a client side tool. We create stored procedures using Query
> Analyzer. We save each stored procedure to a text file of the same name
> (with a .sql extension) and then we check these into VSS. To modify a
> stored procedure we check it our of VSS, open the file within Query
> Analyzer, make the change, test it, save the file, recreate the stored
> procedure (Ctrl-E), and check the file back into VSS.
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > Thanks for the response. I have enterprise manager but am not too
> familiar
> > with it, not even sure how to connect to the desired database. Also have
> > visual source safe on my machine, not sure if vss would have to be on the
> > server.
> > Paul.
> > "Keith Kratochvil" wrote:
> >
> > > You could use Enterprise Manager to script all stored procedures to a
> file.
> > > You could then execute that file within your other database.
> > >
> > > If you have your stored procedures stored within a version control
> system
> > > you could simply grab the most recent files and execute them within the
> > > appropriate database. Source control is nice because you can look back
> at
> > > the changes of the stored procedure. You can see what changed, who
> changed
> > > it, and why. You can also roll back to a previous version if necessary.
> If
> > > you don't store your stored procedures within source control I encourage
> you
> > > to do so.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > Hi just wondering if there is an easy way to copy stored procedures
> from
> > > one
> > > > dbase to another on the same server, possibly using query analyzer, or
> > > vs.net
> > > > server explorer? There are around 100 procedures so hoping do not
> have to
> > > do
> > > > each one individually. Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >
>|||When you generate the script for your stored procedures, you also have the
choice the generate the permissions for each SP (that's if you will have the
same database users on the destination database).
--
Sasan Saidi, MSc in CS
Senior DBA
Brascan Business Services
"I saw it work in a cartoon once so I am pretty sure I can do it."
"Paul" wrote:
> thanks for the additional information, just wondering how to connect to the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also have
> > > visual source safe on my machine, not sure if vss would have to be on the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within the
> > > > appropriate database. Source control is nice because you can look back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if necessary.
> > If
> > > > you don't store your stored procedures within source control I encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||ok thanks for the information. Actually my application does authentication,
password and username check and the connection uses a dedicated username and
password for the application so all stored procedures are accessable for this
dedicated username and password.
"Sasan Saidi" wrote:
> When you generate the script for your stored procedures, you also have the
> choice the generate the permissions for each SP (that's if you will have the
> same database users on the destination database).
> --
> Sasan Saidi, MSc in CS
> Senior DBA
> Brascan Business Services
> "I saw it work in a cartoon once so I am pretty sure I can do it."
>
> "Paul" wrote:
> > thanks for the additional information, just wondering how to connect to the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also have
> > > > visual source safe on my machine, not sure if vss would have to be on the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within the
> > > > > appropriate database. Source control is nice because you can look back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if necessary.
> > > If
> > > > > you don't store your stored procedures within source control I encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >|||In order to connect to a [database] server you have to register that server
within the Enterprise Manager GUI. I am guessing that you already did this
step and you want to know how to "connect" to a specific database.
Open Enterprise Manager
Connect to a specific server.
Expand the databases folder
Now you will see each of the databases
Right-click on one and choose All Tasks and Generate SQL Script
--
Keith
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> thanks for the additional information, just wondering how to connect to
the
> database with EM? paul.
> "Keith Kratochvil" wrote:
> > EM:
> > connect to the server
> > expand the databases tab
> > right-click a database that you want to script the stored procedures for
> > choose All Tasks and Generate SQL Script
> > Click the "show all" button
> > put a checkmark in the "all stored procedures" checkbox
> > The options tab allows you to create one file or one file per object
> > hit preview (or ok)
> >
> > VSS is more of a client side tool. We create stored procedures using
Query
> > Analyzer. We save each stored procedure to a text file of the same name
> > (with a .sql extension) and then we check these into VSS. To modify a
> > stored procedure we check it our of VSS, open the file within Query
> > Analyzer, make the change, test it, save the file, recreate the stored
> > procedure (Ctrl-E), and check the file back into VSS.
> >
> > --
> > Keith
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > Thanks for the response. I have enterprise manager but am not too
> > familiar
> > > with it, not even sure how to connect to the desired database. Also
have
> > > visual source safe on my machine, not sure if vss would have to be on
the
> > > server.
> > > Paul.
> > > "Keith Kratochvil" wrote:
> > >
> > > > You could use Enterprise Manager to script all stored procedures to
a
> > file.
> > > > You could then execute that file within your other database.
> > > >
> > > > If you have your stored procedures stored within a version control
> > system
> > > > you could simply grab the most recent files and execute them within
the
> > > > appropriate database. Source control is nice because you can look
back
> > at
> > > > the changes of the stored procedure. You can see what changed, who
> > changed
> > > > it, and why. You can also roll back to a previous version if
necessary.
> > If
> > > > you don't store your stored procedures within source control I
encourage
> > you
> > > > to do so.
> > > >
> > > > --
> > > > Keith
> > > >
> > > >
> > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > Hi just wondering if there is an easy way to copy stored
procedures
> > from
> > > > one
> > > > > dbase to another on the same server, possibly using query
analyzer, or
> > > > vs.net
> > > > > server explorer? There are around 100 procedures so hoping do not
> > have to
> > > > do
> > > > > each one individually. Thanks.
> > > > > --
> > > > > Paul G
> > > > > Software engineer.
> > > >
> > > >
> >
> >|||actually have not registered it yet, just getting started. Thanks for the
information.
"Keith Kratochvil" wrote:
> In order to connect to a [database] server you have to register that server
> within the Enterprise Manager GUI. I am guessing that you already did this
> step and you want to know how to "connect" to a specific database.
> Open Enterprise Manager
> Connect to a specific server.
> Expand the databases folder
> Now you will see each of the databases
> Right-click on one and choose All Tasks and Generate SQL Script
> --
> Keith
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:7DF6A163-273B-4314-AFCB-AE46382D3CA3@.microsoft.com...
> > thanks for the additional information, just wondering how to connect to
> the
> > database with EM? paul.
> >
> > "Keith Kratochvil" wrote:
> >
> > > EM:
> > > connect to the server
> > > expand the databases tab
> > > right-click a database that you want to script the stored procedures for
> > > choose All Tasks and Generate SQL Script
> > > Click the "show all" button
> > > put a checkmark in the "all stored procedures" checkbox
> > > The options tab allows you to create one file or one file per object
> > > hit preview (or ok)
> > >
> > > VSS is more of a client side tool. We create stored procedures using
> Query
> > > Analyzer. We save each stored procedure to a text file of the same name
> > > (with a .sql extension) and then we check these into VSS. To modify a
> > > stored procedure we check it our of VSS, open the file within Query
> > > Analyzer, make the change, test it, save the file, recreate the stored
> > > procedure (Ctrl-E), and check the file back into VSS.
> > >
> > > --
> > > Keith
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:2EEEA26E-913E-4313-8EDB-C1842CD918F3@.microsoft.com...
> > > > Thanks for the response. I have enterprise manager but am not too
> > > familiar
> > > > with it, not even sure how to connect to the desired database. Also
> have
> > > > visual source safe on my machine, not sure if vss would have to be on
> the
> > > > server.
> > > > Paul.
> > > > "Keith Kratochvil" wrote:
> > > >
> > > > > You could use Enterprise Manager to script all stored procedures to
> a
> > > file.
> > > > > You could then execute that file within your other database.
> > > > >
> > > > > If you have your stored procedures stored within a version control
> > > system
> > > > > you could simply grab the most recent files and execute them within
> the
> > > > > appropriate database. Source control is nice because you can look
> back
> > > at
> > > > > the changes of the stored procedure. You can see what changed, who
> > > changed
> > > > > it, and why. You can also roll back to a previous version if
> necessary.
> > > If
> > > > > you don't store your stored procedures within source control I
> encourage
> > > you
> > > > > to do so.
> > > > >
> > > > > --
> > > > > Keith
> > > > >
> > > > >
> > > > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > > > news:656816D1-61F2-406C-8B0E-32466BD8F812@.microsoft.com...
> > > > > > Hi just wondering if there is an easy way to copy stored
> procedures
> > > from
> > > > > one
> > > > > > dbase to another on the same server, possibly using query
> analyzer, or
> > > > > vs.net
> > > > > > server explorer? There are around 100 procedures so hoping do not
> > > have to
> > > > > do
> > > > > > each one individually. Thanks.
> > > > > > --
> > > > > > Paul G
> > > > > > Software engineer.
> > > > >
> > > > >
> > >
> > >
>