How do I write a query in Microsoft SQL Server Management Studio and then use that Query in Excel

Question

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.

Solution 1

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

Solution 2

I do the following steps when I want to paste in a SQL Server query: (assuming Excel 2007)

1. Data > From Other Sources > From SQL Server
2. Enter server name
3. Choose the database you want and select a small table from the list of tables.
4. Hit OK
5. With the Table Tools>Design tab selected, click the arrow under Refresh, click Connection properties.
6. Click Definition
7. Change Command Type to 'SQL'
8. Paste in your query in the 'Command Text' box. Hit OK.

That has always worked for me.

Comments

[Alex]

both of the answers so far helped a lot. I'd select both of them as answers if I could.

[Alex]

Awesome Mike. That seems to be that "something basic" thing I'm missing. that's: Use "database"; and, for the connection string: database=YourDatabase (the SQL book I'm using doesn't mention the word USE at all)

[Alex]

this helps enormously as well

[DeveloperDan]

For me step 5 should read: With the Data (Connections) tab selected, click the arror under Refresh All, click Connection properties.