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.

Posted in mssql, mysql | Tagged | 3 Comments

‘systeminfo’ – Information about Windows, uptime, etc.

If you need to find out when Windows last rebooted, due to Windows Updates etc, open a Command Prompt, and issue:

systeminfo

A sample of the output is as follows:

C:\Users\sburke>systeminfo  
Host Name:                 SSITECH
OS Name:                   Microsoft Windows 7 Professional OS
Version:                   6.1.7601 Service Pack 1 Build 7601 OS
Manufacturer:              Microsoft Corporation OS
Configuration:             Standalone Workstation OS Build
Type:                      Multiprocessor Free
Registered Owner:          sburke
Registered Organization:
Product ID:                00xxx-OEM-xxxxx
Original Install Date:     13/10/2011, 16:51:59
System Boot Time:          14/03/2012, 09:03:02
System Manufacturer:       Dell Inc.
System Model:              Precision M65
System Type:               x64-based PC
Processor(s):              1 Processor(s) Installed.                            
  [01]: Intel64 Family 6 Model 30 Stepping 5 GenuineIntel ~1728 Mhz
BIOS Version:              Dell Inc. A07, 25/09/2011
Windows Directory:         C:\Windows
System Directory:          C:\Windows\system32
Boot Device:               \Device\HarddiskVolume2
System Locale:             en-ie;English (Ireland)
Input Locale:              en-ie;English (Ireland)
Time Zone:                 (UTC) Dublin, Edinburgh, Lisbon, London Total
Physical Memory:           8,116 MB
Available Physical Memory: 5,579 MB
Virtual Memory: Max Size:  16,231 MB
Virtual Memory: Available: 13,342 MB
Virtual Memory: In Use:    2,889 MB
Page File Location(s):     C:\pagefile.sys
Domain:                    WORKGROUP
Logon Server:              \\PC-Name
Hotfix(s):                 77 Hotfix(s) Installed.                           
  [01]: 982861                           
  [02]: KB971033
Posted in System Information | Tagged | Leave a comment

SQL Activity Monitor – Debug high CPU Usage for SQL Server

START -> All Programs -> Microsoft SQL Server 2008 R2 -> SQL Server Management Studio

Connect as normal and right-click on the SQL Server (top left) and go “Activity Monitor”.

The Activity Monitor will show the queries been run, the user, the computer been run from etc. This can help narrow down clients chewing up the SQL Database and/or show the databases and queries causing the most issues.

You can also right-click on a Process listed (by a user/computer) and go “Kill Process” to end the SQL client connection (useful if you want to force a user to logout of sql to free up a client license etc.).

————————————————–
Problem:
When I tried to launch the Activity Monitor on a Windows 7 x64 client, I got the error below:

The Activity Monitor is unable to execute queries against Server. Activity Monitor for this instance will be placed into a paused state.
Unable to  find SQL Server process ID on server (Microsoft.SqlServer.Management.ResourceMonitoring)
Solution:
cd \windows\system32
lodctr /R

Ref (#).

Alternative to Activity Monitor

Run the following queries:

#Stored Procedure for showing who is connected.
sp_who2;

#In the SPID column you can see the Session ID of the User. You can go kill spidno to kill that query.
kill SPIDNo.

#Stored Procedure for showing locks.
sp_lock;

Ref (#).

Posted in MS SQL Server | Tagged , | Leave a comment

Limit Memory Usage of store.exe – Microsoft Exchange

On the SBS 2011 Server, Microsoft Exchange isn’t used at all, yet store.exe uses over 700MB of memory (via Processes in Task Manager). It doesn’t look simple/possible to remove Exchange as part of a SBS Server. Instead, below shows how to limit its memory usage.

  • START -> (run/search) ADSIEDIT.msc
  • Right-Click on ADSI Edit and choose “Connect to…” and choose “Select a well known Naming Context: Configuration”.
  • Browse to: Configuraion -> CN=Configuration, -> CN=Services -> CN=Administrative Groups -> CN=Exchange Administrative Group -> CN=Servers -> CN=Servername -> CN=InformationStore -> (right-click) Properties
  • Scroll down for “msExchESEParamCacheSizeMax”
  • For Exchange 2010 (uses 32KB Pages). Example for 1GB Memory ->
    1GB = 1048576KB
    1048576 / 32 = 32768
  • *new* Set “msExchESEparamCacheSizeMin” to 256 (8MB of Memory).

 References:
Microsoft Article for Exchange 2000 & Microsoft Article for Exchange 2010 with reference to 32KB Page Size
http://eightwone.com/2010/03/25/limiting-exchange-2010-database-cache/
http://eightwone.com/2011/04/06/limiting-exchange-2010-sp1-database-cache/

Posted in Microsoft Exchange | Tagged | Comments Off

Allow Domain Admin to Login to SQLServer & have full Access

For some reason when SQLServer 2008 was installed/setup, admins were not added. Therefore when trying to login via SQL Management Studio, an error was obtained:

Login failed for user "username". (Microsoft SQL Server, Error: 18456)

I also didn’t know the “sa” password which would have let me login. I also didn’t have access to another admin account to test logging in under their account.

Start SQLServer in single-user mode

  • Open “SQL Server Configuration Manager” found in the Start Menu under Microsoft SQL Server 2008 R2 -> Configuration Tools.
  • Right-Click properties of the SQL instance you want to start in single user mode.
  • (Optional / Maybe not required) Change the Logon Account to a different one
  • Under Advanced and “Startup Parameters”, add in:
    ;-m
  • Click Apply. Restart the Service

Add Windows User as SQLServer Admin

  • Start a Command Prompt (right-click Run as Administrator!!)
  • Issue the following commands:
C:\Windows\system32>sqlcmd -E
1> exec sp_addsrvrolemember 'yourdomain\sburke', 'sysadmin';
2> go
1> exit
#For a named instance, go:
sqlcmd -E -S servername\instancename

Reference 

Posted in MS SQL Server | Tagged , , , | Leave a comment

Find all MSSQL Instances running, Connect and show Version

The diagram below, shows how you can use SQL Management Studio to connect to all instances of SQLServer running on your computer (as indicated in the Services list) and run a simple query to find out the exact version and service pack of SQLServer running.

In the SQL Management Studio, you can connect to a default instance by just typing the computername. To connect to a named (second) instance, you can connect to computername\instancename

Clicking “New Query” opens up the query window, and after typing ”select @@version” (intellisense prompting will appear) and pressing F5, runs the query and shows the SQLServer version and service pack.

Posted in MS SQL Server | Tagged , , | Leave a comment

Test MSSQL Server Connection

Method 1 – Command Prompt

telnet server 1433

#for named instances:
telnet server 1434

Method 2 – ODBC Data Source

Control Panel\Administrative Tools\Data Sources (ODBC)

 

Posted in MS SQL Server | Tagged , | Leave a comment