scripting data

Hi,

I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

Thanks much for any advice regarding this.On Feb 13, 6:07 am, "farseer" <fars...@.optonline.netwrote:

Quote:

Originally Posted by

Hi,
>
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
>
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
>
Thanks much for any advice regarding this.

Hi,

Yes, there is and it's called DB Ghost (www.dbghost.com). It has a
Data and Schema Scripter (which does what you need) and a host of
other tools that form a complete change management process for SQL
Server.and can all be called from the command line for full process
automation :)

Kind regards,

Malcolm|||farseer (farseer@.optonline.net) writes:

Quote:

Originally Posted by

Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?

SELECT 'bcp yourdb..' + name + ' out ' + name + '.bcp -n -T'
FROM sys.objects
WHERE type = 'U'

Copy result into a BAT file and run.

The to load:

SELECT 'bcp yourtargetdb..' + name + ' in ' + name + '.bcp -n -T ' +
'-S REMOTSER' +
CASE WHEN EXISTS (SELECT *
FROM sys.columns c
WHERE o.object_id = c.object_id
AND c.is_identity = 1)
THEN ' -E '
ELSE ''
END
FROM sys.objects o
WHERE type = 'U'

I assume that you are not using any data types that are not available
in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||I've listed some tools here: http://www.karaszi.com/SQLServer/info_generate_script.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegroups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

scripting data

Hi,
I need to take data from a SQL Serer 2005 database, and load into a
remote 2000 database. I've already been able to script and create the
database objects (MS SQL Server 2005 has a nifty option which allows
you to scripting for SQL Server 2000 compliance). Now i just need to
get the data in.
Is there a tool or utility out there that i can use to generate insert
statements for all the tables in database?
Thanks much for any advice regarding this.
You could uses SSIS to transfer the data, however if you want to generate
insert scripts then take a look at sp_generate_inserts
http://vyaskn.tripod.com/code.htm#inserts
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Hi
You are able to create a linked server between both and share the data
"farseer" <farseer@.optonline.net> wrote in message
news:1171346776.733940.21960@.a75g2000cwd.googlegro ups.com...
> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>
|||Try www.sqlscripter.com to script your data.
Its free.
"farseer" wrote:

> Hi,
> I need to take data from a SQL Serer 2005 database, and load into a
> remote 2000 database. I've already been able to script and create the
> database objects (MS SQL Server 2005 has a nifty option which allows
> you to scripting for SQL Server 2000 compliance). Now i just need to
> get the data in.
> Is there a tool or utility out there that i can use to generate insert
> statements for all the tables in database?
> Thanks much for any advice regarding this.
>

Scripting an index to a table

What is the correct syntax to add an index to a table?
Example: tableA with fieldB- create an index on fieldBCheck out CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp).

-PatP|||Originally posted by Pat Phelan
Check out CREATE INDEX (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_64l4.asp).

-PatP

Thank you! I found it immediatley after I submitted the post|||Does anyone know the correct procedure to setup a pull relationship from MS Access to a SQL database and vice versa?|||Refer to your post http://www.dbforums.com/t994687.html

scripting an entire database along with the data

