I'm running against a SQL 2000 DB with multiple selects, basically collecting the number of rows from about 80 tables.

I need to get the output of all the selects by email but the VC output only displays the result of the first select.

How can I get VC to capture all the results?

e.g.

SELECT COUNT(*) AS table 1 FROM table 1
SELECT COUNT(*) AS table 2 FROM table 2

If the column were the same it could be treated as one query. In your case you probably need two SQL Tasks and use the output Variables to retrieve both at the same time.

---

I have about 6 tasks that have about 50 selects each in them so that won't really work . . .

That would be a feature request then (and I am moving it to that forum). It is not built that way, either my Microsoft or by us. The main reason is that we work against one set of columns and one resultset. If the columns or resultset was mixed in its format it would be problems both for MS and for us. I know the query browser in MS handles it by showing two windows with result but that is just a quick GUI solution.

---

May I suggest a couple of alternative approaches.

Using the example given on selecting row count

a) Use union.

select 'TableA' as tablename, count(*) from TableA
union
select 'TableB' as tablename, count(*) from TableC
union
select 'TableC' as tablename, count(*) from TableC

b) Use a table containing 2 columns, tablename and count.

Insert into that table the result of the "select 'TableA' as tablename, count(*) from TableA"

The above can either be achieved using SQL queries on VC or as Stored Procedures.

Personally I prefer having my queries(views) or stored procedures on SQL. In that way they are available to the DBA as well.

I use the above methodology extensively.

Graham