1

I have using Microsoft Query in Excel 2007 for the past few weeks and have had many cases where the query works fine in SQL Server 2005 but gives irrelevant errors in Microsoft Query. For example I have this case Multipart identifier error in Excel 2007 MS Query but not in SQL Server 2008 where when I removed the sub queries in SELECT and joined those tables it worked. It doesn't seem to work in this case too.

Pass parameters to temp variables in MS Query on SQL Server from Excel I guess CTEs don't work in MS Query like CTE in MS Query Excel 2007. It doesn't work in this case too. Alternative to Left Join

Can anyone list all the SQL Limitations in Microsoft Query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ram
  • 3,092
  • 10
  • 40
  • 56

1 Answers1

3

I often found that queries that run on Oracle, AS400 and MS Sql wouldn't run on MS Query, I think the reason is because that MS Query always tries to display the query graphically. If the query is impossible to show graphically MS Query will display a waning message and then display just results (not the table or conditional panes). Simple queries work fine but there is a middle ground between complex and simple that MS Query seems to choke on.

The solution I found was not to simplify my queries, but to actually make them more complex thus forcing MS Query not to attempt a graphical display. I did this by making my queries a sub query and entering into MS Query like so:

SELECT * FROM (
--enter your query here
) a
Dave Sexton
  • 10,768
  • 3
  • 42
  • 56