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:
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.
Again, 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.
Here, use the "Usage" tab to define, in this case that we're querying a SQL DataBase, the refresh of the data.
On the "Definition" tab:
change the "Command type" from Table to SQL
place your SQL Server query on the "Command text" area.
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.