1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/for...st10017013.aspx
http://www.eggheadcafe.com/articles...e_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>
Showing posts with label enterprise. Show all posts
Showing posts with label enterprise. Show all posts
Friday, March 30, 2012
Scripting a SQL database
1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?
Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>
|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.
|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland
|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland
|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?
Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>
|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.
|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland
|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland
|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/foru...t10017013.aspx
http://www.eggheadcafe.com/articles/..._generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>
Scripting a SQL database
1. With Enterprise Manager what is required to have the script create the
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>
database, the SQL user and
set their password?
2. Now that I have created the scripts, how do I execute them on a different
SQL server and can it be done from ASP?Yes and yes.
You just use the connection.execute mysqlscriptstring
You've just got to connect to the database with an account
that has enough priviledge to run your script.
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nonononono.us> wrote in message
news:%23J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl...
> 1. With Enterprise Manager what is required to have the script create the
> database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different
> SQL server and can it be done from ASP?
>|||"Roland Hall" <nobody@.nonononono.us> wrote in
news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
> 1. With Enterprise Manager what is required to have the script create
> the database, the SQL user and
> set their password?
> 2. Now that I have created the scripts, how do I execute them on a
> different SQL server and can it be done from ASP?
>
Please clarify what you are trying to achieve.|||"JTC ^..^" wrote in message news:Xns9602F24559D9daveJTC@.217.32.252.50...
: "Roland Hall" <nobody@.nonononono.us> wrote in
: news:#J5RkctFFHA.3368@.TK2MSFTNGP10.phx.gbl:
:
: > 1. With Enterprise Manager what is required to have the script create
: > the database, the SQL user and
: > set their password?
: > 2. Now that I have created the scripts, how do I execute them on a
: > different SQL server and can it be done from ASP?
: >
: >
:
: Please clarify what you are trying to achieve.
Thanks for responding...
I have an application that uses SQL Server. Everything is portable except
the SQL part. I would like to create the database and the structure
programmatically without requiring the Admin to create it manually. My
preferred method would be to do it from ASP if possible. The other response
from Robbie, says it can be done.
I know SQL well enough to work with it in a web environment but not well
versed with SPs. Enterprise Manager appears to be able to create the
scripts for me, which will help me learn but I don't know how to utilize
them after they're created. I'm still trying to figure that out. A little
help would go a long way for me.
Roland|||"Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
: Yes and yes.
Thanks for responding...
I only asked one closed-end question. Can I get some pointers on getting
this done? You might want to review my response to the other responder.
Examples would be great but research material and knowing what to look for
would be fine too.
:
: You just use the connection.execute mysqlscriptstring
:
: You've just got to connect to the database with an account
: that has enough priviledge to run your script.
How are the scripts that Enterprise Manager can create from an existing
database used to recreate this structure on a different server?
Roland|||Roland Hall (nobody@.nowhere) writes:
> I have an application that uses SQL Server. Everything is portable
> except the SQL part. I would like to create the database and the
> structure programmatically without requiring the Admin to create it
> manually. My preferred method would be to do it from ASP if possible.
> The other response from Robbie, says it can be done.
> I know SQL well enough to work with it in a web environment but not well
> versed with SPs. Enterprise Manager appears to be able to create the
> scripts for me, which will help me learn but I don't know how to utilize
> them after they're created. I'm still trying to figure that out. A
> little help would go a long way for me.
To do this without Enterprise Mangager you would have to use DMO which is
a OLE interface. No, I am not going give examples, because I don't use
DMO myself. But is documented in Books Online.
Once you have the scripts, running them from ASP is no different from
running any other SQL statements. Just recall that GO is not an SQL
statement - you will have to split the code into batches yourself.
Personally, I would build this on a solution where I had the scripts to
build the database under version control, and work from these. In fact
this is how we distribute our databases, although our build tool is in
Perl and not in ASP.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Virtually everything SQL Server Enterprise Manager does can be
do via script because that is in large part what EM uses "at some
point" in the process (either directly or indirectly via DMO).
To run the generated script in ASP, you literally just
read the script file in as a string and pass it to the ADO
connection object's .Execute method. It is that simple.
You can also create a DTS (Data Transformation Package)
utilizing the Import / Export Wizard. Then, use ASP to run
the package. This, in my opinion, may be a better option than
scripts from a support and simplicity standpoint. My guess is
that you'll want to select the "copy database objects and data"
when you get to the part in the wizard where it asks you what
you want to do. At the end, you'll be given an option as
to how you want to store the package. Select the option to
store it in SQL Server. What is nice about this option is that
if you make a change to your database, you just rerun the
wizard (after deleting the current package) and you are all set.
No need to move files around or deploy them.
Using the wizard is pretty straight forward. Calling the stored
package in SQL Server isn't. Here is a VBScript (which
is really the underlying language in ASP) to run a DTS
package. It also includes other code for ftp'ing data that
you probably don't need.
http://www.eggheadcafe.com/articles/20030923.asp
I'm upgrading our servers today. So, if the link doesn't come
up, check again in a few hours.
These other SQL Server Flash Video presentations for database
management may also come in handy:
http://www.eggheadcafe.com/videodemo/
--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
"Roland Hall" <nobody@.nowhere> wrote in message
news:e6iVeUzFFHA.2180@.TK2MSFTNGP10.phx.gbl...
> "Robbe Morris [C# MVP]" <info@.turnkeytools.com> wrote in message
> news:%23tAuoytFFHA.3840@.tk2msftngp13.phx.gbl...
> : Yes and yes.
> Thanks for responding...
> I only asked one closed-end question. Can I get some pointers on getting
> this done? You might want to review my response to the other responder.
> Examples would be great but research material and knowing what to look for
> would be fine too.
> :
> : You just use the connection.execute mysqlscriptstring
> :
> : You've just got to connect to the database with an account
> : that has enough priviledge to run your script.
> How are the scripts that Enterprise Manager can create from an existing
> database used to recreate this structure on a different server?
> Roland
>
Wednesday, March 28, 2012
Script, Save, Export SQL Database Diagrams
When you create database diagrams in Enterprise Manager, the details
for constructing those diagrams is saved into the dtproperties table.
This table includes an image field which contains most of the relevant
infomation, in a binary format.
for constructing those diagrams is saved into the dtproperties table.
This table includes an image field which contains most of the relevant
infomation, in a binary format.
SQL Enterprise manager offers no way to script out those diagrams, so
I have created two Transact SQL components, one User Function and one
User Procedure, which together provide a means to script out the
contents of the dtproperties table, including all of the binary based
image data, into a self documenting, easy to read script. This script
can be stowed away safely, perhaps within your versioning software,
and it can subsequently be recalled and executed to reconstruct all
the original diagrams.
The script is intelligent enough not to overwrite existing diagrams,
although it does allow the user to purge any existing diagrams, if
they so choose.
Once these two objects have been added to any database, you may then
backup (script out) the current database diagrams by executing the
stored procedure, like this:
Exec usp_ScriptDatabaseDiagrams
By default, all database diagrams will be scripted, however, if you
want to script the diagrams individually, you can execute the same
procedure, passing in the name of a specific diagram. For example:
Exec usp_ScriptDatabaseDiagrams 'Users Alerts'
The Transact SQL code for the two objects is too long to paste here,
but if you are interested, I will email it to you. Just drop me a note
at: clayTAKE_THIS_OUT@.beattyhomeTAKE_THIS_OUT.com (Remove both
instances of TAKE_THIS_OUT from my email address first!!)
-ClayOk, I've had a few emails on this, so I'll post the code here.
This is the code for the first component, a user defined function to
translate a Varbinary value into a Varchar string of hex values. The
hex string will obviously contain twice as many bytes as the binary
string.
The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.
-Clay
if exists (select 1
from sysobjects
where name = 'ufn_VarbinaryToVarcharHex'
and type = 'FN')
drop function ufn_VarbinaryToVarcharHex
GO
CREATE FUNCTION dbo.ufn_VarbinaryToVarcharHex (@.VarbinaryValue
varbinary(4000))
RETURNS Varchar(8000) AS
BEGIN
Declare @.NumberOfBytes Int
Declare @.LeftByte Int
Declare @.RightByte Int
SET @.NumberOfBytes = datalength(@.VarbinaryValue)
IF (@.NumberOfBytes > 4)
RETURN Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@.VarbinaryValue,
1,
(@.NumberOfBytes/2)) as varbinary(2000)))
+ Payment.dbo.ufn_VarbinaryToVarcharHex(cast(substri ng(@.VarbinaryValue,
((@.NumberOfBytes/2)+1),
2000) as varbinary(2000)))
IF (@.NumberOfBytes = 0)
RETURN ''
-- Either 4 or less characters (8 hex digits) were input
SET @.LeftByte = CAST(@.VarbinaryValue as Int) & 15
SET @.LeftByte = CASE WHEN (@.LeftByte < 10)
THEN (48 + @.LeftByte)
ELSE (87 + @.LeftByte)
END
SET @.RightByte = (CAST(@.VarbinaryValue as Int) / 16) & 15
SET @.RightByte = CASE WHEN (@.RightByte < 10)
THEN (48 + @.RightByte)
ELSE (87 + @.RightByte)
END
SET @.VarbinaryValue = SUBSTRING(@.VarbinaryValue, 1,
(@.NumberOfBytes-1))
RETURN CASE WHEN (@.LeftByte < 10)
THEN
Payment.dbo.ufn_VarbinaryToVarcharHex(@.VarbinaryVa lue) +
char(@.RightByte) + char(@.LeftByte)
ELSE
Payment.dbo.ufn_VarbinaryToVarcharHex(@.VarbinaryVa lue) +
char(@.RightByte) + char(@.LeftByte)
END
END
go
GRANT EXECUTE ON [dbo].[ufn_VarbinaryToVarcharHex] TO [PUBLIC]
GO|||Ok, I've had a few emails on this, so I'll post the code here.
This is the code for the second component, a user stored procedure to
script out your diagrams, in the form of a new SQL script which will
populate dtproperties appropriately.
The formatting of the code pasted here got a little messed up with the
line wraps, but you should be able to clean that up easily enough in
SQL Query Analyzer.
-Clay
if exists (select 1
from sysobjects
where name = 'usp_ScriptDatabaseDiagrams'
and type = 'P')
drop procedure usp_ScriptDatabaseDiagrams
GO
CREATE PROCEDURE dbo.usp_ScriptDatabaseDiagrams @.DiagramName varchar
(128) = null
AS
-- Variable Declarations
--------
Declare @.idint
Declare @.objectidint
Declare @.propertyvarchar(64)
Declare @.valuevarchar (255)
Declare @.uvaluevarchar (255)
Declare @.lvaluePresentbit
Declare @.versionint
Declare @.PointerToDatavarbinary (16)
Declare @.ImageRowByteCountint
Declare @.CharDatavarchar (8000)
Declare @.DiagramDataFetchStatusint
Declare @.CharDataFetchStatusint
Declare @.Offsetint
Declare @.LastObjectidint
Declare @.NextObjectidint
Declare @.ReturnCodeint
-- Initializations
------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @.ReturnCode = -1
SET @.ImageRowByteCount = 40
SET @.LastObjectid = -1
SET @.NextObjectid = -1
-- Temp Table Creation for transforming Image Data into a text (hex)
format
-----------------------
CREATE TABLE #ImageData(KeyValue int NOT NULL IDENTITY (1, 1),
DataFieldvarbinary(8000) NULL) ON [PRIMARY]
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO CREATE TABLE
#ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
ALTER TABLE #ImageData ADD CONSTRAINT
PK_ImageData PRIMARY KEY CLUSTERED
(KeyValue) ON [PRIMARY]
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO Index TABLE
#ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Output Script Header Documentation
------------
PRINT '----------------------'
PRINT '-- Database Diagram Reconstruction Script'
PRINT '----------------------'
PRINT '-- Created on: ' + Convert(varchar(23), GetDate(), 121)
PRINT '-- From Database: ' + DB_NAME()
PRINT '-- By User: ' + USER_NAME()
PRINT '--'
PRINT '-- This SQL Script was designed to reconstruct a set of
database'
PRINT '-- diagrams, by repopulating the system table dtproperties, in
the'
PRINT '-- current database, with values which existed at the time
this'
PRINT '-- script was created. Typically, this script would be created
to'
PRINT '-- backup a set of database diagrams, or to package up those
diagrams'
PRINT '-- for deployment to another database.'
PRINT '--'
PRINT '-- Minimally, all that needs to be done to recreate the target'
PRINT '-- diagrams is to run this script. There are several options,'
PRINT '-- however, which may be modified, to customize the diagrams to
be'
PRINT '-- produced. Changing these options is as simple as modifying
the'
PRINT '-- initial values for a set of variables, which are defined
immediately'
PRINT '-- following these comments. They are:'
PRINT '--'
PRINT '-- Variable Name Description'
PRINT '-- --------
--------------'
PRINT '-- @.TargetDatabase This varchar variable will establish
the'
PRINT '-- target database, within which the
diagrams'
PRINT '-- will be reconstructed. This variable
is'
PRINT '-- initially set to database name from
which the'
PRINT '-- script was built, but it may be
modified as'
PRINT '-- required. A valid database name
must be'
PRINT '-- specified.'
PRINT '--'
PRINT '-- @.DropExistingDiagrams This bit variable is initially set
set to a'
PRINT '-- value of zero (0), which indicates
that any'
PRINT '-- existing diagrams in the target
database are'
PRINT '-- to be preserved. By setting this
value to'
PRINT '-- one (1), any existing diagrams in
the target'
PRINT '-- database will be dropped prior to'
PRINT '-- reconstruction. Zero and One are the
only'
PRINT '-- valid values for the variable.'
PRINT '--'
PRINT '-- @.DiagramSuffix This varchar variable will be used
to append'
PRINT '-- to the original diagram names, as
they'
PRINT '-- existed at the time they were
scripted. This'
PRINT '-- variable is initially set to take on
the'
PRINT '-- value of the current date/time,
although it'
PRINT '-- may be modified as required. An
empty string'
PRINT '-- value would effectively turn off the
diagram'
PRINT '-- suffix option.'
PRINT '--'
PRINT '----------------------'
PRINT ''
PRINT 'SET NOCOUNT ON'
PRINT ''
PRINT '-- User Settable Options'
PRINT '--------'
PRINT 'Declare @.TargetDatabase varchar (128)'
PRINT 'Declare @.DropExistingDiagrams bit'
PRINT 'Declare @.DiagramSuffix varchar (50)'
PRINT ''
PRINT '-- Initialize User Settable Options'
PRINT '-----------'
PRINT 'SET @.TargetDatabase = ''Payment'''
PRINT 'SET @.DropExistingDiagrams = 0'
PRINT 'SET @.DiagramSuffix = '' '' + Convert(varchar(23), GetDate(),
121)'
PRINT ''
PRINT ''
PRINT '----------------------'
PRINT '-- END OF USER MODIFIABLE SECTION - MAKE NO CHANGES TO THE
LOGIC BELOW --'
PRINT '----------------------'
PRINT ''
PRINT ''
PRINT '-- Setting Target database and clearing dtproperties, if
indicated'
PRINT '--------------------'
PRINT 'Exec(''USE '' + @.TargetDatabase)'
PRINT 'IF (@.DropExistingDiagrams = 1)'
PRINT ' TRUNCATE TABLE dtproperties'
PRINT ''
PRINT ''
PRINT '-- Creating Temp Table to persist specific variables '
PRINT '-- between Transact SQL batches (between GO statements)'
PRINT '-----------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobjects'
PRINT ' WHERE name like ''%#PersistedVariables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVariables'
PRINT 'CREATE TABLE #PersistedVariables (VariableName varchar (50)
NOT NULL,'
PRINT ' VariableValue varchar (50)
NOT NULL) ON [PRIMARY]'
PRINT 'ALTER TABLE #PersistedVariables ADD CONSTRAINT'
PRINT ' PK_PersistedVariables PRIMARY KEY CLUSTERED '
PRINT ' (VariableName) ON [PRIMARY]'
PRINT ''
PRINT ''
PRINT '-- Persist @.DiagramSuffix'
PRINT '--------'
PRINT 'INSERT INTO #PersistedVariables VALUES (''DiagramSuffix'','
PRINT ' @.DiagramSuffix)'
PRINT 'GO'
PRINT ''
-- Cusror to be used to enumerate through each row of
-- diagram data from the table dtproperties
----------------
Declare DiagramDataCursor Cursor
FOR SELECT dtproperties.id,
dtproperties.objectid,
dtproperties.property,
dtproperties.value,
dtproperties.uvalue,
CASE WHEN (dtproperties.lvalue is Null) THEN 0
ELSE 1
END,
dtproperties.version
FROM dtproperties INNER JOIN (SELECT objectid
FROM dtproperties
WHERE property = 'DtgSchemaNAME'
AND value =
IsNull(@.DiagramName, value)) TargetObject
ON dtproperties.objectid =
TargetObject.objectid
ORDER BY dtproperties.id,
dtproperties.objectid
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Cusror to be used to enumerate through each row of
-- varchar data from the temp table #ImageData
----------------
Declare CharDataCursor Cursor
FOR SELECT '0x'+Payment.dbo.ufn_VarbinaryToVarcharHex(DataFie ld)
FROM #ImageData
ORDER BY KeyValue
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO DECLARE CURSOR
CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Open the DiagramDataCursor cursor
-----------
OPEN DiagramDataCursor
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO OPEN CURSOR
DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Get the Row of Diagram data
----------
FETCH NEXT FROM DiagramDataCursor
INTO @.id,
@.objectid,
@.property,
@.value,
@.uvalue,
@.lvaluePresent,
@.version
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO FETCH NEXT FROM
CURSOR DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Initialize the Fetch Status for the DiagramDataCursor cursor
-------------------
SET @.DiagramDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
----------
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@.DiagramDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Begin the processing each Row of Diagram data
---------------
WHILE (@.DiagramDataFetchStatus = 0)
BEGIN
-- Build an Insert statement for non-image data
PRINT ''
PRINT '-- Insert a new dtproperties row'
PRINT '----------'
IF (@.LastObjectid <> @.objectid)
BEGIN
-- Retrieve the persisted DiagramSuffix - If
processing DtgSchemaNAME
IF (@.property = 'DtgSchemaNAME')
BEGIN
PRINT 'Declare @.DiagramSuffix varchar (50)'
PRINT 'SELECT @.DiagramSuffix = Convert(varchar
(50), VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''DiagramSuffix'''
END
-- Build the Insert statement for a New Diagram -
Apply and Persist the new Objectid
PRINT 'INSERT INTO dtproperties (objectid,'
PRINT ' property,'
PRINT ' value,'
PRINT ' uvalue,'
PRINT ' lvalue,'
PRINT ' version)'
PRINT ' VALUES (0,'
PRINT ' ''' + @.property +
''','
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.value + ''' + @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.value + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.uvalue + '''+ @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.uvalue + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.lvaluePresent = 1)
THEN
'cast(''0'' as varbinary(10)),'
ELSE
'null,'
END
PRINT ' ' +
IsNull(Convert(varchar(15), @.version), 'null') + ')'
PRINT 'DELETE #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'INSERT INTO #PersistedVariables VALUES
(''NextObjectid'','
PRINT '
Convert(varchar(15), @.@.IDENTITY))'
PRINT 'Declare @.NextObjectid int'
PRINT 'SELECT @.NextObjectid = Convert(int,
VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'UPDATE dtproperties'
PRINT ' SET Objectid = @.NextObjectid'
PRINT 'WHERE id = @.NextObjectid'
SET @.LastObjectid = @.objectid
END
ELSE
BEGIN
-- Retrieve the persisted DiagramSuffix - If
processing DtgSchemaNAME
IF (@.property = 'DtgSchemaNAME')
BEGIN
PRINT 'Declare @.DiagramSuffix varchar (50)'
PRINT 'SELECT @.DiagramSuffix = Convert(varchar
(50), VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''DiagramSuffix'''
END
-- Build the Insert statement for an in process
Diagram - Retrieve the persisted Objectid
PRINT 'Declare @.NextObjectid int'
PRINT 'SELECT @.NextObjectid = Convert(int,
VariableValue)'
PRINT 'FROM #PersistedVariables'
PRINT 'WHERE VariableName = ''NextObjectid'''
PRINT 'INSERT INTO dtproperties (objectid,'
PRINT ' property,'
PRINT ' value,'
PRINT ' uvalue,'
PRINT ' lvalue,'
PRINT ' version)'
PRINT ' VALUES (@.NextObjectid,'
PRINT ' ''' + @.property +
''','
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.value + ''' + @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.value + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.property = 'DtgSchemaNAME')
THEN
IsNull(('''' + @.uvalue + '''+ @.DiagramSuffix,'), 'null,')
ELSE
IsNull(('''' + @.uvalue + ''','), 'null,')
END
PRINT ' ' + CASE WHEN
(@.lvaluePresent = 1)
THEN
'cast(''0'' as varbinary(10)),'
ELSE
'null,'
END
PRINT ' ' +
IsNull(Convert(varchar(15), @.version), 'null') + ')'
END
-- Each Insert deliniates a new Transact SQL batch
PRINT 'GO'
-- Check for a non-null lvalue (image data is present)
IF (@.lvaluePresent = 1)
BEGIN
-- Fill the temp table with Image Data of length @.ImageRowByteCount
INSERT INTO #ImageData (DataField)
EXEC usp_dtpropertiesTextToRowset @.id,
@.ImageRowByteCount
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
INSERT INTO #ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Prepare to build the UPDATETEXT statement(s) for
the image data
SET @.Offset = 0
-- Open the CharDataCursor cursor
OPEN CharDataCursor
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
OPEN CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Get the CharData Row
FETCH NEXT FROM CharDataCursor
INTO @.CharData
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
FETCH NEXT FROM CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Initialize the Fetch Status for the CharDataCursor
cursor
SET @.CharDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
SET @.CharDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Begin the processing of each Row of Char data
WHILE (@.CharDataFetchStatus = 0)
BEGIN
-- Update a segment of image data
PRINT ''
PRINT '-- Update this dtproperties row with a
new segment of Image data'
PRINT 'Declare @.PointerToData varbinary (16)'
PRINT 'SELECT @.PointerToData = TEXTPTR(lvalue)
FROM dtproperties WHERE id = (SELECT MAX(id) FROM dtproperties)'
PRINT 'UPDATETEXT dtproperties.lvalue
@.PointerToData ' + convert(varchar(15), @.Offset) + ' null ' +
@.CharData
-- Each UPDATETEXT deliniates a new Transact
SQL batch
PRINT 'GO'
-- Calculate the Offset for the next segment
of image data
SET @.Offset = @.Offset + ((LEN(@.CharData) - 2)
/ 2)
-- Get the CharData Row
FETCH NEXT FROM CharDataCursor
INTO @.CharData
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO FETCH NEXT FROM CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the
CharDataCursor cursor
SET @.CharDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE
ATTEMPTING TO SET @.CharDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
-- Cleanup CharDataCursor Cursor resources
Close CharDataCursor
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
CLOSE CURSOR CharDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Flush the processed Image data
TRUNCATE TABLE #ImageData
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO
TRUNCATE TABLE #ImageData'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
-- Get the Row of Diagram data
FETCH NEXT FROM DiagramDataCursor
INTO @.id,
@.objectid,
@.property,
@.value,
@.uvalue,
@.lvaluePresent,
@.version
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO FETCH
NEXT FROM CURSOR DiagramDataCursor'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
-- Update the Fetch Status for the DiagramDataCursor cursor
SET @.DiagramDataFetchStatus = @.@.FETCH_STATUS
-- Check for an unexpected error
IF (@.@.error != 0)
BEGIN
PRINT ''
PRINT '***'
PRINT '*** ERROR OCCURRED WHILE ATTEMPTING TO SET
@.DiagramDataFetchStatus'
PRINT '***'
PRINT ''
GOTO Procedure_Exit
END
END
PRINT ''
PRINT '-- Cleanup the temp table #PersistedVariables'
PRINT '--------------'
PRINT 'IF EXISTS(SELECT 1'
PRINT ' FROM tempdb..sysobjects'
PRINT ' WHERE name like ''%#PersistedVariables%'''
PRINT ' AND xtype = ''U'')'
PRINT ' DROP TABLE #PersistedVariables'
PRINT 'GO'
PRINT ''
PRINT 'SET NOCOUNT OFF'
PRINT 'GO'
-- Processing Complete
-------
SET @.ReturnCode = 0
Procedure_Exit:
-----
Close DiagramDataCursor
DEALLOCATE DiagramDataCursor
DEALLOCATE CharDataCursor
DROP TABLE #ImageData
SET NOCOUNT OFF
RETURN @.ReturnCode
GO
GRANT EXECUTE ON [dbo].[usp_ScriptDatabaseDiagrams] TO [Public]
GO|||Yikes!!! SOmeone just correctly pointed out to me that there are
actually three components which I should have posted... I neglected
to post the stored procedure: usp_dtpropertiesTextToRowset Which is
required for the process to work.
Here it is, the third component... This should be built after the
function, but before the other procedure, since the other procedure
references this one.
-Clay
if exists (select 1
from sysobjects
where name = 'usp_dtpropertiesTextToRowset'
and type = 'P')
drop procedure usp_dtpropertiesTextToRowset
GO
CREATE PROCEDURE dbo.usp_dtpropertiesTextToRowset @.idint,
@.RowsetCharLenint =
255
AS
-- Variable Declarations
--------
Declare @.PointerToDatavarbinary (16)
Declare @.TotalSizeint
Declare @.LastReadint
Declare @.ReadSizeint
Declare @.ReturnCodeint
-- Initializations
------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @.ReturnCode = -1
-- Establish the Pointer to the Image data
-------------
SELECT @.PointerToData = TEXTPTR(lvalue),
@.TotalSize = DATALENGTH(lvalue),
@.LastRead = 0,
@.ReadSize = CASE WHEN (@.RowsetCharLen < DATALENGTH(lvalue))
THEN @.RowsetCharLen
ELSE DATALENGTH(lvalue)
END
FROM dtproperties
WHERE id = @.id
-- Loop through the image data, returning rows of the desired length
---------------------
IF (@.PointerToData is not null) AND
(@.ReadSize > 0)
WHILE (@.LastRead < @.TotalSize)
BEGIN
IF ((@.ReadSize + @.LastRead) > @.TotalSize)
SET @.ReadSize = @.TotalSize - @.LastRead
READTEXT dtproperties.lvalue @.PointerToData @.LastRead
@.ReadSize
SET @.LastRead = @.LastRead + @.ReadSize
END
-- Processing Complete
-------
SET @.ReturnCode = 0
Procedure_Exit:
-----
SET NOCOUNT OFF
RETURN @.ReturnCode
GO
GRANT EXECUTE ON [dbo].[usp_dtpropertiesTextToRowset] TO [Public]
GO|||Yikes!!! SOmeone just correctly pointed out to me that there are
actually three components which I should have posted... I neglected
to post the stored procedure: usp_dtpropertiesTextToRowset Which is
required for the process to work.
Here it is, the third component... This should be built after the
function, but before the other procedure, since the other procedure
references this one.
-Clay
if exists (select 1
from sysobjects
where name = 'usp_dtpropertiesTextToRowset'
and type = 'P')
drop procedure usp_dtpropertiesTextToRowset
GO
CREATE PROCEDURE dbo.usp_dtpropertiesTextToRowset @.idint,
@.RowsetCharLenint =
255
AS
-- Variable Declarations
--------
Declare @.PointerToDatavarbinary (16)
Declare @.TotalSizeint
Declare @.LastReadint
Declare @.ReadSizeint
Declare @.ReturnCodeint
-- Initializations
------
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
SET @.ReturnCode = -1
-- Establish the Pointer to the Image data
-------------
SELECT @.PointerToData = TEXTPTR(lvalue),
@.TotalSize = DATALENGTH(lvalue),
@.LastRead = 0,
@.ReadSize = CASE WHEN (@.RowsetCharLen < DATALENGTH(lvalue))
THEN @.RowsetCharLen
ELSE DATALENGTH(lvalue)
END
FROM dtproperties
WHERE id = @.id
-- Loop through the image data, returning rows of the desired length
---------------------
IF (@.PointerToData is not null) AND
(@.ReadSize > 0)
WHILE (@.LastRead < @.TotalSize)
BEGIN
IF ((@.ReadSize + @.LastRead) > @.TotalSize)
SET @.ReadSize = @.TotalSize - @.LastRead
READTEXT dtproperties.lvalue @.PointerToData @.LastRead
@.ReadSize
SET @.LastRead = @.LastRead + @.ReadSize
END
-- Processing Complete
-------
SET @.ReturnCode = 0
Procedure_Exit:
-----
SET NOCOUNT OFF
RETURN @.ReturnCode
GO
GRANT EXECUTE ON [dbo].[usp_dtpropertiesTextToRowset] TO [Public]
GO|||One last note on the subject...
You might have already noticed, but I had coded two of the components
to include a reference to the database which I work with (Payment).
You'll need to change that name, in the function
ufn_VarbinaryToVarcharHex, and in the procedure
usp_ScriptDatabaseDiagrams, to reflect the database name which you are
working with.
-Clay|||One last note on the subject...
You might have already noticed, but I had coded two of the components
to include a reference to the database which I work with (Payment).
You'll need to change that name, in the function
ufn_VarbinaryToVarcharHex, and in the procedure
usp_ScriptDatabaseDiagrams, to reflect the database name which you are
working with.
-Clay
Script User Roles
I have created a user role in my SQL 7 database. I would like to copy this
role to 8 other servers. I created the role using Enterprise Manager. Can
you advise how to query this out to SQL Script so that I could just run the
script on the other servers to create the role and then assign users as
required.In Enterprise Manager you can script database and select option to script
roles and permissions also. Then find in the generated script related T-SQL
by role name (starting from sp_addrole and below).
Martin Rakhmanov
"dtatham" <dtatham@.discussions.microsoft.com> wrote in message
news:79CC672A-B46A-4F2C-9CC1-F7DAE8772DB8@.microsoft.com...
>I have created a user role in my SQL 7 database. I would like to copy this
> role to 8 other servers. I created the role using Enterprise Manager.
> Can
> you advise how to query this out to SQL Script so that I could just run
> the
> script on the other servers to create the role and then assign users as
> required.
>
role to 8 other servers. I created the role using Enterprise Manager. Can
you advise how to query this out to SQL Script so that I could just run the
script on the other servers to create the role and then assign users as
required.In Enterprise Manager you can script database and select option to script
roles and permissions also. Then find in the generated script related T-SQL
by role name (starting from sp_addrole and below).
Martin Rakhmanov
"dtatham" <dtatham@.discussions.microsoft.com> wrote in message
news:79CC672A-B46A-4F2C-9CC1-F7DAE8772DB8@.microsoft.com...
>I have created a user role in my SQL 7 database. I would like to copy this
> role to 8 other servers. I created the role using Enterprise Manager.
> Can
> you advise how to query this out to SQL Script so that I could just run
> the
> script on the other servers to create the role and then assign users as
> required.
>
Script to reset the "Identity Seed" and "Identity Increment" values
hi,
Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.
I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.
The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?
Any help whould be greatly appreciated.
Thanks,
Prashanth ReddyOriginally posted by Prashanth
hi,
Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.
I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.
The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?
Any help whould be greatly appreciated.
Thanks,
Prashanth Reddy
You have 2 options that spring to mind:
1. You can use TRUNCATE TABLE to accomplish this, but you will lose all data and as its a non-logged operation nothing will be written to the transaction log. I should add that if the table is referenced by a foriegn key constraint then you will not be able to use this method.
Usage: TRUNCATE TABLE tablename
2. The other option is to use DBCC CHECKIDENT to reset the seed:
Usage: DBCC CHECKIDENT (tablename, RESEED, 0)
macka.|||thanks for the response.|||thanks for the response.
I have patient table with PatientID as auto increment field
with
"Identity Increment" as 1 and
"Identity seed" as 1
I can chane Identity Seed using
DBCC checkident (patient,reseed,4)
How do i change the "Identity Increment" value to 10?
Prashanth|||The following script should work. In this example it resets the seed to 4 and increment to 10.
If the table is referenced by foreign key constraints then you will need to drop them before this script is run and add them back after it completes.
ALTER TABLE Demo
DROP CONSTRAINT PK_Demo
ALTER TABLE Demo
DROP COLUMN DemoID
ALTER TABLE Demo
ADD DemoID INT IDENTITY(4,10)
ALTER TABLE Demo ADD CONSTRAINT
PK_Demo PRIMARY KEY CLUSTERED
(
DemoID
) ON [PRIMARY]
macka.
Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.
I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.
The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?
Any help whould be greatly appreciated.
Thanks,
Prashanth ReddyOriginally posted by Prashanth
hi,
Can any one help me out with the script to reset the "Identity Seed" and "Identity Increment" values for the Identity field.
I can go to enterprise manager and do this or event Generate the script. The script Enterprise manager is creating drops the original table and recreates the table with new defination.
The table can't be droped as it is part of replication.
Is there a work around for this ?
Can I change the "Identity Seed" and "Identity Increment" fields with out droping the table. ?
Any help whould be greatly appreciated.
Thanks,
Prashanth Reddy
You have 2 options that spring to mind:
1. You can use TRUNCATE TABLE to accomplish this, but you will lose all data and as its a non-logged operation nothing will be written to the transaction log. I should add that if the table is referenced by a foriegn key constraint then you will not be able to use this method.
Usage: TRUNCATE TABLE tablename
2. The other option is to use DBCC CHECKIDENT to reset the seed:
Usage: DBCC CHECKIDENT (tablename, RESEED, 0)
macka.|||thanks for the response.|||thanks for the response.
I have patient table with PatientID as auto increment field
with
"Identity Increment" as 1 and
"Identity seed" as 1
I can chane Identity Seed using
DBCC checkident (patient,reseed,4)
How do i change the "Identity Increment" value to 10?
Prashanth|||The following script should work. In this example it resets the seed to 4 and increment to 10.
If the table is referenced by foreign key constraints then you will need to drop them before this script is run and add them back after it completes.
ALTER TABLE Demo
DROP CONSTRAINT PK_Demo
ALTER TABLE Demo
DROP COLUMN DemoID
ALTER TABLE Demo
ADD DemoID INT IDENTITY(4,10)
ALTER TABLE Demo ADD CONSTRAINT
PK_Demo PRIMARY KEY CLUSTERED
(
DemoID
) ON [PRIMARY]
macka.
Tuesday, March 20, 2012
Script SP with permission automatically
Hi,
In enterprise manager, there is a option to automatically script any trigger, permission on a table or store procedure. It seems that this feature is gone from new the SQL management studio. Is there a way to easy script this easily just like before?
thanks,
Bernie
Hi there,Well, you can script triggers by going to the specific table in the Object Explorer and expanding the node for that table....There should be a sub-node for triggers which you can expand to see the triggers defined on the table. Right click on the desired trigger and select "Script Trigger As" from the context menu that appears.
Note that you can take the same action for most of the other sub-nodes you see under the table such as Constraints, Indexes and Keys.
As for scripting objects with permissions, I believe that you will need to use the Script Wizard to do this. To access the wizard you right click on a database and from the context menu that appears you select: Tasks > Generate Scripts
The Script Wizard will now appear. You can run through it as follows:
1) Click the "Next>" button on the initial screen
2) Select the database you want to script and then click the "Next>" button. You can also tick the "Script All Objects In The Selected Database" option if you want to script absolutely everything
3) You will be prompted to select scripting options such as scripting object permissions, table triggers and constraints etc. Review the options and select the ones that are right for you
4) If you did not select the "Script All Objects In The Selected Database" option, you will now get to choose which database objects (e.g. stored procedures) to script and will run through a series of screens in order to make your selection
It's pretty much smooth sailing after that.
Hope that helps a bit, but sorry if it doesn't
|||
sql server management studio 2005 has a better scripting support than sql server enterprise manager 2000.
you can right click the object and the choose "script object to ; alter ; create ; delete;"
yet another way to do it is to right click the object and then choose properties
on the top portion of the properties tab there's a clcikable script dropdown which allows you to script to clipboard, file, jobs or to new query window. on the left hand side of the properties window there's a listbox with the following item geneneral,permission, extended properties. to script permission, choose permission from the list and then click the script dropdown
here's another
You can also right click the database then task then choose then choose generate scripts. this will lunch the scripts wizard which is somewaht similar to those of the EM
Monday, March 12, 2012
Script Generation
In SQL 2000 Enterprise Manager, you can create SQL scripts for
procedures by right clicking and choosing generate SQL script. It sets
some database defaults and turns some things on and off. Is there a
way to modify the things that are created to return more data? Is it
in a template where i can add some code. I want to include 1 line in
each script it generates but cant find where to do it. Can it be done?
How?
Thanks so much in adavnce!
-dL
Hi DL,
You can not customize that. Instead you can use SQL DMO to generate customized scripts.
See the below link:-
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Thanks
Hari
SQL Server MVP
____________________________________
Sweet Daddy Coconut Wrote:
In SQL 2000 Enterprise Manager, you can create SQL scripts for
procedures by right clicking and choosing generate SQL script. It sets
some database defaults and turns some things on and off. Is there a
way to modify the things that are created to return more data? Is it
in a template where i can add some code. I want to include 1 line in
each script it generates but cant find where to do it. Can it be done?
How?
Thanks so much in adavnce!
-dL
Sent via SreeSharp NewsReader http://www.SreeSharp.com
procedures by right clicking and choosing generate SQL script. It sets
some database defaults and turns some things on and off. Is there a
way to modify the things that are created to return more data? Is it
in a template where i can add some code. I want to include 1 line in
each script it generates but cant find where to do it. Can it be done?
How?
Thanks so much in adavnce!
-dL
Hi DL,
You can not customize that. Instead you can use SQL DMO to generate customized scripts.
See the below link:-
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Thanks
Hari
SQL Server MVP
____________________________________
Sweet Daddy Coconut Wrote:
In SQL 2000 Enterprise Manager, you can create SQL scripts for
procedures by right clicking and choosing generate SQL script. It sets
some database defaults and turns some things on and off. Is there a
way to modify the things that are created to return more data? Is it
in a template where i can add some code. I want to include 1 line in
each script it generates but cant find where to do it. Can it be done?
How?
Thanks so much in adavnce!
-dL
Sent via SreeSharp NewsReader http://www.SreeSharp.com
Wednesday, March 7, 2012
Script Alerts
I know that I can script all of the server alerts,
operators, and jobs using enterprise manager, however,
does anyone know how I can go about scripting out the
alerts, operators, and jobs not using enterprise
manager. What I want to do is make this part of my
disaster recovery process to have the SQLAgent run a job
every night to capture a script of the alerts, operators,
and jobs so that I can use these scripts to recovery said
objects in case of a disaster.Take a look at the Script method in the SQL-DMO
documenation in the SQL Server Books Online.
Linchi
>--Original Message--
>I know that I can script all of the server alerts,
>operators, and jobs using enterprise manager, however,
>does anyone know how I can go about scripting out the
>alerts, operators, and jobs not using enterprise
>manager. What I want to do is make this part of my
>disaster recovery process to have the SQLAgent run a job
>every night to capture a script of the alerts, operators,
>and jobs so that I can use these scripts to recovery said
>objects in case of a disaster.
>.
>|||In addition to Linchi's reply, if you are not comfortable with SQL-DMO, you
can contact me and I would develop a script for you if you were to
compensate me for my time. Pls remove NO_SPAM(s) from my email address if
you do.
--
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
> I know that I can script all of the server alerts,
> operators, and jobs using enterprise manager, however,
> does anyone know how I can go about scripting out the
> alerts, operators, and jobs not using enterprise
> manager. What I want to do is make this part of my
> disaster recovery process to have the SQLAgent run a job
> every night to capture a script of the alerts, operators,
> and jobs so that I can use these scripts to recovery said
> objects in case of a disaster.
>|||But the script is rather simple to write. Here's a Perl
script I just whipped up to script out Alerts, Jobs, and
Operators:
# Begin script
use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO';
my $serverName = 'yourSQLServer';
my ($server, $obj);
my $script = shift; # get command line argument
$server = Win32::OLE->new('SQLDMO.SQLServer')
or die "Could not create SQLDMO object.";
$server->{LoginSecure} = 1;
$server->connect($serverName, '', '');
if (Win32::OLE->LastError()) {
die "***Err: Could not connect to $serverName.";
}
print "**** Scripting Alerts ... \n";
my $alerts = $server->JobServer->Alerts();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the alerts collection in
$serverName.";
}
foreach $obj (in($alerts)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
print "**** Scripting Jobs ... \n";
my $jobs = $server->JobServer->Jobs();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the jobs collection in
$serverName.";
}
foreach $obj (in($jobs)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
print "**** Scripting Operators ... \n";
my $operators = $server->JobServer->Operators();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the operators collection in
$serverName.";
}
foreach $obj (in($operators)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
$server->disconnect();
$server->DESTROY();
# End script
Linchi
>--Original Message--
>In addition to Linchi's reply, if you are not comfortable
with SQL-DMO, you
>can contact me and I would develop a script for you if
you were to
>compensate me for my time. Pls remove NO_SPAM(s) from my
email address if
>you do.
>--
>Ata R
>Parvan Consulting Inc
>NO_SPAMar_alias001@.NO_SPAMparvan.net
>
>"Z" <anonymous@.discussions.microsoft.com> wrote in message
>news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
>> I know that I can script all of the server alerts,
>> operators, and jobs using enterprise manager, however,
>> does anyone know how I can go about scripting out the
>> alerts, operators, and jobs not using enterprise
>> manager. What I want to do is make this part of my
>> disaster recovery process to have the SQLAgent run a job
>> every night to capture a script of the alerts,
operators,
>> and jobs so that I can use these scripts to recovery
said
>> objects in case of a disaster.
>
>.
>|||AndI have a VB version of this that will script out the whole server for
you. As Linchi alludes to, SQL DMO is an untuitive object model and can
really help you out.
If you want the application then mail me
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:00c901c3d53f$db652240$a301280a@.phx.gbl...
> But the script is rather simple to write. Here's a Perl
> script I just whipped up to script out Alerts, Jobs, and
> Operators:
> # Begin script
> use strict;
> use Win32::OLE 'in';
> use Win32::OLE::Const 'Microsoft SQLDMO';
> my $serverName = 'yourSQLServer';
> my ($server, $obj);
> my $script = shift; # get command line argument
> $server = Win32::OLE->new('SQLDMO.SQLServer')
> or die "Could not create SQLDMO object.";
> $server->{LoginSecure} = 1;
> $server->connect($serverName, '', '');
> if (Win32::OLE->LastError()) {
> die "***Err: Could not connect to $serverName.";
> }
> print "**** Scripting Alerts ... \n";
> my $alerts = $server->JobServer->Alerts();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the alerts collection in
> $serverName.";
> }
> foreach $obj (in($alerts)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> print "**** Scripting Jobs ... \n";
> my $jobs = $server->JobServer->Jobs();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the jobs collection in
> $serverName.";
> }
> foreach $obj (in($jobs)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> print "**** Scripting Operators ... \n";
> my $operators = $server->JobServer->Operators();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the operators collection in
> $serverName.";
> }
> foreach $obj (in($operators)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> $server->disconnect();
> $server->DESTROY();
> # End script
> Linchi
> >--Original Message--
> >In addition to Linchi's reply, if you are not comfortable
> with SQL-DMO, you
> >can contact me and I would develop a script for you if
> you were to
> >compensate me for my time. Pls remove NO_SPAM(s) from my
> email address if
> >you do.
> >
> >--
> >Ata R
> >Parvan Consulting Inc
> >NO_SPAMar_alias001@.NO_SPAMparvan.net
> >
> >
> >"Z" <anonymous@.discussions.microsoft.com> wrote in message
> >news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
> >> I know that I can script all of the server alerts,
> >> operators, and jobs using enterprise manager, however,
> >> does anyone know how I can go about scripting out the
> >> alerts, operators, and jobs not using enterprise
> >> manager. What I want to do is make this part of my
> >> disaster recovery process to have the SQLAgent run a job
> >> every night to capture a script of the alerts,
> operators,
> >> and jobs so that I can use these scripts to recovery
> said
> >> objects in case of a disaster.
> >>
> >
> >
> >.
> >
operators, and jobs using enterprise manager, however,
does anyone know how I can go about scripting out the
alerts, operators, and jobs not using enterprise
manager. What I want to do is make this part of my
disaster recovery process to have the SQLAgent run a job
every night to capture a script of the alerts, operators,
and jobs so that I can use these scripts to recovery said
objects in case of a disaster.Take a look at the Script method in the SQL-DMO
documenation in the SQL Server Books Online.
Linchi
>--Original Message--
>I know that I can script all of the server alerts,
>operators, and jobs using enterprise manager, however,
>does anyone know how I can go about scripting out the
>alerts, operators, and jobs not using enterprise
>manager. What I want to do is make this part of my
>disaster recovery process to have the SQLAgent run a job
>every night to capture a script of the alerts, operators,
>and jobs so that I can use these scripts to recovery said
>objects in case of a disaster.
>.
>|||In addition to Linchi's reply, if you are not comfortable with SQL-DMO, you
can contact me and I would develop a script for you if you were to
compensate me for my time. Pls remove NO_SPAM(s) from my email address if
you do.
--
Ata R
Parvan Consulting Inc
NO_SPAMar_alias001@.NO_SPAMparvan.net
"Z" <anonymous@.discussions.microsoft.com> wrote in message
news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
> I know that I can script all of the server alerts,
> operators, and jobs using enterprise manager, however,
> does anyone know how I can go about scripting out the
> alerts, operators, and jobs not using enterprise
> manager. What I want to do is make this part of my
> disaster recovery process to have the SQLAgent run a job
> every night to capture a script of the alerts, operators,
> and jobs so that I can use these scripts to recovery said
> objects in case of a disaster.
>|||But the script is rather simple to write. Here's a Perl
script I just whipped up to script out Alerts, Jobs, and
Operators:
# Begin script
use strict;
use Win32::OLE 'in';
use Win32::OLE::Const 'Microsoft SQLDMO';
my $serverName = 'yourSQLServer';
my ($server, $obj);
my $script = shift; # get command line argument
$server = Win32::OLE->new('SQLDMO.SQLServer')
or die "Could not create SQLDMO object.";
$server->{LoginSecure} = 1;
$server->connect($serverName, '', '');
if (Win32::OLE->LastError()) {
die "***Err: Could not connect to $serverName.";
}
print "**** Scripting Alerts ... \n";
my $alerts = $server->JobServer->Alerts();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the alerts collection in
$serverName.";
}
foreach $obj (in($alerts)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
print "**** Scripting Jobs ... \n";
my $jobs = $server->JobServer->Jobs();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the jobs collection in
$serverName.";
}
foreach $obj (in($jobs)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
print "**** Scripting Operators ... \n";
my $operators = $server->JobServer->Operators();
if (Win32::OLE->LastError()) {
die "**Err: Could not get the operators collection in
$serverName.";
}
foreach $obj (in($operators)) {
$obj->Script(SQLDMOScript_Default |
SQLDMOScript_AppendToFile |
SQLDMOScript_OwnerQualify |
SQLDMOScript_IncludeHeaders |
SQLDMOScript_Drops |
SQLDMOScript_ToFileOnly,
$script);
}
$server->disconnect();
$server->DESTROY();
# End script
Linchi
>--Original Message--
>In addition to Linchi's reply, if you are not comfortable
with SQL-DMO, you
>can contact me and I would develop a script for you if
you were to
>compensate me for my time. Pls remove NO_SPAM(s) from my
email address if
>you do.
>--
>Ata R
>Parvan Consulting Inc
>NO_SPAMar_alias001@.NO_SPAMparvan.net
>
>"Z" <anonymous@.discussions.microsoft.com> wrote in message
>news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
>> I know that I can script all of the server alerts,
>> operators, and jobs using enterprise manager, however,
>> does anyone know how I can go about scripting out the
>> alerts, operators, and jobs not using enterprise
>> manager. What I want to do is make this part of my
>> disaster recovery process to have the SQLAgent run a job
>> every night to capture a script of the alerts,
operators,
>> and jobs so that I can use these scripts to recovery
said
>> objects in case of a disaster.
>
>.
>|||AndI have a VB version of this that will script out the whole server for
you. As Linchi alludes to, SQL DMO is an untuitive object model and can
really help you out.
If you want the application then mail me
--
--
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:00c901c3d53f$db652240$a301280a@.phx.gbl...
> But the script is rather simple to write. Here's a Perl
> script I just whipped up to script out Alerts, Jobs, and
> Operators:
> # Begin script
> use strict;
> use Win32::OLE 'in';
> use Win32::OLE::Const 'Microsoft SQLDMO';
> my $serverName = 'yourSQLServer';
> my ($server, $obj);
> my $script = shift; # get command line argument
> $server = Win32::OLE->new('SQLDMO.SQLServer')
> or die "Could not create SQLDMO object.";
> $server->{LoginSecure} = 1;
> $server->connect($serverName, '', '');
> if (Win32::OLE->LastError()) {
> die "***Err: Could not connect to $serverName.";
> }
> print "**** Scripting Alerts ... \n";
> my $alerts = $server->JobServer->Alerts();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the alerts collection in
> $serverName.";
> }
> foreach $obj (in($alerts)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> print "**** Scripting Jobs ... \n";
> my $jobs = $server->JobServer->Jobs();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the jobs collection in
> $serverName.";
> }
> foreach $obj (in($jobs)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> print "**** Scripting Operators ... \n";
> my $operators = $server->JobServer->Operators();
> if (Win32::OLE->LastError()) {
> die "**Err: Could not get the operators collection in
> $serverName.";
> }
> foreach $obj (in($operators)) {
> $obj->Script(SQLDMOScript_Default |
> SQLDMOScript_AppendToFile |
> SQLDMOScript_OwnerQualify |
> SQLDMOScript_IncludeHeaders |
> SQLDMOScript_Drops |
> SQLDMOScript_ToFileOnly,
> $script);
> }
> $server->disconnect();
> $server->DESTROY();
> # End script
> Linchi
> >--Original Message--
> >In addition to Linchi's reply, if you are not comfortable
> with SQL-DMO, you
> >can contact me and I would develop a script for you if
> you were to
> >compensate me for my time. Pls remove NO_SPAM(s) from my
> email address if
> >you do.
> >
> >--
> >Ata R
> >Parvan Consulting Inc
> >NO_SPAMar_alias001@.NO_SPAMparvan.net
> >
> >
> >"Z" <anonymous@.discussions.microsoft.com> wrote in message
> >news:027d01c3d49b$344851c0$a501280a@.phx.gbl...
> >> I know that I can script all of the server alerts,
> >> operators, and jobs using enterprise manager, however,
> >> does anyone know how I can go about scripting out the
> >> alerts, operators, and jobs not using enterprise
> >> manager. What I want to do is make this part of my
> >> disaster recovery process to have the SQLAgent run a job
> >> every night to capture a script of the alerts,
> operators,
> >> and jobs so that I can use these scripts to recovery
> said
> >> objects in case of a disaster.
> >>
> >
> >
> >.
> >
Subscribe to:
Posts (Atom)