I have an Excel file that I want to open up, hit refesh, and it pulls in the data from the SQL server and then I manipulate the data and create some dashboards. I actually have this but I'm pulling in to much data and need a more complex Query with some JOINs to narrow it down.
I can query a SQL Server from with-in Excel. I can edit the query (its just text) but it tedious its a bit like wlking in the dark because with Excel does the query the error messages are useless. I used to do this using the .exe called MSQuery that comes with Excel (but it chokes on a lot of things).
I can write the query I want using Microsoft SQL Server Management Studio.
You would think that I could write the query in Studio and cut and paste the Query into Excel but the syntax seems to be slightly different. Specifically, in Studio, before you write the query you have to select the database therefore the FROM statement in the SQL query starts with dbo.vSomeViewName.
Whereas, in Excel/MSQuery, you don't specify the database and so the FROM statement in the queries start with TheDataBaseName.dbo.vSomeViewName. After that, there some other differences that I don't understand.
I get the feeling that I'm missing something basic that that books seem to assume is obvious or is not needed.
Best Answer
I regularly copy and paste queries between Excel and SQL Server Studio and I don't encounter the problems you describe. As long as the database is identified in the connection string in Excel then you shouldn't need to put the database name in front of every table or column name.
Alternatively, you can specify the database name at the beginning of the query:
use database