SQL Drill – Excel Add-In for building and running SQL queries

About SQL-Drill

Building complex SQL queries for a particular database can take some time and effort. SQL-Drill is a simple Excel Add-in which can connect to a SQL Server or MySQL Database, show all the tables, and allow you to easily pick what columns you want to select data from. It will take care of the inner selects and joins, and will give you the final SQL query (which you can use in SQL Server Management Studio for reports etc.)

Installation

  1. Download from: http://www.sqldrill.com/ (local mirror)
  2. Install MSI setup
  3. Run as Administrator – a Command Prompt
    regsvr32 “C:\Program Files (x86)\SQL Drill\SQLDrill.dll”
  4. Open Excel. The Add-Ins tab should now show, and in this Tab, will be “SQL Drill”

How SQL-Drill will appear in Excel once installed.

Connect with SQL Server

Connecting to SQL Server is very easy. Click the New/Edit, Add a new Profile, Edit the Connection String, choose “Microsoft OLE DB Provider for SQL Server” click next, enter the server name, and login details (or SA details). Clicking “Test Connection” should return “Test connection succeeded”.

Connect SQL-Drill to Microsoft SQL Server

Connect with MySQL Server

Connecting SQL-Drill with MySQL Server is a little more involved than connecting to Microsoft SQL Server. There are two main Steps. One is setting up the ODBC Connection in the Windows ODBC Data Sources. Two is using this connection inside of SQL-Drill.

  1. Download and install the MySQL ODBC Connector
    (I had to install both mysql-connector-odbc-5.1.10-winx64.msi and mysql-connector-odbc-5.1.10-win32.msi as I was on Win7 x64 with Excel x32)
  2. START -> Search/Run -> odbcad32
    This should open the “ODBC Data Source Administrator” app.
    (Also accessible from: Control Panel – Administrative Tools – Data Sources (ODBC))
  3. Under “User DSN”, choose Add. Choose “MySQL ODBD 5.1 Driver”, and click Finish.
  4. In the MySQL Connector/ODBC, enter the Connection details. Click Test and it should return “Test Successful”.
  5. Open up Excel. Open up SQLDrill.
    Click the New/Edit, Add a new Profile, Edit the Connection String, choose “Microsoft OLE DB Provider for ODBC Drivers” click next. Under “Use data source name” click the drop-down, and the earlier ODBC connection should be there. Clicking “Test Connection” should return “Test connection succeeded”.

Create ODBC Connection to MySQL

Connect SQL-Drill to MySQL via ODBC

Reference: -> http://blog.mclaughlinsoftware.com/microsoft-excel/accessing-to-mysql/

Using SQL Drill

SQL-Drill is very easy to use. After connecting, all the tables are shown on the right. Drag and drop tables into the workspace. Tick the columns you want data from; the select query will be visible, and you can execute the query.

SQL-Drill worked great for a SQL Server Database as it identified all foreign keys and relationships between the tables. As a result it made intelligent select statements.

For MySQL however, sql drill didn’t identify multiple foreign keys and as a result did not have the relationship between the tables. As a result for MySQL, it did not create intelligent select statements (i.e. with Joins and inner selects).

SQL-Drill Usage on a SQLServer DB showing the relationships identified between tables.

This entry was posted in mssql, mysql and tagged . Bookmark the permalink.

3 Responses to SQL Drill – Excel Add-In for building and running SQL queries

  1. STANCIU ADRIAN says:

    I have WIN 7 and Office 2007 on my current laptop. On my old one I have use SQL DRILL with WIN XP and Office 2007 an d worked fine. Now after a clean instalation I do not see the add-in in excel. How can I activate it?

    Thank you.

    • admin says:

      @ADRIAN: You’ll have to go through the Installation instructions above. You’ll have to reinstall sqldrill. You may also have to go: START -> (in the search box, type “cmd”) -> (Right-Click) on cmd.exe and go “Run as Administrator”. Then in the black command prompt, type: regsvr32 “C:\Program Files (x86)\SQL Drill\SQLDrill.dll”
      It may take a little trial and error, but it should work.

  2. Gonçalo Rodrigues says:

    I use because of its hability do produce XLSX documents with more than 65000 lines. got one with 750000 lines…
    If it times out change the ODBC Connection timeout setting
    If it gives a data bindings error try this: http://www.jcapper.com/TechServ_Bind.html

    Thank you.

Leave a Reply to admin Cancel reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>