access datasource in script component

I'm trying to access a group of rows in a data source in my script component (transformation). Currently im using the connection manager in the script component, acquire the connection, and issue a query command to get the data. This will happen on every rows being pass to the script component from the data source. Is there a better way to access the a group of rows from the data source?

It depends what you want to do. Perhaps you could populate an in-memory store of the data to prevent the round-trip each time.

Are you sure that cannot be achieved with LOOKUP component?

-Jamie

Access / SQL Query issue

I will explain (or at least try to) first and then give an example
after.

I need to append a number of rows from several tables into one master
table. Unfortunately there are certain columns (which are UNIQUE
columns in the master table) in the source tables that are repeated in
the same table or across the source tables.

Example:
Source 1 Source 2

[SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
MSFT STOCK AAPL STOCK
AA STOCK MER OPTION
MER OPTION
MSFT OPTION

Master

[SECURITY] -- Unique Key
[SECURITY_TYPE]

As you can see in the example, MSFT is listed twice in Source 1 -- as
a stock and an option. MER is listed as an option in both Source 1 and
Source 2.

My first solution (when I wrote it in Access) was to read in a source
row, check if it existed in the master table, and then add it if it
did not. This of course ran very slow. To fix that I added all the
source rows to a temp table and then appended that temp table to the
master. Since I was able to turn off warnings with the SetWarnings
action in access any row that caused a duplicate key error got ignored
BUT the query continued to the end.

(Note: DISTINCT on a source table does not work since MSFT-Stock is a
row while MSFT-Option is another.)

I rewrote the query to run in SQL Query Analyzer but cannot seem to
figure out how to turn off errors so the query runs to completion. It
may not even be possible to do so in which case I am hoping for a
solution other than checking each row to see if it exists before I add
it."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0309081059.1817f089@.posting.google.c om...
> I will explain (or at least try to) first and then give an example
> after.
> I need to append a number of rows from several tables into one master
> table. Unfortunately there are certain columns (which are UNIQUE
> columns in the master table) in the source tables that are repeated in
> the same table or across the source tables.
> Example:
> Source 1 Source 2
> [SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
> MSFT STOCK AAPL STOCK
> AA STOCK MER OPTION
> MER OPTION
> MSFT OPTION
> Master
> [SECURITY] -- Unique Key
> [SECURITY_TYPE]
>
> As you can see in the example, MSFT is listed twice in Source 1 -- as
> a stock and an option. MER is listed as an option in both Source 1 and
> Source 2.
> My first solution (when I wrote it in Access) was to read in a source
> row, check if it existed in the master table, and then add it if it
> did not. This of course ran very slow. To fix that I added all the
> source rows to a temp table and then appended that temp table to the
> master. Since I was able to turn off warnings with the SetWarnings
> action in access any row that caused a duplicate key error got ignored
> BUT the query continued to the end.
> (Note: DISTINCT on a source table does not work since MSFT-Stock is a
> row while MSFT-Option is another.)
> I rewrote the query to run in SQL Query Analyzer but cannot seem to
> figure out how to turn off errors so the query runs to completion. It
> may not even be possible to do so in which case I am hoping for a
> solution other than checking each row to see if it exists before I add
> it.

Your example isn't really clear without DDL (CREATE TABLE statements) and
sample data. You seem to indicate that Master.Security is the primary key,
but if so, you could have only one row in Master for MSFT, not two, which is
what I think you want. If my understanding is correct, you probably want
something like this, but without extra details, it's only a guess:

insert into
dbo.Master (Security, Security_Type)
select
Symbol,
Symbol_Type
from
dbo.Source1 s1
where
not exists (select *
from dbo.Master m
where s1.Symbol = m.Security and
s1.Symbol_Type = m.Security_Type)

You can modify the same query to use Source2.

Simon|||Jason,

It's not clear to me what you want, particularly in
the SECURITY_TYPE column of the master table. If the
primary key of that table is SECURITY, then you can't put
MSFT in twice - so what is SECURITY_TYPE? Is it something
different than STOCK or OPTION?

If you want the master table to represent what the source
tables represent, you will need to have (SECURITY,SECURITY_TYPE)
as the primary key (and you won't have the trouble you're having.

If SECURITY_TYPE is something completely different, and you
want MSFT in the table only once, then you can just insert
select SYMBOL, NULL from [Source 1]
union
select STOCK_SYMBOL, NULL from [Source 2]
-- union will eliminate duplicates

and then you can update the SECURITY_TYPE column as needed.

If you want to list MSFT-STOCK and MSFT-OPTION as the values
in the first column of the master table, then insert
select SYMBOL+'_'+SYMBOL_TYPE, NULL -- still don't know what type is
from [Source 1]
union
...

-- Steve Kass
-- Drew University
-- Ref: C3330EF8-EA21-4840-8969-1BC9DCF019B0

Jason wrote:
> I will explain (or at least try to) first and then give an example
> after.
> I need to append a number of rows from several tables into one master
> table. Unfortunately there are certain columns (which are UNIQUE
> columns in the master table) in the source tables that are repeated in
> the same table or across the source tables.
> Example:
> Source 1 Source 2
> [SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
> MSFT STOCK AAPL STOCK
> AA STOCK MER OPTION
> MER OPTION
> MSFT OPTION
> Master
> [SECURITY] -- Unique Key
> [SECURITY_TYPE]
>
> As you can see in the example, MSFT is listed twice in Source 1 -- as
> a stock and an option. MER is listed as an option in both Source 1 and
> Source 2.
> My first solution (when I wrote it in Access) was to read in a source
> row, check if it existed in the master table, and then add it if it
> did not. This of course ran very slow. To fix that I added all the
> source rows to a temp table and then appended that temp table to the
> master. Since I was able to turn off warnings with the SetWarnings
> action in access any row that caused a duplicate key error got ignored
> BUT the query continued to the end.
> (Note: DISTINCT on a source table does not work since MSFT-Stock is a
> row while MSFT-Option is another.)
> I rewrote the query to run in SQL Query Analyzer but cannot seem to
> figure out how to turn off errors so the query runs to completion. It
> may not even be possible to do so in which case I am hoping for a
> solution other than checking each row to see if it exists before I add
> it.|||My bad. When I type this out, SECURITY_TYPE should NOT have been
included in the Master table.

- Jason

Steve Kass <skass@.drew.edu> wrote in message news:<W287b.10380$_26.6571@.newsread2.news.atl.earthlink. net>...
> Jason,
> It's not clear to me what you want, particularly in
> the SECURITY_TYPE column of the master table. If the
> primary key of that table is SECURITY, then you can't put
> MSFT in twice - so what is SECURITY_TYPE? Is it something
> different than STOCK or OPTION?
> If you want the master table to represent what the source
> tables represent, you will need to have (SECURITY,SECURITY_TYPE)
> as the primary key (and you won't have the trouble you're having.
> If SECURITY_TYPE is something completely different, and you
> want MSFT in the table only once, then you can just insert
> select SYMBOL, NULL from [Source 1]
> union
> select STOCK_SYMBOL, NULL from [Source 2]
> -- union will eliminate duplicates
> and then you can update the SECURITY_TYPE column as needed.
> If you want to list MSFT-STOCK and MSFT-OPTION as the values
> in the first column of the master table, then insert
> select SYMBOL+'_'+SYMBOL_TYPE, NULL -- still don't know what type is
> from [Source 1]
> union
> ...
> -- Steve Kass
> -- Drew University
> -- Ref: C3330EF8-EA21-4840-8969-1BC9DCF019B0
> Jason wrote:
> > I will explain (or at least try to) first and then give an example
> > after.
> > I need to append a number of rows from several tables into one master
> > table. Unfortunately there are certain columns (which are UNIQUE
> > columns in the master table) in the source tables that are repeated in
> > the same table or across the source tables.
> > Example:
> > Source 1 Source 2
> > [SYMBOL] [SYMBOL_TYPE] [STOCK_SYMBOL] [TYPE]
> > MSFT STOCK AAPL STOCK
> > AA STOCK MER OPTION
> > MER OPTION
> > MSFT OPTION
> > Master
> > [SECURITY] -- Unique Key
> > [SECURITY_TYPE]
> > As you can see in the example, MSFT is listed twice in Source 1 -- as
> > a stock and an option. MER is listed as an option in both Source 1 and
> > Source 2.
> > My first solution (when I wrote it in Access) was to read in a source
> > row, check if it existed in the master table, and then add it if it
> > did not. This of course ran very slow. To fix that I added all the
> > source rows to a temp table and then appended that temp table to the
> > master. Since I was able to turn off warnings with the SetWarnings
> > action in access any row that caused a duplicate key error got ignored
> > BUT the query continued to the end.
> > (Note: DISTINCT on a source table does not work since MSFT-Stock is a
> > row while MSFT-Option is another.)
> > I rewrote the query to run in SQL Query Analyzer but cannot seem to
> > figure out how to turn off errors so the query runs to completion. It
> > may not even be possible to do so in which case I am hoping for a
> > solution other than checking each row to see if it exists before I add
> > it.

About Security between views and tables

Hi,

I've got a table T1 and a view V1 based on T1's rows (but not all rows).

I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.

then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.

Is there a way of doing this ? (sql 2000).

You can't update a view. A view isn't a table. It's just a select statement.|||

Yes i can. I did it many times, as the select is not too complex, you can do it.

For insance, if you got a table T1 with fields (ID, f1,f2,f3,f4) and a view V1 as select id, f1,f2 from t1, you 'll be allowed to make updates.

this feature works fine with many views i wrote.

|||Well, I'll be...

Have you read through this document to see if you have met the criteria for updateable views? http://msdn2.microsoft.com/en-gb/library/ms187956.aspx|||

thanks for the link. yes my view is updateable, and when there's no grant restrictions, it works fine.

My problem is the following : I'm working on an ms access migration to sql 2000.
Then, i use my view as an attached table in access, an users can modify some values.
But i don't wan basic users have a direct access to the table, only to the view.
That's why i tried to restrict select and update on the table, whereas select and updates are allowed to the view.

But i'm afraid i won't be able to do this...

|||If you have the base table restricted, I'm wondering if the view can supercede that security. I'm thinking not.|||

i'm afraid you're right. so i'll have to manage my own security... :-(

about option Can Grow

Who know when feild some feild have rows more one.Have function to check this?Why do you want to check this?|||I use feild to make table I use bottom border of each feild but have some problem that problem is if some feild can grow more one row the table will not complete feild will overlap Thank you very much for your reply

About my way to insert rows.

I have heard that the most time consuming task into a DB is the when we use
the Insert Statatement,
Is that true?
Ok I have an application that sends from 10 to 500 rows to the DB, those
rows are compared again a fixed value and then they are inserted to the DB i
f
the comparison is ok.
I would like to do all that inside the DB (a SP) but I don’t know how, so
I
created a WebService to do that, my point is that I’m not inserting row by
row instead I hold the rows in a Dataset (memory) and later I make one big
insert to the DB.
My question is should I leave that code like this or should go to the DB in
order to insert row by row to gain some performance?
Can you think a better way to do that, because that is the heart of my app
and I want to minimize the time it takes.
Thnks.
Kenny M.Hi Kenny
Please let me know what kind of comparision is that u are doing before
inserting rows into your database.
Just try avoiding bringing data to the front-end if its only a validation
part.
You can write a query as:
INSERT INTO <TABLE>
SELECT <Columns>
FROM <another table>
<WHERE Condition>
alternatively you can also write INSTEAD OF Triggers, if you want to check
the records before they are inserted
hope this will solve the problem
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Kenny M." wrote:

> I have heard that the most time consuming task into a DB is the when we us
e
> the Insert Statatement,
> Is that true?
> Ok I have an application that sends from 10 to 500 rows to the DB, those
> rows are compared again a fixed value and then they are inserted to the DB
if
> the comparison is ok.
> I would like to do all that inside the DB (a SP) but I don’t know how, s
o I
> created a WebService to do that, my point is that I’m not inserting row
by
> row instead I hold the rows in a Dataset (memory) and later I make one big
> insert to the DB.
> My question is should I leave that code like this or should go to the DB i
n
> order to insert row by row to gain some performance?
> Can you think a better way to do that, because that is the heart of my app
> and I want to minimize the time it takes.
> Thnks.
> --
> Kenny M.|||Hi, Well
In order to understand it better, the table Category is like this
CategoryID Number SetAmount
1 0 1000
1 ... ...
1 999 2000 For each Category there
are 1000 numbers
2 0 200
2 ... ...
2 999 3000
.. ... ...
n 0 - 999 ...
e.g
I send 100 rows to the method . The rows are like this
ID,DATETIME, NUMBER,AMOUNT,CATEGORYID
For each row I have to go to the categorytable look for the CATEGORYID then
look for the NUMBER and ask if the AMOUNT is > SetAmount. If so I have to
Insert the Whole Record into another table.
At this time I'm doing that process inside a WService.
I'm not inserting row by row, instead I'm saving the valid row in a DataSet
to later Insert them to a Table.(Using Fill method)
Any sug.
thks.
Kenny M.
"Chandra" wrote:
> Hi Kenny
> Please let me know what kind of comparision is that u are doing before
> inserting rows into your database.
> Just try avoiding bringing data to the front-end if its only a validation
> part.
>
> You can write a query as:
> INSERT INTO <TABLE>
> SELECT <Columns>
> FROM <another table>
> <WHERE Condition>
> alternatively you can also write INSTEAD OF Triggers, if you want to check
> the records before they are inserted
> hope this will solve the problem
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Kenny M." wrote:
>

About my way to insert rows.

I have heard that the most time consuming task into a DB is the when we use
the Insert Statatement,
Is that true?
Ok I have an application that sends from 10 to 500 rows to the DB, those
rows are compared again a fixed value and then they are inserted to the DB i
f
the comparison is ok.
I would like to do all that inside the DB (a SP) but I don’t know how, so
I
created a WebService to do that, my point is that I’m not inserting row by
row instead I hold the rows in a Dataset (memory) and later I make one big
insert to the DB.
My question is should I leave that code like this or should go to the DB in
order to insert row by row to gain some performance?
Can you think a better way to do that, because that is the heart of my app
and I want to minimize the time it takes.
Thnks.
Kenny M.Hi Kenny,
Maybe this will give you a few solutions.
http://www.sommarskog.se/arrays-in-sql.html
"Kenny M." <KennyM@.discussions.microsoft.com> wrote in message
news:26D58C74-C301-466F-9B58-AC3C2A84AAFA@.microsoft.com...
>I have heard that the most time consuming task into a DB is the when we use
> the Insert Statatement,
> Is that true?
> Ok I have an application that sends from 10 to 500 rows to the DB, those
> rows are compared again a fixed value and then they are inserted to the DB
> if
> the comparison is ok.
> I would like to do all that inside the DB (a SP) but I don't know how, so
> I
> created a WebService to do that, my point is that I'm not inserting row by
> row instead I hold the rows in a Dataset (memory) and later I make one big
> insert to the DB.
> My question is should I leave that code like this or should go to the DB
> in
> order to insert row by row to gain some performance?
> Can you think a better way to do that, because that is the heart of my app
> and I want to minimize the time it takes.
> Thnks.
> --
> Kenny M.

about Db table cell setting

How do I enable multiline on data table rows (cells)?

When I press enter the cursor jumps to the next cell and no the next line in the same cell.

Thanks

Hi,

What version of SQL Server are you using, also what application are you using?

Visual Studio, Access, ASP Web App etc...|||

hi,

sql server 2005 express and standard edition.

Thanks

About automatic process at midnight

Hi
I have a table1 which hold a field (amount) with 1000 rows
The amount in the 1000 rows is consumed by clients during the day
The next day the amounts have to return to their original values (at this
time I’m doing that by hand)
Now I’m going to save the original values in another table
My question is: Can the SQL server copy the original values from table1 to
table2 at midnight or at a time I schedule?
Thks again.Yes. Create a SQL Agent job. Make sure the SQLSERVERAGENT service is set t
o
Automatic.
"Kenny M." wrote:

> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I’m doing that by hand)
> Now I’m going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>|||Insert them into a new table
1.INSERT INTO NewTableName
Select * From OldTableName
2.Update the Working table (at night after the work) with a update statement
referencing the ID column of the table
BEGIN Transaction
DELETE FROM Oldtable where ID IN (Select IDCOLUMN From NewTable)
INSERT INTO Oldtable SELECT * from NewTable
IF @.@.ERROR = 0
COMMIT
ELSE
ROLLBACK
Is it that what you ment ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Kenny M." <KennyM@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D19D255D-E863-4E71-8E53-2145187103A7@.microsoft.com...
> Hi
> I have a table1 which hold a field (amount) with 1000 rows
> The amount in the 1000 rows is consumed by clients during the day
> The next day the amounts have to return to their original values (at this
> time I'm doing that by hand)
> Now I'm going to save the original values in another table
> My question is: Can the SQL server copy the original values from table1 to
> table2 at midnight or at a time I schedule?
> Thks again.
>