Scripting component.

Hi Guys,

I need to generate a file using script component. I'm taking some data from an oledb source and then i would need to modify the fields, generate new fields and then generate the flat file.

I don't have any problems with this but the file i need to replicate would need to have a head record and a trail record which doesn't need to be duplicated.

Now when i generate the file and put anything in "Public Overrides Sub Input0_ProcessInputRow" it will work but it will generate the header record again and again as well.

Any quick help would be really appreciated.

TA

Gemma

Gemma,

Any reason for using the script component to create the file? Can you just use the script to populate the pipeline and use a flat file destination for the file? If so, I recently described how you would go about creating a flat file with header and trailing records.. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2168163&SiteID=1

|||

Hi Eric,

I'm just combinging the fields, padding the fields with zero and doing some cleaning of the data as well.

Is there any other way? I've checked your post but I don't know how to add something from the database in the header.

Yes you can enter any expressions but i need the value from the table.

Can you help me do that?

TA
Gemma

|||

Add a boolean flag at the class level and default it to True, Then in the ProcessInput check the flag. If it is True, write the header line and change the flag to false.

There's an example of that approach in the script I posted here - http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx. The example is not what you were asking about, but the script illustrates the technique.

Scripting "INSERT"s in an SQL database?

Hi there,

Getting ready for deployment and I would like to be able to run an install
script that will not just generate the schema, but fill in some of the
tables with default data. Is there a tool out there that will scan user
tables in an SQL database and generate the script required in order to
create a database with this default data?

Thanks

Robin"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c97t42$rmv$1$8300dec7@.news.demon.co.uk...
> Hi there,
> Getting ready for deployment and I would like to be able to run an install
> script that will not just generate the schema, but fill in some of the
> tables with default data. Is there a tool out there that will scan user
> tables in an SQL database and generate the script required in order to
> create a database with this default data?
> Thanks
>
> Robin

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

If you have a lot of default data, it might be more efficient to have the
data in flat files and load it with bcp.exe or BULK INSERT.

Simon|||Hi

Another alternative would be to ship a default data and log file that is
already populated. For upgrades then a similar container but you would need
to use a tool such as those produced by www.red-gate.com sqlcompare or
www.innovartis.co.uk/Home.aspx dbghost or your home grown application.

John

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:c97t42$rmv$1$8300dec7@.news.demon.co.uk...
> Hi there,
> Getting ready for deployment and I would like to be able to run an install
> script that will not just generate the schema, but fill in some of the
> tables with default data. Is there a tool out there that will scan user
> tables in an SQL database and generate the script required in order to
> create a database with this default data?
> Thanks
>
> Robinsql

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 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 "Generate Scripts"

Ok, guys this is a weird question.

my boss wants script the database objects in a daily basis.

he is too lazy to do the "tasks -> generate scripts -> select objects -> and click finish."

Is there a way to automate this task?

I appreciate your help.

Scriptio is a good way to learn this; http://weblogs.sqlteam.com/billg/archive/2006/06/13/10208.aspx

Script Table, Index, Stored Procedures from Table list

I have a list of approximately 2000 objects that need to create a sql script
to generate these objects. The script will be applied to another datbase to
generate these objects.
The 2000 objects name have been loaded into a Table A.
Please help put together a program that will read the object names from
Table A and generate a sql script for objects.
Thank You,Option#1
Try from Enterprise Manager (Tools -> Generate SQL Scripts)
Option#2
Try Import\Export utility from Enterprise Manager
Thanks,
RK
"Joe K." wrote:

