Excel 2013: Getting Data From Parametized SQL Query (vs SQL Server)

I would have thought that dragging in data from SQL Server into Excel (both Microsoft products) would be easy - and it is if you're looking to drag in entire tables, views, etc. But if you want to do something a little more complicated with parameters it becomes a lot harder and less intuitive to get it to work.

The example below shows how to get the ExecutionLogs from a SQL Server instance between two dates.
 
I'm going to use Excel 2013 as it's the latest version and the one I have to hand.

Create a blank workbook by selecting "Blank workbook" (which is usually the first option in the middle of the screen);

Excel 2013: New "Blank workbook" Tile

Select the "Data" page in the ribbon and then click on "From Other Sources" in the "Get External Data" part of the ribbon (on the left). Select "From Microsoft Query" (which should be the very bottom option);

Excel 2013: Data Page

 NOTE: you may think selecting "SQL Server" is a slightly more obvious choice. However this will not allow you to use parametrized SQL - it's just for direct export from tables or views (why that's the case if beyond me!).

This will then open the "Choose Data Source" dialog;

Excel 2013: Choose Data Source Dialog

This dialog clearly dates from an earlier version of Windows and it's difficult to see how Microsoft couldn't have "updated" this with the rest of the 2013 look-and-feel. I'm running Windows 7 but I have have a sneaking suspicion that everyone from Windows XP onwards will be familiar with this dialog (although possibly not with the addition of "OLAP Cubes").

This dialog also isn't part of Excel, it's a separate application. Sure Microsoft will score some marks for re-use of a standard Windows component but the change in interface is jarring to say the least ... and it gets worse.

Leave "New Data Source" highlighted and click "OK";

Excel 2013: Create New Data Source Dialog

We seem to have slipped back to a pre-wizard era and we now have fields labelled 1 to 4. When we complete field 1, field 2 becomes available, on completing field 2 field 3 becomes available. This is jarring different from the other dialogs within Excel 2013.

Anyway populate fields and 2 in the dialog, selecting "SQL Server" from the drop down (in mine it was at the very bottom). Then click "Connect ...";

Excel 2013: SQL Server Login

Enter the login information - "Use Trusted Connection" means use your already authenticated (Active Directory) credentials - once you've entered a Server the "Options" box at the bottom right will become available, click on it;

Excel 2013: SQL Server Login Dialog - Extended

Use the "Database" drop down to select the database you wish to connect to. If you leave it as default it will pick the default database for your database user.

Click "OK".

Click "OK" again (on the "Create Data Source" dialog) - do not pick a table in the bottom drop down, we're going to use SQL with parameters.

The data source you just created should be select (in the "Choose Data Source" dialog) so just click "OK".

You will then be presented with the "Query Wizard - Choose Columns" dialog;

Excel 2013: Query Wizard - Choose Columns

Now you'll notice that you can't do anything from this stage *except* select a table.

Click "Cancel" (at the bottom right);

Excel 2013: Microsoft Query Confirmation Dialog

 Click "Yes";

Excel 2013: Add Tables Dialog

We're not working with tables so click "Close";

Excel 2013: Microsoft Query

Click on the "SQL" button on the menu bar;

Excel 2013: Microsoft Query - SQL Dialog

Here is the SQL we are going to use;

SELECT
  EL.InstanceName,
  EL.ItemPath,
  EL.UserName,
  EL.ExecutionId,
  EL.RequestType,
  EL.Format,
  EL.Parameters,
  EL.ItemAction,
  EL.TimeStart,
  EL.TimeEnd,
  EL.TimeDataRetrieval,
  EL.TimeProcessing,
  EL.TimeRendering,
  EL.Source,
  EL.Status,
  EL.ByteCount,  EL.AdditionalInfo
FROM ExecutionLog3 EL
WHERE EL.TimeStart >= ["Min Start Date"]
AND EL.TimeStart < ["Max Start Date"]
ORDER BY EL.TimeStart DESC

Enter the SQL and click "OK".

NOTE: There are a couple of "gotchas" here. The SQL is processed prior to being run and it isn't particularly flexible. If you use ANSI SQL (JOIN ... ON ...) then you won't get the graphical interface at best, or it just won't work. Equally including square-backets [] seems to break the SQL, as does having "dbo." in front of the table name.

"Broken" SQL is usually identified by you being told that the SQL doesn't support the graphical interface. This is usually a prelude to a more obscure error.

Providing everything is working OK you'll see;

Microsoft Query: Sample Data

Click on the "Exit" button (fourth from the left at the top left).

This closes Microsoft Query and returns control to Excel. The "Import Data" dialog will now appear;

Excel 2013: Import Data Dialog

Change "=$A$1" to "=$A$4" (so we have a few lines for the parameter entry boxes) and click "OK";

Enter "Start Date" in A1, and "End Date" into A2 (just labels). And then two dates into B1 and B2 (these will be the from/to dates we run the report as);

Excel 2013: Parameter Values in Excel

Now we need to link up the cells we've used with the parameters in use in our query. Click on the "Data" tab in the ribbon and then "Connections";

Excel 2013: Connections

Select the connection and then click "Properties";

Excel 2013: Connection Properties

Click on the "Definition" tab;

Excel 2013: Connection Properties - Definition Tab

Click on the "Parameters" button at the bottom (if you have used the SQL Server option in Excel this is where you'd have the problem - "Parameters" would be permanently greyed out);

Excel 2013: Parameters

As you can see in the list there are two parameters, the two we created earlier in the SQL. Both are currently set to prompt us for values. Click on the "Get the value from the following cell" radio group and select the cell we have entered the Start Date in;

Excel 2013: Default Parameter Value

You can also check the "refresh automatically when cell value changes" box if you want to work that way.

Repeat the process with the Max Start Date Parameter.

Click "OK" (closed Parameters dialog)

Click "OK" (closes Connection Properties dialog)

Click "Close" (closes Workbook Connections dialog)

Click "Refresh all" (in the ribbon)

And we're done! If this was useful for you don't forget to leave a comment ...

Oracle SQL Developer: Using 'Windows Authentication' To Connect to SQL Server (Fixing SSO Failed Error)

The purpose of this post is to help you solve the an error message which prevents you using "Windows Authentication" to connect to SQL Server databases within Oracle SQL Developer.

The error message you get is;

Status: Failure - I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.

Which is nicely displayed at the bottom of the "New/ Select Database Connect" dialog when you're trying to connect to your SQL Server instance;

Oracle SQL Developer: Windows Authentication Error

Now you're seeing this error because the system can't locate one of the required .dll's it needs to order for SSO to work.

This .dll is included in the installation (which includes the JRE and the SQL Server plugin). If you do a search in the directory;

SQL Developer Install Directory

For the file ntlmauth.dll and then copy it into the root (the same as the sqldeveloper.exe application) and then shutdown and restart you'll find that this will have fixed the connection issue.

Copying to to the root directory will allow the application to work without having to install anything on the machine it's running on (as opposed to altering the path or copying to file to somewhere else that's already on the path).

SQL Server: Installing BIDS 2008R2 (Developer Edition)

After inserting the CD and running the Setup.exe the following User Account Control (UAC) dialog will appear;

SQL Server 2008 R2 UAC Dialog

Click "Yes".

The SQL Server Installation Center dialog will now appear;

SQL Server Installation Center

Click on "Installation" on the left (highlighted above);

SQL Server Installation Center (Installation Page)

Click "New installation or add features to an existing installation" (the top option on the right);

SQL Server 2008 R2 Setup

The installation program will run some tests to make sure your system can run the software. I'm installing onto Windows 7 Enterprise (32-bit). If there any any issues you'll need to resolve them before it will allow you to continue. Click "OK";

SQL Server 2008 R2: Product Key Dialog

After entering your product key click "Next";

SQL Server 2008 R2: License Terms

Click on the "I accept the license terms" checkbox (after reading it of course!) and then click "Next";

SQL Server 2008 R2: Setup Support Files

Click "Install" (if the installation of Setup Support Files fails with 1605 or 1608 error message then you may be interested in this post here - thank you Sage!);

SQL Server 2008 R2: Setup Support Rules

Click "Next";

SQL Server 2008 R2: Setup Role

Click "Next";

SQL Server 2008 R2: Feature Selection

Select "Business Intelligence Development Studio" (I've also chosen the Client/ Management Tools as they're often quite useful as well). Click "Next";

SQL Server 2008 R2: Installation Rules

Click "Next";

SQL Server 2008 R2: Disk Space Requirements

Click "Next";

SQL Server 2008 R2: Error Reporting

Click "Next";

SQL Server 2008 R2: Installation Configuration Rules

Click "Next";

SQL Server 2008 R2: Ready to Install

Click "Install";

SQL Server 2008 R2: Installation Progress

Wait for the installation to complete (takes about 10 minutes);

SQL Server 2008 R2:Complete

Click "Close".

You can now find SQL Server Business Intelligence Development Studio on your start menu;

SQL Server Business Intelligence Development Studio

