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
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,391
Messages
5,468,327
Members
406,579
Latest member
lollypop1389

This Week's Hot Topics

Top