Hi,
I would like to know how to create a script for an exsisting database such
that the script also contains statements to recreate all the data in the
database aswell.
regards,
Ahmed.Ahmed Shafi wrote:
> Hi,
> I would like to know how to create a script for an exsisting database
> such that the script also contains statements to recreate all the
> data in the database aswell.
There was a post about a free tool http://www.sqlscripter.com in
sqlserver.tools recently.
You might also want to see ApexSQL Script for this
http://www.apexsql.com/index_ai.htm
There is also really a good one in Vyas's site,
http://vyaskn.tripod.com/code/generate_inserts.txt
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create insert statements via any of the following tools
Largo SQL Tools -- http://www.largosqltools.com/
ObjectScripter -- http://www.rac4sql.net/
QALite -- http://www.rac4sql.net/
Lockwood Tech -- http://www.lockwoodtech.com/
You can create the database script via some of these tools. You can =
also use Enterprise Manager to script the DDL for you.
With that said, what is your goal? If you want to "save" or "move" your =
database, an easier method might be BACKUP and RESTORE. You can execute =
a Transact-SQL backup command to the database server. This command will =
backup your database (tables, data, stored procedures...) to a file =
that you can backup to tape or burn to CD. You can also use this file =
to recreate your entire database.
--=20
Keith
"Ahmed Shafi" <aflatoon99 At hotmail DOT com> wrote in message =
news:OxMW4t5BEHA.1236@.TK2MSFTNGP11.phx.gbl...
> Hi,
>=20
> I would like to know how to create a script for an exsisting database =
such
> that the script also contains statements to recreate all the data in =
the
> database aswell.
>=20
> regards,
> Ahmed.
>=20
>|||Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||>> you may want to take a look at what else we have to offer...
You are certainly good at copying others' code and claiming it as your own.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:61AD85AE-450A-4599-9503-EAB1CD7AF58B@.microsoft.com...
Hi Keith & Ahmed,
take a look at this script we give away
http://www.innovartis.co.uk/downloa..._statements.zip
you may want to take a look at what else we have to offer...|||I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!

>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||First of all, who are you to 'give away' my code, on your website?
I did look at the code, and you did make a few modifications here and there.
But it is a minimum courtesy in software industry, to credit the original
author when using his/her code.
You wrote "There is no claim that it is your code". But hey, the code is
hosted on your website. You haven't bothered giving credit to me. Isn't that
implying that it's your code?
If you want to continue offering that code on your website, you better add
my name and URL in the header of that script. Or else, remove it from your
site.
I hate plagiarism.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
MODIFIED/IMPROVED!
GET BENT!!!

>--Original Message--
offer...
>You are certainly good at copying others' code and
claiming it as your own.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:61AD85AE-450A-4599-9503-
EAB1CD7AF58B@.microsoft.com...
>Hi Keith & Ahmed,
>take a look at this script we give away
>http://www.innovartis.co.uk/downloa...reate_insert_st
atements.zip
>you may want to take a look at what else we have to
offer...
>
>.
>|||mark baekdal wrote:

