| Groups | Blog | Home |
sql server dts : 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?
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 [quoted text, click to view] > 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; [quoted text, click to view] "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 > > > 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 > > > >
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 [quoted text, click to view] > 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 >>> 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 >>>
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 [quoted text, click to view] "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 > > > 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 > >>> 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 > >>> > >
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 [quoted text, click to view] > 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 >>> 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 >>>>> 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? >>>>> Mik
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 [quoted text, click to view] "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 > > > 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 > >>> 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 > >>>>> 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 > >
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |