{"id":649,"date":"2012-03-16T23:04:00","date_gmt":"2012-03-16T22:04:00","guid":{"rendered":"http:\/\/sburke.eu\/blog\/?p=649"},"modified":"2022-07-25T23:27:40","modified_gmt":"2022-07-25T22:27:40","slug":"sql-drill-excel-add-in-for-building-and-running-sql-queries","status":"publish","type":"post","link":"http:\/\/sburke.eu\/blog\/2012\/03\/sql-drill-excel-add-in-for-building-and-running-sql-queries\/","title":{"rendered":"SQL Drill – Excel Add-In for building and running SQL queries"},"content":{"rendered":"\n

About SQL-Drill<\/h2>\n\n\n\n

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.)<\/p>\n\n\n\n

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

    Connect with SQL Server<\/h2>\n\n\n\n

    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”.<\/a>Connect SQL-Drill to Microsoft SQL Server<\/p>\n\n\n\n

    \"\"<\/a>
    Create ODBC Connection to MySQL<\/a>Connect SQL-Drill to MySQL via ODBC<\/figcaption><\/figure>\n\n\n\n

    Connect with MySQL Server<\/h2>\n\n\n\n

    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.<\/p>\n\n\n\n

    1. Download and install the MySQL ODBC Connector<\/a>
      (I had to install both<\/strong> 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<\/em>)<\/li>
    2. START -> Search\/Run -> odbcad32
      This should open the “ODBC Data Source Administrator” app.
      (Also accessible from: Control Panel – Administrative Tools – Data Sources (ODBC)<\/em>)<\/li>
    3. Under “User DSN”, choose Add. Choose “MySQL ODBD 5.1 Driver”, and click Finish.<\/li>
    4. In the MySQL Connector\/ODBC, enter the Connection details. Click Test and it should return “Test Successful”.<\/li>
    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<\/strong>” 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”.<\/li><\/ol>\n\n\n\n
      \"\"<\/a>
      Create ODBC Connection to MySQL<\/figcaption><\/figure>\n\n\n\n
      \"\"<\/a>
      Connect SQL-Drill to MySQL via ODBC<\/figcaption><\/figure>\n\n\n\n

      Reference: -> http:\/\/blog.mclaughlinsoftware.com\/microsoft-excel\/accessing-to-mysql\/<\/a><\/p>\n\n\n\n

      Using SQL Drill<\/h2>\n\n\n\n

      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.<\/p>\n\n\n\n

      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.<\/p>\n\n\n\n

      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).<\/a>SQL-Drill Usage on a SQLServer DB showing the relationships identified between tables.<\/p>\n\n\n\n

      \"\"<\/a>
      SQL-Drill Usage on a SQLServer DB showing the relationships identified between tables.<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"

      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 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[59,49],"_links":{"self":[{"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/posts\/649"}],"collection":[{"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/comments?post=649"}],"version-history":[{"count":2,"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/posts\/649\/revisions"}],"predecessor-version":[{"id":657,"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/posts\/649\/revisions\/657"}],"wp:attachment":[{"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/media?parent=649"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/categories?post=649"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sburke.eu\/blog\/wp-json\/wp\/v2\/tags?post=649"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}