0

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.

Improve this question
1
  • both of the answers so far helped a lot. I'd select both of them as answers if I could.
    – Alex
    Commented Sep 27, 2010 at 16:51

2 Answers 2

Reset to default
0

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

Improve this answer
1
  • 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
    Commented Sep 27, 2010 at 15:12
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.

Improve this answer
2
  • this helps enormously as well
    – Alex
    Commented Sep 27, 2010 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
    Commented Jun 10, 2013 at 16:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .