Getting data into Excel from SQL Server using Microsoft Query

[amitvalia]

Here's a query that works just fine on my SQL Server Studio.

select t.converted_date as AsOfDate,  a.acc_name as Name, mvs_mkt as Prt, a.field_7 as AcctType
from IndataDB_MarketValues..tblMarketValues m, 
IndataDB_MarketValues..tblDateTable t,
indatadb_main..rimsacc_Master a
where m.mvs_counter = t.counter
and convert(varchar,converted_date,101) = '06/30/2017'
and mvs_port-90000000 = a.acc_cust
and mvs_mkt > 1000000

But when I run the same query through Excel (Data -> From Other Sources -> From Microsoft Query and select the appropriate Data Source Name and typing the query in the SQL Statement box), I get an error saying
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name."

Interestingly, the same query with a '*' in the end works.

select t.converted_date as AsOfDate,  a.acc_name as Name, mvs_mkt as Prt, a.field_7 as AcctType, *
from IndataDB_MarketValues..tblMarketValues m, 
IndataDB_MarketValues..tblDateTable t,
indatadb_main..rimsacc_Master a
where m.mvs_counter = t.counter
and convert(varchar,converted_date,101) = '06/30/2017'
and mvs_port-90000000 = a.acc_cust
and mvs_mkt > 1000000

I feel It's something right in front of me and I'm not seeing it.

I'm using MS Office Pro 2010, SQL Server 2008 R2

 

[SpillerBD]

Is it the asterisks or the comma?

 

[amitvalia]

No it's not.
When I changed the query (or just last table) to use SQL Server syntax as against Oracle syntax it works. The query below works.

select distinct convert(varchar,t.converted_date,101) as AsOfDate,left(convert(varchar,t.converted_date,112),4) + '-' + SUBSTRING(convert(varchar,t.converted_date,112),5,2) as Period, a.acc_acct as Acct, a.acc_name as AcctName, mvs_mkt, a.acc_mgr as IC, a.field_7 as Acct_Type, c.custodian_name as Custodian
from IndataDB_MarketValues..tblMarketValues m,
IndataDB_MarketValues..tblDateTable t,
rimsacc_Master a
<b>inner join custodians c on c.acc_cus = a.acc_cus</b>
and m.mvs_counter = t.counter
and datediff(MONTH, converted_date, current_timestamp - day(current_timestamp))< 6 and mvs_mqy in ('M','Q','Y')
and mvs_port-90000000 = a.acc_cust
and mvs_mkt > 10
order by 1


Also, and interestingly,
If I go back to the properties tab and change the query to the one that did not work, it starts working.