Can you help?

I'm attempting to create a DTS package but have encountered a problem that I
haven't seen before. I'm attempting to compile statistics by inserting a
result set from a Sybase query into a MS SQL Server table. The following SQL
statement runs fine in SQL Advantage and it also returns accurate results in
DTS when I click the Preview button. However, after selecting the DTS
destination table, when I attempt to set Transformations, no columns are
available in the Source list. I expect to see the following columns in the
Source list:
- company
- total_qty
- active_qty
- source
- dtCaptured

-- The query --
select company, count(*) total_qty ,
( select count(*) from operatorm1 i where login_revoked <> 't' and
max_logins <> 0
and i.company = a.company ) , active_qty,
'ah' as source,
getdate() as dtCaptured
from operatorm1 a
group by company
order by company

If I change my query to the following, Transformations work fine but of
course, I don't get the results that I want.

select company,
'ah' as source,
getdate() as dtCaptured
from dbo.operatorm1
group by company
order by company

It seems that DTS doesn't like the count operations. Is this an "attribute"
(limitation) of SQL Tasks in DTS?

Mike

Hello mak,

You need to alias all columns or use their name. In your query you have
not aliased the sub-select. DTS is unable to offer you therefore a column
name.


Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

I see that there's a typo in the query in the original mail note. Here's the
current statement and unless I'm missing something, all result columns are
either named or aliased:

select company,
count(*) AS total_qty ,
(select count(*) from operatorm1 i where login_revoked <> 't' and
max_logins <> 0
and i.company = a.company ) AS active_qty,
'plano' AS source,
getdate() AS dtCaptured
from operatorm1 a
group by company
order by company;

"Allan Mitchell" wrote:

Hello mak,

Ok so it looks as though the columns should be generated just fine.

This is a long shot but can you remove the alias of your table as well.
You do not use the alias anywhere. I just wonder whether the driver gets
confused by this.




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Thanks Allan. I'm not an SQL expert but it looks to me like I'm using the
table aliases of a and i. How could I word my query without the aliases?

Mike

"Allan Mitchell" wrote:

Hello mak,

Ahhh you do use them I have just seem the relationship, my bad.

select
company,
count(*) AS total_qty ,
(select count(*) from operatorm1 where login_revoked <> 't' and
max_logins <> 0 and i.company = a.company ) AS active_qty,
'plano' AS source,
getdate() AS dtCaptured
from
operatorm1 a
group by
company
order by
company;

OK So looking at this I see no reason why the column mappings screen would
not show columns. Can you change the Query to

select
a.company,
count(*) AS total_qty ,
active_qty,
'plano' AS source,
getdate() AS dtCaptured
from
operatorm1 a
JOIN
(select count(*) as active_qty,company from operatorm1 where login_revoked
<> 't' and max_logins <> 0 ) b
ON
a.company = b.company
group by
a.company,active_qty
order by
a.company

Just a thought



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Allan, thanks for your attention to this problem. I did try your lastest
suggestion but with no better result... it's still not working. I think I'll
give up on this approach for now and look for some more efficient and
versitile way to meet my needs. This statement is only one of many similar
queries that I want to execute on a regular basis.

Mike

"Allan Mitchell" wrote: