Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
both of the answers so far helped a lot. I'd select both of them as answers if I could. –  Alex Sep 27 '10 at 16:51
add comment

2 Answers

up vote 0 down vote accepted

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

share|improve this answer
1  
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 Sep 27 '10 at 15:12
add comment

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.

share|improve this answer
    
this helps enormously as well –  Alex Sep 27 '10 at 16:50
    
For me step 5 should read: With the Data (Connections) tab selected, click the arror under Refresh All, click Connection properties. –  DeveloperDan Jun 10 '13 at 16:49
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.