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.

Script to identify line count in an procedure

Hi Guys,
Do any one of you have any scripts which return the procedure name and
number of lines for all procedures in a database.
Say I have a server with 1000 stored procedure,I need a script which return:
Procedure name Number of lines
-- --
Thanks in advance
HariI think this should do it:
SELECT O.name, SUM(LEN(text)-LEN(REPLACE(text,CHAR(13),'')))
FROM syscomments AS C
JOIN sysobjects AS O
ON C.id = O.id
WHERE O.xtype='P'
GROUP BY O.id, O.name
--
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot.
Regards
Hari
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:iMidnTtJieiJzlqiRVn-gQ@.giganews.com...
> I think this should do it:
> SELECT O.name, SUM(LEN(text)-LEN(REPLACE(text,CHAR(13),'')))
> FROM syscomments AS C
> JOIN sysobjects AS O
> ON C.id = O.id
> WHERE O.xtype='P'
> GROUP BY O.id, O.name
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
>

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 runs fine second time (was "Need Help....")

Hi Guys and Gals...

I have a script that runs every night to pull some data from Delphi. For some strange reason the past month it has been failing on the inital running of the script but when I close off all of the error boxes and hit the script again it runs just fine... Please find enclosed the script . Can anyone tell me whe it is wrong... thanks in advanceWhat is the very first error message that you get?|||it is a follows:

Line:67
Char:1
Error: ODBC SQL Server Driver Timeout error
Code:80004005
Source: OLE DB provider for IDBC Drivers

The second and continuing errors are:

Line:49
Char:1
Permission Denied
error:800a0046
VB Script runtime error

This program runs at midnight. All the errors appear but yet when I close all of them and hit the script again it runs.....

Any help|||Is there any other activity going on with the database server at midnight?
Nightly maintenance, for instance?|||No other items are running that database around or after midnight.. This is a strange as hell...

Runs fine during the day but when it changes to another day errors.....|||What if you set it to run at 12:05 am, so that its execution does not span midnight?
Honestly, I haven't looked at the details of your dynamic SQL. You should really consider creating this as a stored procedure and dropping the cursor. That would probably effectively solve the problem on its own.|||I would expect that this is being caused by SQL needing to read in all the data you are after from disk. Once the first query times out, the second query gets the advantage of having all the data pre-populated in memory. Simple fix, just up your command timeout value. Still, you should look into the query(s) to see if improvements can be made. Especially if the query is taking over 30 seconds as it is.|||My programmer just left yesterday for holidays...

Can anyone give me the lines needed to add the "command timeout " into my script.... And where it would be best to place it...

Total anal when it comes to VB... Great with DOS though...

many thanks

scouts, badges, and groups of badges (was "Please help")

Hi Guys,:rolleyes:

I am designing a database for scouts and i am stuck on the ERD(relationships design)...The situation is this: I have an entity 'scout' and entity 'badge'.
A scout can have many badges as obvious. However there are different classification(groups) of badges(main badges, activity badges,Staged Activity badges...). Each of these group of badges consists of different types of badges and finally a badge type has required tasks to accomplish to qualify for it!!

Is there anyone with a suggestion how to model these entities please??

migarich@.yahoo.frWhen you post your homework, please either provide the URL where the assignment was given, or at least scan in the handout you got in class that describes the assignment. It makes life so much simpler if we can do your homework for you in one pass, instead of completing what you've posted so far, only to have you realize that there is another part you need for full credit!

-PatP|||This is not actually an assignment but a system i am designing as a voluntary action for my local scout troup...and there is no specifications as such...the descriptions i have made are from my head and not some handouts as you may think...If there is any part of the description you do not understand i would be grateful to hear...as i said it is only part of the problem that i am having issues with.

Regards|||Ok, I see three detail level tables: Scouts, Requirements, Badges.

I see three linking (many to many) tables, Scout to requirement (showing that the scout has met a requirement), Badge to requirement (showing that the requirement is a pre-requisite for the badge), and Scout to badge (showing that the scout has earned the badge).

I see one "decorative" table, showing that a badge is of a given badge type.

-PatP|||Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Hi Blindman,

This definetely makes sense...I was thinking more of the supertypes and subtypes...Would you say it is feasible?...Thanks

Here is a sample model using natural keys. You can substitue surrogate keys if you'd like:
Table: Scouts
(ScoutName, [pkey]
etc...)

Table: BadgeClasses
(BadgeClass, [pkey]
etc...)

Table: Badges
(Badge, [pkey]
BadgeClass, [fkey to BadgeClasses]
etc...)

Table: ScoutBadges [implements many-to-many relationship between scouts and badges]
(ScoutName, [fkey to Scouts]
Badge, [fkey to Badges]
etc...)|||Thank you very much...It is now clearer...How about replacing the requirement table by 'Scout_badge' which has 2 key fields as from scout and badge??

Thanks for you help|||Table: Requirements
(Requirement, [pkey]
Badge, [fkey to badges]
Description,
etc...)

Table: ScoutRequirementsCompleted
(ScoutName, [fkey to scouts]
Requirement, [fkey to Requirements]
DateCompleted,
etc...)

When a scout has completed all the requirements for a badge, grant him the badge and add a record to ScoutBadges|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?|||But a Scout can only earn a badge once, and if I remember correctly, aren;t some badges prerequsities to other badges?A UNIQUE CONSTRAINT will take care of a scout only earning a badge once. Making the possesion of a badge a requirement for another badge takes care of the prerequisite.

-PatP|||Hi again,

there are various tasks/activities that for a badge requirement...
Thanks again|||Hi guys, I have a table called camp, Scout and Medical_File...i need to include the details of a scout medical file when the scout is booking a camp so we know what we need to take care of in different activities...does anyone know how to model this. The medical File is not currently linked to the camp at the moment!!
Thanks|||If there is only one medical file for each scout, then consider simplifying your schema by including the medical information in the scouts table.|||That is actually a good idea...i did that before and decided to move it to a separate entity as the enity scout seemed of a big table...

Yeah i have another prob...sometimes scouts go to a camp by patrol but also individually depending on who organised the camp...at the moment i had scout entity linked to camp via payment(camp payment)...do u think i should link patrol as well?

thanks|||Is a scout in only one Patrol?

You should probably store "snapshot" information about the scout and the patrol the belong to in your camp/scout attendance table, as some of the data may change over time (they may join a different patrol), but you would still want to know that they were in the original patrol when they attended the camp.