> I have a list of approximately 2000 objects that need to create a sql scri
pt
> to generate these objects. The script will be applied to another datbase
to
> generate these objects.
> The 2000 objects name have been loaded into a Table A.
> Please help put together a program that will read the object names from
> Table A and generate a sql script for objects.
> Thank You,
>|||I would like to way read from a table to "Generate SQL Scripts".
I would like to automate the "Generate SQL Scripts" procedure.
Thanks,
"Ram Kumar Koditala" wrote:
> Option#1
> Try from Enterprise Manager (Tools -> Generate SQL Scripts)
> Option#2
> Try Import\Export utility from Enterprise Manager
> Thanks,
> RK
> "Joe K." wrote:
>|||You'll have to use SQL DMO to do this. See if the following links help:
http://www.karaszi.com/SQLServer/in...rate_script.asp
http://www.databasejournal.com/feat...cle.php/1480901
Anith

Script out "Trigger rules"

When I choose the tables, generatel SQL script. it insist me to generate the
"create table" script also.
Any Simple method which i can script trigger rule (for all tables) only ?
ThxIf I understand your question...
In the Enterprise manager, goto the formatting tab and uncheck Generate Crea
te and Drop for the Objects.
Then on the Options tab check Triggers.
That should gen just the Trigger scripts.
Matt Barbour
Technology Specialist – Communications Sector.
mbarbour@.microsoft.com
W: 469-775-6206
C: 972-746-1278
--Original Message--
From: Agnes
Posted At: Friday, October 28, 2005 10:32 AM
Posted To: microsoft.public.sqlserver.programming
Conversation: Script out "Trigger rules"
Subject: Script out "Trigger rules"
When I choose the tables, generatel SQL script. it insist me to generate the
"create table" script also.
Any Simple method which i can script trigger rule (for all tables) only ?
Thx|||Yes, I try that,It will generate "CREATE TABLE...." SCRIPT together ,
IF I unclick "create table script", and tick "Trigger script" only . Nothing
is generated.
"Matt Barbour" <mestolphies@.hotmail.com> glsD:uTc8cd92FHA.3188@.TK2MSFTNGP12.phx.g
bl...
> If I understand your question...
> In the Enterprise manager, goto the formatting tab and uncheck Generate
> Create and Drop for the Objects.
> Then on the Options tab check Triggers.
> That should gen just the Trigger scripts.
> Matt Barbour
> Technology Specialist 'Communications Sector.
> mbarbour@.microsoft.com
> W: 469-775-6206
> C: 972-746-1278
> --Original Message--
> From: Agnes
> Posted At: Friday, October 28, 2005 10:32 AM
> Posted To: microsoft.public.sqlserver.programming
> Conversation: Script out "Trigger rules"
> Subject: Script out "Trigger rules"
>
> When I choose the tables, generatel SQL script. it insist me to generate
> the
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx|||Interesting.. never noticed that before.
Is this something you are going to do more then once?
If not, just do the Create flag + the Trigger flag and prune off all the Cre
ate table's, they are up front prior to the first trigger command.
--Original Message--
From: Agnes
Posted At: Friday, October 28, 2005 10:57 AM
Posted To: microsoft.public.sqlserver.programming
Conversation: Script out "Trigger rules"
Subject: Re: Script out "Trigger rules"
Yes, I try that,It will generate "CREATE TABLE...." SCRIPT together ,
IF I unclick "create table script", and tick "Trigger script" only . Nothing
is generated.
"Matt Barbour" <mestolphies@.hotmail.com> glsD:uTc8cd92FHA.3188@.TK2MSFTNGP12.phx.gbl...[colo
r=darkred]
> If I understand your question...
> In the Enterprise manager, goto the formatting tab and uncheck Generate
> Create and Drop for the Objects.
> Then on the Options tab check Triggers.
> That should gen just the Trigger scripts.
> - Matt.
> --Original Message--
> From: Agnes
> Posted At: Friday, October 28, 2005 10:32 AM
> Posted To: microsoft.public.sqlserver.programming
> Conversation: Script out "Trigger rules"
> Subject: Script out "Trigger rules"
>
> When I choose the tables, generatel SQL script. it insist me to generate
> the
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx[/color]|||Are you perhaps still trying to temporarily disable triggers? Read the
answers to your previous posts.
ML|||Agnes,
You may wish to try to script just the trigger with
sp_helptext tr_mytrigger
HTH
JeffP...
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:%23royOT92FHA.2800@.TK2MSFTNGP10.phx.gbl...
> When I choose the tables, generatel SQL script. it insist me to generate t
he
> "create table" script also.
> Any Simple method which i can script trigger rule (for all tables) only ?
> Thx
>
>

Script generation for Objects - Management Studio

How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.

I want to have separate file for each of the objects.

In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.

Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.

Clarity Consulting (http://www.claritycon.com)

|||

Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.

It applies to other objects like procs, funcs, triggers, constraints, FKs etc

|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.

Script generation for Objects - Management Studio

How do I generate a single script for each object - table, view, index, trigger, sp, function etc.... in SQL Server. The script that is generated from SQL Server wizards is in a single file.

I want to have separate file for each of the objects.

In EM, you right-click on the object, select 'all tasks', then 'Generate SQL Script'. I do not have access to Management Studio right now, so this may or may not be much help.

Do you have access to Enterprise Manager? You can run SQL Server 2000 and 2005 side by side, so it may be worthwhile installing if you cannot do this in MS.

Clarity Consulting (http://www.claritycon.com)

|||

Well we have the same feature in SQL 2005 but I need one individual file for each object. I do not want the script of say all tables in file. I want this in different files with file name as table name that is getting scripted.

It applies to other objects like procs, funcs, triggers, constraints, FKs etc

|||You might look at Scriptio. I use it to create .sql files off of my db schema. On the 2nd tab I select ONE FILE PER OBJECT. www.sqlteam.com. The only problem I have found is that existing sql files (we re-exporting) can't be read-only.|||Currently, the Generate Script Wizard can only create a single file. We plan to implement file-per-object functionality in SP2.

Script Generation

In SQL 2000 Enterprise Manager, you can create SQL scripts for
procedures by right clicking and choosing generate SQL script. It sets
some database defaults and turns some things on and off. Is there a
way to modify the things that are created to return more data? Is it
in a template where i can add some code. I want to include 1 line in
each script it generates but cant find where to do it. Can it be done?
How?
Thanks so much in adavnce!
-dL
Hi DL,
You can not customize that. Instead you can use SQL DMO to generate customized scripts.
See the below link:-
http://www.karaszi.com/sqlserver/inf...ate_script.asp
Thanks
Hari
SQL Server MVP
____________________________________
Sweet Daddy Coconut Wrote:
In SQL 2000 Enterprise Manager, you can create SQL scripts for
procedures by right clicking and choosing generate SQL script. It sets
some database defaults and turns some things on and off. Is there a
way to modify the things that are created to return more data? Is it
in a template where i can add some code. I want to include 1 line in
each script it generates but cant find where to do it. Can it be done?
How?
Thanks so much in adavnce!
-dL
Sent via SreeSharp NewsReader http://www.SreeSharp.com

script

Hello

I want to create sql script for my database. I know about “Generate SQL Server Scripts Wizard” but it just give me script for structure of my database and I want all records of my tables too.

And I’ve tried “Script table as / Insert to“via right click on my table too and it doesn’t give me a script to insert all records!

How can I create script to insert all records of a table to another table with same column properties?does exist any things to do it for me?

Thanks in advance.

Best to just create a database backup and create a TSQL script to restore it.

Or, you need to bulk copy the data out into a file and use bcp.exe or bulk insert to re-populate the table.

|||

Hi,

In order to generate insert statments for a table, you can use a very well written script that can be found at http://vyaskn.tripod.com/code.htm#inserts

Here you will find both the versions of the script (for SQL2000 and SQL2005) and its very easy to use.

Regards,
Neeraj

SCPTXFR.EXE-- Problem with Drop procedure

hi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.

SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:\HSE0607\SCHEMA.sql /q/r/T

Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.

Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this?
Regards,
Thanks.
Gurpreet S. Gill

hey nobody help me here, but i find the solution at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73290