NOTE: One of the fantastic anomalies with BIDS is that unless you have Integration Services installed locally you don't seem to actually do deploy to a remote server from within BIDS - you need to deploy to the file system and then use management studio to deploy to the server.

Also unless you install Integration Services *locally* you won't be able to test the deployed package on your system.

SSIS: Exporting Query Results To A Flat File

This is a fairly quick blog post to just go through the process of taking some SQL, running it against a database, and exporting the result into a Flat File. We recently followed pretty much this process to allow us to schedule a non-interactive job to export our entire Financial General Ledger into a flat file to give to our auditors.

The first step is to start BIDS, go to the File menu and select "New Project";

BIDS: New Project Dialog

Select "Integration Services Project", give it a name and select a location to save it to then click "OK". you will then be presented with the new project;

BIDS: Blank SSIS Project

First things first. In the "Solution Explorer" (right-hand side) you'll notice that there is a file called "Package.dtsx". If you deploy this to a server then this is exactly what it will be called - rename it to something else. I usually use the name of the project (without spaces). A dialog will appear;

SSIS: Package Renaming Dialog

Click "Yes", then you'll see something like this;

BIDS: Solution Explorer

Now let's start building the process. Drag a "Data Flow Task" from the Toolbox (on the left) into the designer;

BIDS: Adding a Data Flow Task

Once you've added the new "Data Flow Task" double-click the icon to open it;

Now assuming you're going to be dragging the data down from an OLE DB Source (like me) you need to drag one of those into the designer;

BIDS: OLE DB Source

The little red "x" at the right-side of the new icon means you haven't configured it yet, double click the icon to open the configuration screen;

BIDS: OLD DB Source Editor

The first thing you need to do is connect to your database. This will involve clicking "New" to invoke the "Configure OLE DB Connection Manager" dialog;

BIDS: Configure OLE DB Connection Manager Dialog

In here you can setup connections for SSIS. Once you've created a connection it will be available here for you to use in other SSIS projects.

When you're happy with your connection (i.e. it works!) make sure it's selected and click "OK".

You are now returned to the previous screen with your selected connection highlighted;

BIDS: OLE DB Source Editor with Selected Connection

You have two choices you can either directly pick a table or enter some SQL. I'm going to enter some SQL so you change the drop down from "Table or view" to SQL Command;

The SQL I'm using is;

SELECT *
FROM OPENQUERY ( XXX, 'select * from lot_genealogy_whereused')

This retrieves data (via SQL Server) from our Oracle e-Business Suite. Paste the SQL into the "SQL Command Text" entry box;

BIDS: OLE DB Source Editor with SQL Command

Click on "Parse Query" (to make sure everything is working OK) and then click "OK" at the bottom.

This closes the dialog and returns you to the main window;

BIDS: OLD DB Source (No Errors)

You'll notice that the little red "x" has gone. Now drag a "Flat File Destination" (NOTE it's DESTINATION, not Source) into the designer and connect them up (drag the green arrow from the OLE DB Source to the new object);

BIDS: OLE DB Source with Flat File Destination

You'll notice that the red "x" is back on the Flat File Destination object. Double-click it to configure it;

BIDS: Flat File Destination Editor

To create a new "Flat File Connection Manager" click on "New";

BIDS: Flat File Format

I'm going to use "Delimited" (basically CSV). Click "OK";

BIDS: Flat File Connection Manager Editor

Click on "Browse" to select the destination where you want to place the file. It's important that the destination you select will be visible to the server you're going to deploy it to (using UNC paths if it's on a different server);

BIDS: Flat File Connection Manager Editor

The other thing I change is to add in a double-quote as a Text Qualifier. the main reason for that is if I have data (like mobile numbers) in the source then I don't want Excel treating it as a number when I open it - including a text delimiter solves this problem.

Remember to check the "Column names in the first data row" checkbox if you want them!

Click "OK" when you're done.

BIDS: Flat File Destination Editor

Click on "Mappings" (on the left) if you fancy a look at how the columns in your source are being mapped to the destination;

BIDS: Flat File Destination Editor (Showing Mappings)

Click "OK" to return to BIDS;

BIDS: Designer Window

The designer now shows you that the little red "x" has gone and you're ready to test your package.

Click "Run"/"Start Debugging" and after a few seconds the task will run and you'll start seeing records being written to your file. When the task is complete everything will go green;

SSIS: Task Successful Completion

I'll create a separate blog post for how to deploy the SSIS package to a server so it can be run as a background task (200k rows took around 10 seconds, but if you had 20m you wouldn't want to sit there watching it).