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
>