|
Home > Archive > MS SQL Server DTS > March 2006 > Problem with SQL Task in MS SQL Server DTS package
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Problem with SQL Task in MS SQL Server DTS package
|
|
|
| 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
| |
| Allan Mitchell 2006-03-15, 7:30 am |
| 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
> 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
> 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
>
| |
|
| 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,
>
> 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
>
>
>
>
| |
| Allan Mitchell 2006-03-16, 7:30 am |
| 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
[color=darkred]
> 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:
>
| |
|
| 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,
>
> 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
>
>
>
>
| |
| Allan Mitchell 2006-03-16, 9:25 am |
| 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
[color=darkred]
> 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:
>
| |
|
| 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:
> 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
>
>
>
>
|
|
|
|
|