enumerating Foreach Loop from rowset

Please let me know if I am on the right track here.

I have an Execute SQL Task that selects multiple rows from an OLE DB connection, each row containing 3 columns (data types = string, Int32, Int32). In this task ResultSet = "Full result set" and Result Set > Result Name = 0, Variable Name = [User::viewInfo] which is a user variable with Data Type = Object.

I want to use a Foreach Loop Container to enumerate over the result set rows that are contained in the [User::viewInfo] variable described above. For each resultset row I want to breakout the 3 column values and assign them to 3 corresponding variables that can be referenced in a Data Flow in the Foreach Loop.

Current settings for the Foreach Loop Container: Collection > Enumerator = "Foreach ADO Enumerator", Collection > Enumerator Configuration > ADO object source variable = [User::viewInfo], Enumeration mode = "Rows in the first table". On the Variable Mappings page I select the 3 corresponding user variables I want the rowset column values assigned to, with indexes starting at 1 (not 0).

Thanks - Dana Reed

It sounds spot on to me Dana!

-Jamie

Enterprise Manager Error Pls Help

Hi All,
Sqlserver 7.
From the Enterprise Manager when i right click on a table
and select
Return all row. I am getting the below error
An unexpected error happened during this operation.
[Query]- Query Designer encountered a Query error:
Unspecified error
When i use Query Analyser i am able to retrive the Data
Pls help Its Urgent
TIA
HaseebIf the drive that holds the temp directory is running low on space this
error can be generated. Enterprise manager creates temporary files when it
returns all rows.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Enterprise Manager Error Pls Help

Hi All,
Sqlserver 7.

From the Enterprise Manager when i right click on a table and select
Return all row. I am getting the below error

An unexpected error happened during this operation.
[Query]- Query Designer encountered a Query error: Unspecified error

When i use Query Analyser i am able to retrive the Data
Pls help Its Urgent

TIA
AdilDid you start and stop EM

Why is this urgent btw?

EM is quirky...if you deleted an object it might still be referencing it...

Don't use EM to query data in the first place...|||Hi,

I didnt stop and start EM
Most of the programmer are using the EM and complaining. And non of the objects are deleted. It is happening with all the Tables
Pls help me out

Originally posted by Brett Kaiser
Did you start and stop EM

Why is this urgent btw?

EM is quirky...if you deleted an object it might still be referencing it...

Don't use EM to query data in the first place...

Enterprise Manager Error Pls Help

Hi All,
Sqlserver 7.
From the Enterprise Manager when i right click on a table
and select
Return all row. I am getting the below error
An unexpected error happened during this operation.
[Query]- Query Designer encountered a Query error:
Unspecified error
When i use Query Analyser i am able to retrive the Data
Pls help Its Urgent
TIA
HaseebIf the drive that holds the temp directory is running low on space this
error can be generated. Enterprise manager creates temporary files when it
returns all rows.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Enterprise Manager and Query Analyser Hang-up

When I use the Query Analyser, I can view the data in one table. But if I
insert a row to the table, the Query Analyser hangs up. If I want to drop an
index from that table using the Enterprise Manager, it hangs up too. All the
other tables work fine. How can I solve it? Thanks.
Joe
Drop/Create INDEX requres page lock and hence nobody is available to use the
table till transaction will be commited. In fact Iwould do that by EM
because it has lots of lacks.
When you insert a row into the table check out that the table does not have
a trigger which may hangs up the application.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FE0761E8-C157-4A45-BDB9-C7A4FDD63424@.microsoft.com...
> When I use the Query Analyser, I can view the data in one table. But if I
> insert a row to the table, the Query Analyser hangs up. If I want to drop
an
> index from that table using the Enterprise Manager, it hangs up too. All
the
> other tables work fine. How can I solve it? Thanks.

Enterprise Manager and Query Analyser Hang-up

When I use the Query Analyser, I can view the data in one table. But if I
insert a row to the table, the Query Analyser hangs up. If I want to drop an
index from that table using the Enterprise Manager, it hangs up too. All the
other tables work fine. How can I solve it? Thanks.Joe
Drop/Create INDEX requres page lock and hence nobody is available to use the
table till transaction will be commited. In fact Iwould do that by EM
because it has lots of lacks.
When you insert a row into the table check out that the table does not have
a trigger which may hangs up the application.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FE0761E8-C157-4A45-BDB9-C7A4FDD63424@.microsoft.com...
> When I use the Query Analyser, I can view the data in one table. But if I
> insert a row to the table, the Query Analyser hangs up. If I want to drop
an
> index from that table using the Enterprise Manager, it hangs up too. All
the
> other tables work fine. How can I solve it? Thanks.

Enterprise Manager and Query Analyser Hang-up

When I use the Query Analyser, I can view the data in one table. But if I
insert a row to the table, the Query Analyser hangs up. If I want to drop an
index from that table using the Enterprise Manager, it hangs up too. All the
other tables work fine. How can I solve it? Thanks.Joe
Drop/Create INDEX requres page lock and hence nobody is available to use the
table till transaction will be commited. In fact Iwould do that by EM
because it has lots of lacks.
When you insert a row into the table check out that the table does not have
a trigger which may hangs up the application.
"Joe" <Joe@.discussions.microsoft.com> wrote in message
news:FE0761E8-C157-4A45-BDB9-C7A4FDD63424@.microsoft.com...
> When I use the Query Analyser, I can view the data in one table. But if I
> insert a row to the table, the Query Analyser hangs up. If I want to drop
an
> index from that table using the Enterprise Manager, it hangs up too. All
the
> other tables work fine. How can I solve it? Thanks.

Entering Unique data into a column

Is there a better to insure an INSERT will only add a new row only if the
data being added to a particular column is unique, besides doing a SELECT
before doing the INSERT?
SELECT X from table where X='NewData'
ExecuteReader and test for number of Rows
if Zero then do the INSERT
Thank you...
BruceINSERT INTO YourTable (x, ...)
SELECT 'NewData', ...
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE x = 'NewData')
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Thanks.....
Bruce|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ecidneFpqq8UaW7cRVn-pA@.giganews.com...
> INSERT INTO YourTable (x, ...)
> SELECT 'NewData', ...
> WHERE NOT EXISTS
> (SELECT *
> FROM YourTable
> WHERE x = 'NewData')
> --
> David Portas
> SQL Server MVP
> --
>
Okay, What am I doing wrong? The following didn't work.. The INSERT happened
even though the value of 'Dentist' was in the table in the columne
AddressType.
INSERT INTO AddressType(AddressType) Values ('Dentist')
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
Now, my table, AddressType, had only two columns, PrimaryKey (with
autoincrement) and AddressType.
Thanks...
Bruce|||You added the VALUES clause, which means it's no longer an INSERT... SELECT.
Try:
INSERT INTO AddressType (AddressType)
SELECT 'Dentist'
WHERE NOT EXISTS
(SELECT *
FROM AddressType
WHERE AddressType = 'Dentist')
BTW I assume AddressType is declared unique. Always declare unique columns,
don't just rely on the INSERT.
David Portas
SQL Server MVP
--|||
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:L8udnQJ32Z_Hm2ncRVn-rA@.giganews.com...
> You added the VALUES clause, which means it's no longer an INSERT...
SELECT.
> Try:
> INSERT INTO AddressType (AddressType)
> SELECT 'Dentist'
> WHERE NOT EXISTS
> (SELECT *
> FROM AddressType
> WHERE AddressType = 'Dentist')
> BTW I assume AddressType is declared unique. Always declare unique
columns,
> don't just rely on the INSERT.
> --
> David Portas
> SQL Server MVP
> --
>
This works. Thanks. And no, the column is not declared unique. I forgot
about that attribute, but will add it...
Thanks again...
Bruce

Entering a row from a table to a column of other table

Is it possible to enter a complete record into a column say varchar(max) and then be able to retreive the record from the column. I am using sql 2005.

Thanks very much in advance,

you can use a separator for columns and insert it into a table.

insert into tablename (varcharnmaxcolumn)

select col1 +'|' + col2+'|' +col3

from table1

and during retrieval you can split the output as per separator as '|' in this case