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

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 generator anyone?

Dear subscribers,
Does anyone happen to be aware of a tested script generator that would
create a script that would contain statements in the right order for
dropping indexes, keys, and DRI of all tables in a database?
Many thanks.You can use the EM Generate scripts utility. If you are concerned about
dependencies, you can use CREATE SCHEMA DDL to generate the script without
worrying about the order in which the dependant objects are created (with
the exception for views dependent on views). Pl. refer to SQL Server Books
Online for more details on CREATE SCHEMA.
--
- Anith
( Please reply to newsgroups only )|||Thanks Anith,
From BOL: CREATE SCHEMA provides a way to create tables and views and to
grant permissions for objects with a single statement. So if I understood it
correctly CREATE SCHEMA is for creating objects, whereas my initial concern
was about dropping objects in the right order, not their creation.
Regarding EM - it seems that it only generates Transact-SQL statements to
create indexes/keys/dri that currently exist for any selected tables, not to
drop them.
Or have I misread that in BOL?
Many thanks.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OpMDmUnkDHA.1708@.TK2MSFTNGP12.phx.gbl...
> You can use the EM Generate scripts utility. If you are concerned about
> dependencies, you can use CREATE SCHEMA DDL to generate the script without
> worrying about the order in which the dependant objects are created (with
> the exception for views dependent on views). Pl. refer to SQL Server Books
> Online for more details on CREATE SCHEMA.
> --
> - Anith
> ( Please reply to newsgroups only )
>|||On Wed, 15 Oct 2003 11:41:48 +0300, "Oskars Salnins"
<osalnins@.inbox.lv> wrote:
>Thanks Anith,
>From BOL: CREATE SCHEMA provides a way to create tables and views and to
>grant permissions for objects with a single statement. So if I understood it
>correctly CREATE SCHEMA is for creating objects, whereas my initial concern
>was about dropping objects in the right order, not their creation.
>Regarding EM - it seems that it only generates Transact-SQL statements to
>create indexes/keys/dri that currently exist for any selected tables, not to
>drop them.
>Or have I misread that in BOL?
Try it and see.
hint: the Formatting tab item "Generate the drop <object> command for
each object" may be relevant!
J.

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

Script Component - Timeout Expired

Hello,

I have a script component inside one of my packages that performs a calculation on the fields and then does several insert statements on SQL 2005 database for each row inputed.

During the execution, when the data is a bit large ~ 3000 records input that produce around 20,000 insert statement, a "Timeout Expired" message pops up several times from the script component. Although the data enters in the database, but I have to manually click "ok" on each of the messages during run-time.
Any idea as to why I am getting this error or how to fix it?
Below is the code of the script component that does the insert.

Thanks for your help.

Grace

Part of Script Component Code:

Try

connMgr = Me.Connections.Connection

conn = CType(connMgr.AcquireConnection(Nothing), SqlConnection)

cmd.Connection = conn

cmd.CommandText = "INSERT statement .... "

If conn.State = ConnectionState.Closed Then conn.Open()

cmd.ExecuteNonQuery()

Catch ex As Exception

MsgBox(ex.Message)

End Try

Why are you doing this inside a script component? This is incredibly inefficient as it issues an INSERT for each of the rows. If you used the data-flow OLE DB Destination it would do set based inserts.

-Jamie

|||

Hi Jamie,

For each row I have an amount, inception date and expiry date. I am using the script component because I have to divide the amount porportionally over each month between the inception - expiry dates and then issue an insert statement for each month with its amount. What I posted in my earlier question was the part that is doing the connection and insert because I thought there might be something wrong in it. So one row triggers multiple inserts depending on dates. I don't know how this is possible using OLE DB Destination.

Thank You,

Grace

|||

How big is your cmd.CommandTimeout? Have you tried to set it to 0 to see whether that helps?

thanks

wenyang

|||

Yes I tried to set it to 0 but still the same error.

The full error message I get: "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were in use and max pool size was reached."

I tried to use conn.clearPool (conn) after the execution but get the message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I tried to close the connection after the ExecuteNonQuery, it seems it worked, no more timeout expired. However, sometimes i get error on the DataFlow Task but when I run it another time, it works fine. I am still testing this case to know what is happening.

Thanks,

Grace

|||

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

|||

vaishali.mspp wrote:

Hi ,

I'm getting the same error in my script component as mentioned in the first post.

the error is "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding"

I tried all the ways mentioned in the post.

can anybody help me in this regard.

Thanks,

vaishu.

Are you trying to insert into a table that is also the target of an OLE DB Destination? If so, your timeout is probably due to a lock.

For the record, I think the OP would be better served sending the new rows to another output of the script and pushing them into an OLE DB Destination, rather than trying to do inserts in the script. It did not sound as if the original scenario was inserting to different tables, which is really the only reason (off the top of my head) to do it in the script.

SCRIPT

Hi
I am very familiar with insert statements, but how does a
value get passed for a user name and password from either
a web or windows application i.e
A simple insert statement would be
Insert into Table1
(username,password)values('bob','password')
but if you do not know the values of the username and
password are going to be then what would the query look
like.
So if I plan to make my password banana when I log onto
the application how would the script look like then,
would you leave the '' where banana would be as blank.
Insert into Table1
(username,password)values('bob','')
Thanks
Bob
BOB wrote:
> Hi
> I am very familiar with insert statements, but how does a
> value get passed for a user name and password from either
> a web or windows application i.e
> A simple insert statement would be
> Insert into Table1
> (username,password)values('bob','password')
> but if you do not know the values of the username and
> password are going to be then what would the query look
> like.
> So if I plan to make my password banana when I log onto
> the application how would the script look like then,
> would you leave the '' where banana would be as blank.
> Insert into Table1
> (username,password)values('bob','')
>
> Thanks
> Bob
First, we might enquire why you are storing user names and passwords in
a table. Can you rely on SQL Server's security mechanisms instead?
In general, though, is you have an attribute (column) in a table that
may contain unknown data, you allow NULL values. And you insert a NULL
or leave the column out of the insert operation altogether.
For web-based security, it's best to use Windows Authentication. Then no
passwords need to be sent at all. Some web servers may use hard-coded
connection strings to the server, but it's better to use Windows
Authentication.
Can you provide more detail about what you're trying to accomplish?
David Gugick
Imceda Software
www.imceda.com

scope statement in the session scope?

How to write [scope] statements in the session cope,not in the cube-mdx-script?

In the book called "Microsoft Analysis Services 2005" written by developers from microsft's ssas2005 team, there is a senctence:

"because scope statements and assignment operators work ony in the context of a cube or session..."

How to do that ?

Thanx.

You can't do a scope statement from Management Studio because Management Studio doesn't set the current cube on the connection string.

If you look at the MDX Script Performance tool (http://www.codeplex.com/mdxscriptperf) that Chris Webb wrote you will see an example of running a scope statement within a session. It specifies the cube in the connection string. And it replays the calc script one statement at a time which is an example of using "scope" in the context of a session.

Hope that helps.

|||

Thanx for your answer,which is right absolutely.

But I doubt about the Performance Analyzer according the inner working rule , which can be found in the book called "Microsoft Analysis Services 2005" that is written by developers from SSAS2005.