MS-Query code from EXCEL97 doesn't work in EXCEL2003 ??

[Guest]

I am using MS-Query to import data from SQLServer2000 to MS-EXCEL.
We recently upgraded to EXCEL2003 from EXCEL97 and now MS-Query don't
understand more "complicated" code
which worked without problems in EXCEL97.

The code below is an example of this problem. I understand that I can solve
this through building views in SQLServer but I would prefer not to because of
the number of views required.

What has happended to MS-Query in EXCEL2003 and is there an solution to this
available from Microsoft?
---------------------------------------------------------------------------------------

SELECT
(USER_DISBURSED_PLANNING2.Allocation_Account) as 'Alloc',
USER_DISBURSED_PLANNING2.Allocation_Account_Text,
USER_DISBURSED_PLANNING2.CountryCode2,
USER_DISBURSED_PLANNING2.CountrynameSv2,
Excel_Sektorer_MH.H_SEKTOR_KOD + ' ' + Excel_Sektorer_MH.Main_Sector
as 'Main_Sector',
Excel_Vomr_Öst.Vomr_Öst +' '+ Excel_Vomr_Öst.Verksamhetsområde
as 'Vomr_Öst',
USER_DISBURSED_PLANNING2.Contribution_ID + ' ' +
USER_DISBURSED_PLANNING2.Contribution_Title_Eng AS 'Contribution',
USER_DISBURSED_PLANNING2.Resp_Officer_Cont as 'Officer',
USER_DISBURSED_PLANNING2.Agr_Start,
USER_DISBURSED_PLANNING2.Agr_End,
USER_DISBURSED_PLANNING2.Status as 'S',
Sum(USER_DISBURSED_PLANNING2.Outcome_2004) AS 'Disb_2004' ,
Sum(USER_DISBURSED_PLANNING2.Outcome_Present_Year) as 'Disb_2005',
Sum(USER_DISBURSED_PLANNING2.DAA2004) as 'DAA_2004',
Sum(USER_DISBURSED_PLANNING2.DAA2005) as 'DAA_2005',
Sum(USER_DISBURSED_PLANNING2.DAA_TOTAL) as 'DAA_Total',
Sum(USER_DISBURSED_PLANNING2.FC2004) as 'FC_2004',
Sum(USER_DISBURSED_PLANNING2.FC2005) as 'FC_2005'
FROM
LISPC05.dbo.USER_DISBURSED_PLANNING2 USER_DISBURSED_PLANNING2 LEFT OUTER JOIN
LISPC05.dbo.Excel_Vomr_Öst Excel_Vomr_Öst
ON USER_DISBURSED_PLANNING2.Sector = Excel_Vomr_Öst.SectorCode
LEFT OUTER JOIN LISPC05.dbo.Excel_Sektorer_MH Excel_Sektorer_MH
ON USER_DISBURSED_PLANNING2.Sector = Excel_Sektorer_MH.SEKTOR_KOD
GROUP BY
USER_DISBURSED_PLANNING2.Allocation_Account,
USER_DISBURSED_PLANNING2.Allocation_Account_Text,
USER_DISBURSED_PLANNING2.CountryCode2,
USER_DISBURSED_PLANNING2.CountrynameSv2,
Excel_Sektorer_MH.H_SEKTOR_KOD,
Excel_Sektorer_MH.Main_Sector,
USER_DISBURSED_PLANNING2.Contribution_ID,
USER_DISBURSED_PLANNING2.Contribution_Title_Eng,
USER_DISBURSED_PLANNING2.Resp_Officer_Cont,
USER_DISBURSED_PLANNING2.Agr_Start,
USER_DISBURSED_PLANNING2.Agr_End,
USER_DISBURSED_PLANNING2.Status,
Excel_Vomr_Öst.Vomr_Öst,
Excel_Vomr_Öst.Verksamhetsområde
HAVING
(USER_DISBURSED_PLANNING2.Status IN ('P', 'A'))
AND (USER_DISBURSED_PLANNING2.Allocation_Account
IN ('15651', '15661', '15691', '15692', '15741', '2342' ,'156911',
'156912', '156913', '156914', '156919' )
AND USER_DISBURSED_PLANNING2.CountryCode2
IN
('RUS','BLR','UKR','EST','LVA','LTU','BAL','BGR','ROM','ROU','POL',
'SVK','SVN','CZE','HUN','COE' ) )

OR (USER_DISBURSED_PLANNING2.Status LIKE 'C') AND
(USER_DISBURSED_PLANNING2.Allocation_Account
IN ('15651', '15661', '15691', '15692', '15741', '2342' ,'156911',
'156912', '156913', '156914', '156919' )
AND USER_DISBURSED_PLANNING2.CountryCode2
IN ('RUS','BLR','UKR','EST','LVA','LTU','BAL','BGR','ROM','ROU','POL',
'SVK','SVN','CZE','HUN','COE' )
)
AND (USER_DISBURSED_PLANNING2.Agr_End > '200400')

 

[Nick Hodge]

Not an expert in SQL, but AFAIK the MSQuery module has not changed for some
time and should work, so I suspect the fault lies elsewhere. What error do
you get? Have you tried looking at the query in MSQuery itself? Has the
upgrade screwed the ODBC drivers somehow?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Thankyou for Your reply. The query works in a way but
all columns where I have a assigned a new name as in
the first column where I want to rename Allocation_Account to Alloc
returns "empty" as columnheader. No columnheader at all.
When I look at the SQLcode it has been changed during the query
removing all code like " AS 'xxxx' " from the lines where I use this.
The code works fine though in SS2K Query Analyzer etc.

 

[Guest]

I suppose that a possible workaround would be to write the columnames
manually in excel and then in data range properties set "return the query
without column headers".

 

[Nick Hodge]

That would certainly work but I am not sure why you would have to as I am
almost sure there is no change.

mmmmmmmmmmm....anyone?

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)