>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>
>
Yeah, right. Read your own site:
<begin quote from your site>
The DB Ghost Process
*What you do, as a one-time process, is to:*
* Script out, using Enterprise Manager, your entire production
database's schema and static data into individual scripts. To
script your static data use this _stored procedure_ [link to
Vyas's sp] <downloads/sp_create_insert_statements.zip>. Some
companies actually market products to do this...
<end quote from your site>
I guess you find it easier to steal products to do this, remove the
copyright and authorship info and then throw a tantrum when you get
found out.
People like you suck.
Vyas's original code:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_generate_inserts (Build 22)
(Copyright 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Acknowledgements:
Divya Kalra -- For beta testing
Mark Charsley -- For reporting a problem with scripting
uniqueidentifier columns with NULL values
Artur Zeygman -- For helping me simplify a bit of code for
handling non-dbo owned tables
Joris Laperre -- For reporting a regression bug in handling
text/ntext columns
Tested on: SQL Server 7.0 and SQL Server 2000
Date created: January 17th 2001 21:52 GMT
Date modified: May 1st 2002 19:50 GMT
Email: vyaskn@.hotmail.com
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
Whenever possible, Use @.include_column_list parameter to ommit
column list in the INSERT statement, for better results
IMPORTANT: This procedure is not tested with internation data
(Extended characters or Unicode). If needed
you might want to convert the datatypes of character variables
in this procedure to their respective unicode counterparts
like nchar and nvarchar
The stolen code on your site:
BEGIN
/ ****************************************
***********************************
********************************
Procedure: sp_create_insert_statements
Purpose: To generate INSERT statements from existing data.
These INSERTS can be executed to regenerate the data at some
other location.
This procedure is also useful to create a database setup, where
in you can
script your data along with your table definitions.
NOTE: This procedure may not work with tables with too many columns.
Results can be unpredictable with huge text columns or SQL
Server 2000's sql_variant data types
IMPORTANT: Whenever possible, Use @.include_column_list parameter
to ommit column list in the INSERT statement, for better results
>GET BENT!!!
>
>
>offer...
>
>claiming it as your own.
>
>wrote in message
>
>EAB1CD7AF58B@.microsoft.com...
>
>atements.zip
>
>offer...
>|||The code has been modified extensively.
Deletes have been added.
A transaction has been created to make it atomic/durable.
Triggers have been turned off and then turned back on to
strop unwanted inserts.
Error handling has been added for completeness.
Transaction checking has been added for portability.
All of the above has added value to the script - that was
my intention. I did try to contact Vyas originally (about
two years ago) through the contact on
http://planetsourcecode.com/ where I originally found it,
however no-one got back to me. If I was trying to sell it
then I could understand your comments, but it is posted
here to help, and it is free, and it has been
modified/improved which may well help Ahmed - the whole
point of the excercise.
I will put in comments crediting Vyas for the original
work done, it has moved on though and it is not my
intention to ever profit from the work - I repeat - I
think I added value to it and it will always remain free.

>--Original Message--
>First of all, who are you to 'give away' my code, on
your website?
>I did look at the code, and you did make a few
modifications here and there.
>But it is a minimum courtesy in software industry, to
credit the original
>author when using his/her code.
>You wrote "There is no claim that it is your code". But
hey, the code is
>hosted on your website. You haven't bothered giving
credit to me. Isn't that
>implying that it's your code?
>If you want to continue offering that code on your
website, you better add
>my name and URL in the header of that script. Or else,
remove it from your
>site.
>I hate plagiarism.
>--
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:c3cc01c408f1$201cdec0$a301280a@.phx.gbl...
>I NEVER CLAIMED IT WAS MY OWN AND IT HAS BEEN
>MODIFIED/IMPROVED!
>GET BENT!!!
>
>offer...
>claiming it as your own.
>wrote in message
>EAB1CD7AF58B@.microsoft.com...
t
>atements.zip
>offer...
>
>.
>|||And another modification made
An order by clause was added to make sure the insert
statements are ordered by the primary key if it exists.
Previous to this contact - I hadn't recieved any even
though I did ask - therefore I could not ask anyone
requiring answers to questions to contact Vyas as I
couldn't get them. This is why there wasn't any
references. That has since changed and I have talked with
Vyas who seems happy with the arrangement as it now
stands.
I for one never want to have plagiarist next to my name.
And the end result is everyone has a damn fine piece of
code for free - that must be a good ending to this story!sql

Scripting ALTER TABLE

I need to create a script to disable all triggers and constraints in my
database.

It appears as though I cannot use a local variable for the table name in the
ALTER TABLE statement (e.g. ALTER TABLE @.TBL).

Is there any reason for this?

Thanks,

Kevin"Kevin Haugen" <khaugen@.pacbell.net> wrote in message
news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>I need to create a script to disable all triggers and constraints in my
>database.
> It appears as though I cannot use a local variable for the table name in
> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
> Is there any reason for this?
> Thanks,
> Kevin

You can't use variables in place of table or column names, except when using
dynamic SQL, which has its own issues. Although if you're a DBA running an
admin script, then it's usually a reasonable option - there's more
discussion here:

http://www.sommarskog.se/dynamic_sql.html

Unfortunately, you don't say what your goal is, but if it's to load data
into the database, then all the usual loading tools (bcp.exe, DTS, BULK
INSERT) can ignore both constraints and triggers, so you might not need a
script anyway.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41ecd751$1_1@.news.bluewin.ch...
> "Kevin Haugen" <khaugen@.pacbell.net> wrote in message
> news:jx1Hd.12736$5R.1377@.newssvr21.news.prodigy.co m...
>>I need to create a script to disable all triggers and constraints in my
>>database.
>>
>> It appears as though I cannot use a local variable for the table name in
>> the ALTER TABLE statement (e.g. ALTER TABLE @.TBL).
>>
>> Is there any reason for this?
>>
>> Thanks,
>>
>> Kevin
>>
> You can't use variables in place of table or column names, except when
> using dynamic SQL, which has its own issues. Although if you're a DBA
> running an admin script, then it's usually a reasonable option - there's
> more discussion here:
> http://www.sommarskog.se/dynamic_sql.html
> Unfortunately, you don't say what your goal is, but if it's to load data
> into the database, then all the usual loading tools (bcp.exe, DTS, BULK
> INSERT) can ignore both constraints and triggers, so you might not need a
> script anyway.
> Simon

I'll look into it. I'm planning on converting existing data into a new
format. Since I have to identify each table and write a query to do the
conversion, I could easily do a copy/paste for each table to disable and
re-enable the triggers and constraints. I am hoping to shortcut some of the
work by automating that piece.

Thanks,

Kevin

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

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
>

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
>

Scripting a Database

All,
I need to be able to create a script of a database from within a .NET
application. Does anyone know of any code (T-SQL, C# etc.) available to do
this?
Thanks.Search for "SQLDMO .Net" in your favourite search engine and you will get a
ton of info.
Darrel
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:jsCdnac1rpQIBqXeRVn-hg@.buckeye-express.com...
> All,
> I need to be able to create a script of a database from within a .NET
> application. Does anyone know of any code (T-SQL, C# etc.) available to do
> this?
> Thanks.
>|||I was under the impression that SQLDMO was still only available as a COM
object ... thus in .Net, you would have to use a wrapper.
"Darrel Miller" <darrel@.tavis.ca> wrote in message
news:Oc1IaLwwFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Search for "SQLDMO .Net" in your favourite search engine and you will get
> a ton of info.
> Darrel
> "Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:jsCdnac1rpQIBqXeRVn-hg@.buckeye-express.com...
>|||Earl wrote:
> I was under the impression that SQLDMO was still only available as a
> COM object ... thus in .Net, you would have to use a wrapper.
> "Darrel Miller" <darrel@.tavis.ca> wrote in message
> news:Oc1IaLwwFHA.3236@.TK2MSFTNGP14.phx.gbl...
That's true, but it's somewhat automated by Visual Studio .Net:
Visual Studio .NET generates an interop assembly containing metadata
when you add a reference to a given type library. If a primary interop
assembly is available, Visual Studio uses the existing assembly before
generating a new interop assembly.
To add a reference to a type library
1.. Install the COM DLL or EXE file on your computer, unless a Windows
Setup.exe performs the installation for you.
2.. From the Project menu, select References.
3.. Select the COM tab.
4.. Select the type library from the Available References list, or
browse for the TLB file.
5.. Click OK.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Scripting a Database

All,
I need to be able to create a script of a database from within a .NET
application. Does anyone know of any code (T-SQL, C# etc.) available to do
this?
Thanks.
You probably need to wrap COM (SQLDMO). Here are some options:
http://www.karaszi.com/SQLServer/inf...ate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Amos J. Soma" <amos_j_soma@.yahoo.com> wrote in message
news:18qdnYXLU7Y_BqXeRVn-gA@.buckeye-express.com...
> All,
> I need to be able to create a script of a database from within a .NET application. Does anyone
> know of any code (T-SQL, C# etc.) available to do this?
> Thanks.
>
|||I used to use SQL-DMO, but when I built my scripts, the dependancies
were off. I found the following article, which takes this into
account:
http://www.sqlservercentral.com/colu...tionscript.asp
It's very fast as well.
Stu

Scripting "Create to File" for multiple objects

What would be the best way to create a routine that performs the "Script <object> as Create To File" for multiple objects in my database?

I would like a separate file for each object (table, view, or stored procedure).

Could someone point me in the right direction?

Thanks,

Paul
Seperate File - Use SMO programming using .NET and create it.

Scripted CREATE INDEX

I have SQL Server 2005 x64. I found in the studio I can generate all
sorts of scripts, including the creation of indexes.
I works, however the script contains the CREATE TABLE statements as
well. I want the scripts to recreate the scripts on another filegroup
(testing a theory from previous post).
Am I missing a switch in the wizard to exclude CREATE TABLE?
I did create the script, but then I manually deleted all the CREATE
TABLE statements, which was a pain.
Thanks
RobOn Mar 12, 11:55 am, "rcamarda" <robert.a.cama...@.gmail.comwrote:

Quote:

Originally Posted by

I haveSQLServer 2005 x64. I found in the studio I cangenerateall
sorts of scripts, including the creation of indexes.
I works, however thescriptcontains the CREATE TABLE statements as
well. I want the scripts to recreate the scripts on another filegroup
(testing a theory from previous post).
Am I missing a switch in the wizard to exclude CREATE TABLE?
I did create thescript, but then I manually deleted all the CREATE
TABLE statements, which was a pain.
Thanks
Rob

If you want to fine-tune what gets scripted, you might want to check
out this free app I wrote using SMO:

http://www.elsasoft.org/tools.htm
it will generate scripts for all objects in any 2000 or 2005 db. You
could easily modify it to script only indexes. source code is
available. implemented in C#.

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.

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 to transfer data

I need to have .sql script for moving data from one database to another. I managed to create a script that creates the tables and also have a.sql for all the procedures, but it appears the wizard doesn't spit out anything for moving that data...what is the easiest way to populate these tables once I run the tables.sql script generated by the wizard.have you tried using the DTS Import/Export wizard ?

hth|||that would seem the logical thing to do, however I need to generate a stored proc that moves a table and its data from one Database to another. So that it is run without having to use DTS. Any ideas?|||Depending on how much data it is, you can use cursors to build SQL scripts that insert all the data. It works fine when you have a limited amount of data such as a few master records that are needed to get it up and running. If you're doing something on the scale of trying to restore an existing database, it's not an option.

Basically, use cursors to loop through the tables/data you need and dynamically build a string that will be a bunch of insert statements. Again, only good for small amounts of data.

script to take backup of a database

Hi,

I am trying to create a script that takes backup of a sql database. The script is failing with message "backup failed with message..."

Option Explicit

Dim server

Dim backup

Set server = CreateObject("Microsoft.SQLServer.Management.SMO.Server")

Set backup = CreateObject("Microsoft.SQLServer.Management.SMO.Backup")

backup.Action = 0

backup.Database = "test_old"

backup.SqlBackup(server)

I think this is failing because i did not specify the backup file name. Can u please let me know how can i specify it?

Also it would be great if you can point me to some relevant documentation which shows how to use SMO with vb script.

Regards

Aseem Bansal

You can't use SMO with VBScript. (Well, you can, but you can't use it to communicate with anything but the default server on your local machine.)

Books Online has good examples how to backup a database.

|||

I do not have one in vbscript, but here is one in C#...

using System;

using System.Data;

using System.Collections;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

class Program

{

static void Main(string[] args)

{

BackupDeviceItem bdi =

new BackupDeviceItem("AdventureWorks.bak", DeviceType.File);

Backup bu = new Backup( );

bu.Database = "AdventureWorks";

bu.Devices.Add(bdi);

bu.Initialize = true;

// add percent complete and complete event handlers

bu.PercentComplete +=

new PercentCompleteEventHandler(Backup_PercentComplete);

bu.Complete +=new ServerMessageEventHandler(Backup_Complete);

Server server = new Server("localhost");

bu.SqlBackup(server);

Console.WriteLine(Environment.NewLine + "Press any key to continue.");

Console.ReadKey( );

}

protected static void Backup_PercentComplete(

object sender, PercentCompleteEventArgs e)

{

Console.WriteLine(e.Percent + "% processed.");

}

protected static void Backup_Complete(object sender, ServerMessageEventArgs e)

{

Console.WriteLine(Environment.NewLine + e.ToString( ));

}

}

|||

Can you do this in a windows form application please..? Seems like it doesn't work...I don't know.. If can please let me know..thank you...darshaka..

---

---

script to take backup of a database

Hi,

I am trying to create a script that takes backup of a sql database. The script is failing with message "backup failed with message..."

Option Explicit

Dim server

Dim backup

Set server = CreateObject("Microsoft.SQLServer.Management.SMO.Server")

Set backup = CreateObject("Microsoft.SQLServer.Management.SMO.Backup")

backup.Action = 0

backup.Database = "test_old"

backup.SqlBackup(server)

I think this is failing because i did not specify the backup file name. Can u please let me know how can i specify it?

Also it would be great if you can point me to some relevant documentation which shows how to use SMO with vb script.

Regards

Aseem Bansal

You can't use SMO with VBScript. (Well, you can, but you can't use it to communicate with anything but the default server on your local machine.)

Books Online has good examples how to backup a database.

|||

I do not have one in vbscript, but here is one in C#...

using System;

using System.Data;

using System.Collections;

using Microsoft.SqlServer.Management.Common;

using Microsoft.SqlServer.Management.Smo;

class Program

{

static void Main(string[] args)

{

BackupDeviceItem bdi =

new BackupDeviceItem("AdventureWorks.bak", DeviceType.File);

Backup bu = new Backup( );

bu.Database = "AdventureWorks";

bu.Devices.Add(bdi);

bu.Initialize = true;

// add percent complete and complete event handlers

bu.PercentComplete +=

new PercentCompleteEventHandler(Backup_PercentComplete);

bu.Complete +=new ServerMessageEventHandler(Backup_Complete);

Server server = new Server("localhost");

bu.SqlBackup(server);

Console.WriteLine(Environment.NewLine + "Press any key to continue.");

Console.ReadKey( );

}

protected static void Backup_PercentComplete(

object sender, PercentCompleteEventArgs e)

{

Console.WriteLine(e.Percent + "% processed.");

}

protected static void Backup_Complete(object sender, ServerMessageEventArgs e)

{

Console.WriteLine(Environment.NewLine + e.ToString( ));

}

}

|||

Can you do this in a windows form application please..? Seems like it doesn't work...I don't know.. If can please let me know..thank you...darshaka..

---

---

sql

Script to generate INSERT statements on table

I'd I have a problem I'd like to post CREATE TABLE and INSERT statements
that will create my table and insert data into the table.

I can use the scripting feature in Enterprise Manager to generate CREATE
TABLE scripts.

Is there a script I can run that will generate INSERT statements so I can
include sample data.

ThanksYou can download a free T-SQL script to do this:

http://vyaskn.tripod.com/code.htm#inserts

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Terri" <Terri@.spamaway.com> wrote in message
news:c10ufs$v54$1@.reader2.nmix.net...
> I'd I have a problem I'd like to post CREATE TABLE and INSERT statements
> that will create my table and insert data into the table.
> I can use the scripting feature in Enterprise Manager to generate CREATE
> TABLE scripts.
> Is there a script I can run that will generate INSERT statements so I can
> include sample data.
> Thanks

script to create replication

Is it possible to create distribution, replication... from script? I would
like to do this on a new sql server. Is there a macro to record the step I
do and I can bring that to production server and run it? Thanks.
The easiest method is to create the distributor and publications using EM
then get EM to script it out. Edit the resulting script with the new
computername and run it there.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK. I know how to create distributor and publications using EM but I don't
know how to use EM to script it out. Please advice. Thanks.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23EmyenJFHA.3336@.TK2MSFTNGP10.phx.gbl...
> The easiest method is to create the distributor and publications using EM
> then get EM to script it out. Edit the resulting script with the new
> computername and run it there.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||You can right-click the replication folder (or an individual publication)
and select 'generate sql script'.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql

Script to create and load a database

Dear all,
I am very new to SQL Server but must say I find it and the GUI tools
very good.
I am now in a position where I need to supply a client with a script
file to create a database, create tables within it and then load the
tables.
I have a script that will create the tables and also have exported the
contents to comma separated files.
If someone could supply a sample set of script commands that would:
create a standard database
creat a user in it called "foo" and make this user the db owner
create tables owned by foo
load the data from an export file
I would be very grateful.
Also, how would I run such a script.
Thanks in advance
Ed
PS I am willing to learn - if there is a page or manual where I can
find this out myself tell me to RTFM ;-) (But please let me know where
it is!)> If someone could supply a sample set of script commands that would: (...)
> PS I am willing to learn - if there is a page or manual where I can
> find this out myself tell me to RTFM ;-) (But please let me know where
> it is!)
See SQL Server manual - "SQL Books Online" \Microsoft SQL
Server\80\Tools\BOOKS\SQL80.col
Online MSDN resources on SQL2k -
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/sqlserver2000.asp
> Also, how would I run such a script.
In SQL Query Analyzer
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.