<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

SQL Query to Excel

Published on
8,187 Points
1,887 Views
3 Endorsements
Last Modified:
Approved
Why run SQL server queries on SQL Server Management Studio, only to import the results to a Excel workbook so that they may be "worked" to make those beautiful reports that the boss likes so much?

Here's how to access the data, directly from the SQL Server source, and even, keep it synchronized.

We start by creating the data source. For that, access the menu:
 "Data" -> "From Other Sources" -> "From SQL Server"

A window with presenting the Data Connection wizard will open, and we need to insert:
Data Connection Wizard 3
 1. Server name: Is the SQL server instance instance name (Hostname\Instance Name).
 2. Log on credentials: Are the credentials you use to connect to your SQL Server Instance. Either use Windows Authentication, and it will assume the current logged on user, or use SQL Authentication and fill out the User Name and Password for the SQL Login.

On the "Next" window, select the data base from witch we want to retrieve the data, and the table or view.
Data connection Wizard 2Again, Next, chose a file name for the .odc file that stores the connection info and click Finish.

A New window will appear asking to define how the data will be displayed by excel, choose Table, you can later create a PowerPivot from this table ,... and where to place the data, a usual excel range, or cell.

After making your selection, click the "Properties" button.
Import Data
Here, use the "Usage" tab to define, in this case that we're querying a SQL DataBase, the refresh of the data.
Usage
On the "Definition" tab:
change the "Command type" from Table to SQL
place your SQL Server query on the "Command text" area.
SQL Query

We are now presented with a Table (considering we didn't choose the PowerPivot), with the SQL Server table headers on the first row, of the selected range, and the data returned from the SQL Server Query.
3
Comment
Author:x-men
0 Comments

Featured Post

Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month