Getting data into Excel from SQL Server using Microsoft Query

amitvalia

New Member
Joined
Jun 27, 2005
Messages
4
Here's a query that works just fine on my SQL Server Studio.

Code:
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.


Code:
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
 

amitvalia

New Member
Joined
Jun 27, 2005
Messages
4
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.
 

Forum statistics

Threads
1,082,618
Messages
5,366,619
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top