Office 365: Your Automatic Reply Settings Cannot Be Displayed

We recently decommissioned our aging Exchange 2007 Server and switched to using Office 365 for Email.

One quirk of the new system was that some users were having difficulty accessing their Out Of Office (Automatic Reply) Settings when using Outlook 2010 or 2013.

Your automatic reply settings cannot be displayed because the server is currently unavailable. Try again later.

There seemed to be no real pattern to this, and many suggestions were offered, non of which worked for us.

The simple solution I eventually stumbled upon was to ensure that “Exchange Web Services” was enabled for each Mailbox within Office 365: Active Users => UserName =>  Mail Settings => Email Apps

I have no idea why this is required, but it would appear that MAPI doesn’t support the full functionality of Exchange when using Outlook.

Advertisements

Watchguard Firebox M200: Unblocking WeTransfer

If you are blocking “Personal Network Storage and Backup” at your Watchguard Firewall, users won’t be able to access wetransfer.com. In their own support documentation here, WeTransfer suggested creating the following firewall exceptions:

*.wetransfer.com

*.we.tl

*.amazonaws.com

*.wetransfer.net

However, this still doesn’t prevent the Firebox from dropping the traffic. Below is a table of exceptions I had to create to get things working properly.

Wetransfer #1	Pattern Match	*.wetransfer.com/*
Wetransfer #2	Pattern Match	wetransfer.com/*
Wetransfer #3	Pattern Match	*.we.tl/*
Wetransfer #4	Pattern Match	we.tl/*
Wetransfer #5	Pattern Match	*.wetransfer.net/*
Wetransfer #6	Pattern Match	wetransfer.net/*
Wetransfer #7	Pattern Match	*.amazonaws.com/*

Using Regular Expressions, this can be simplified further:

Wetransfer #1	Regular Expression	(wetransfer\.((com)|(net)))
Wetransfer #2	Regular Expression	(^we.tl)|(\.we\.tl)
Wetransfer #3	Pattern Match	*.amazonaws.com/*

T-SQL: Using the HAVING clause

Here is a quick differentiation between the “WHERE” clause and the “HAVING” clause within an SQL Query.

Below is a basic query that will generate a list of the Customers based upon the number of orders they have placed.

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no order by orders desc

I have used the “WHERE” clause to filter records where the Order Number is greater than zero (you could also filter by order date).

However, if I am only interested in Customers that have placed more than 100 orders, I can use the HAVING clause as shown below.

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc

The HAVING clause allows me to apply a filter based upon a value created by the GROUP BY clause (eg SUM; COUNT; AVG), rather than values in the original table.

If you only need to list your top 10 customers, use the TOP clause or the LIMIT clause.

select top 10 count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc

select count(order_no) as Orders, slcust_no from windows_enquiry where order_no >0 group by slcust_no having COUNT(order_no) > 100 order by orders desc limit 10

T-SQL: Aggregate Functions; Date Functions; and Group By Statement Woes

When combining Aggregate Functions with Date / Time functions within an SQL Query, you may run into errors relating to the “Group By” statement, such as:

• “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”
• “Invalid Column Name”
• “Each GROUP BY expression must contain at least one column that is not an outer reference”

Here are some examples of how NOT to formulate the Query.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by

Msg 8120, Level 16, State 1, Line 2 Column ‘dbo.windows_quote.date_received’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Excluding “year_received” from the GROUP BY statement does not work, and neither does adding it.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, year_received

Msg 207, Level 16, State 1, Line 8 Invalid column name ‘year_received’.

I also tried to reference the field by index number..

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, 2

Msg 164, Level 15, State 1, Line 8 Each GROUP BY expression must contain at least one column that is not an outer reference.

Only by including the function itself within the GROUP BY statement could I get the Query to run properly.

UPDATE: The reason for this is because the “SELECT” clause is the very last part of the query to be evaluated. The query engine works through the statement as follows: FROM => WHERE => GROUP BY => SELECT. Therefore, the attribute “year_received” within the SELECT clause doesn’t exist when GROUP BY tries to reference it.

SELECT processed_by, year(date_received) as year_received, COUNT(date_sent) AS quotes_sent, AVG(DATEDIFF(day, date_received, date_sent)) AS sent_vs_received, AVG(DATEDIFF(day, date_req, date_sent)) AS sent_vs_required FROM dbo.windows_quote WHERE date_sent IS NOT NULL GROUP BY processed_by, year(date_received)

 

SQL Server File Locations for Default Instances

Each Instance of SQL Server has its own set of Executables (sqlservr.exe) and Data Files. The file locations for the default instance are different depending upon the version installed. Essentially, this is to allow multiple versions of SQL Server to happily co-existing on the same server.

The table below lists the default file locations (as they appear on my systems).

Version	Location of Database Engine	Additional Instances	Service Names
SQL Server 2000  & MSDE(v8)	C:\Program Files\Microsoft SQL Server\MSSQL	MSSQL$MyInstance	MSSQLSERVER; MSSQL$MyInstance
SQL Server 2005 (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2005 Express (v9)	C:\Program Files\Microsoft SQL Server\MSSQL.1\	MSSQL.2; MSSQL.3…
SQL Server 2008 (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER	MSSQL10.MyInstance	SQL Server (MSSQLSERVER); SQL Server (MyInstance)
SQL Server 2008 Express (v10)	C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS	MSSQL10.MyInstance
SQL Server 2008 R2 (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Reporting Services (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER	MSSQL10_50.MyInstance	SQL Server (MyInstance)
SQL Server 2008 R2 Express (v10.25; v10.5)	C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS	MSSQL10_50.MyInstance
SQL Server 2012 Express (v11)	C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS	MSSQL11.MyInstance	SQL Server (MyInstance)
SQL Server 2014 Express (v12)	C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS	MSSQL12.MyInstance	SQL Server (MyInstance)

 

Common Files
Additionally, multiple instances (within each version) share some common files.

Version	Location of Common Files	Notes
Common Files (2000)	C:\Program Files\Microsoft SQL Server\80\
Common Files (2005)	C:\Program Files\Microsoft SQL Server\90\	The 2005 installation also creates an  “80” folder.
Common Files (2008)	C:\Program Files\Microsoft SQL Server\100\
Common Files (2012)	C:\Program Files\Microsoft SQL Server\110\
Common Files (2014)	C:\Program Files\Microsoft SQL Server\120\

 

The Browser Service
There is only ever one copy of the SQL Browser Service running (and it always lives in the same folder). Normally, it will be automatically upgraded to whichever is the latest installed version of SQL Server.

The Browser Service listens for requests on UDP Port 1434 on behalf of all the installed SQL Server Instances that are installed on the Server (irrespective of version).

Version	Location of Browser Service	Notes
SQL Server 2000 Listener Service		Requires SQL Server 2000 SP3a
SQL Server 2005 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2005.90.5000.0
SQL Server 2008 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2007.100.5500.0
SQL Server 2008 R2 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2009.100.1600.1
SQL Server 2012 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2011.110.2100.60
SQL Server 2014 Browser Service	C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe	Version 2014.120.2000.8

 

64 Bit Windows
Within 64 Bit Windows, some files may be installed within “Program Files (86)” rather than “Program Files”.

SQL Server Compact Edition
These editions have very little in common with the SQL family as discussed in this document. SQL Server Compact Edition does not run as a service, nor is there any concept of database instances. The default installation directory is “C:\Program Files\Microsoft SQL Server Compact Edition\” and a file type of “sdf” is used for each data store.

XAMPP: Using MySQLDump to Backup MySQL on Windows Server 2003 and Windows 7

I couldn’t find anything specific for Backing up the MySQL Databases within XAMPP. There certainly isn’t a pre-configured routine available. In addition, MySQL Workbench (unlike its predecessor MySQL Administrator) does not provide any automated backup facility.

So, I decided to explore the use of “MySQLDump”, a command line routine that can be inserted into a batch file and executed as a “Scheduled Task”. “MySQLDump” is included as part of XAMPP and is found within “<path to XAMPP>\MySQL\bin\”.

Here is the basic syntax I used (from a command prompt):

<path to XAMPP>\MySQL\bin\MySQLDump –u root –p myrootpassword –result-file=”<path to Backup Folder>\MySQLDump.sql” –all-databases

I then copied this into a batch file (MySQLDump.bat) and used “System Tools => Scheduled Tasks” (Task Scheduler in Windows 7) to run the batch file each evening.

Of course, you must ensure that the “MySQLDump.sql” file itself is stored in a safe location and can be recovered in case of server failure / building loss.

Fitting USB3 into a DELL PowerEdge R510 Server

USB3 is not supported natively within Windows Server 2008 (or Windows 7), and therefore Dell has always seemed reluctant to fit USB3 hardware into its Servers.

To make matters worse, most USB3 cards require some sort of SATA Power from the main board, and Dell does not provide these connectors inside the chassis either. So, fitting a PCI Express USB3 card is a tricky proposition.

Fortunately, StarTech offer several USB3 Cards that do not require any additional power from the board (save that provided by the PCI Express slot itself). However, be aware that if you are intending to connect an external Backup Drive to the card, you will need to ensure that the device itself is powered.

I chose the StarTech PEXUSB3S42 Card. It provides three external USB 3.0 Ports (another port sits inside), and was a breeze to install.

The first image (below) shows my R510 Server prior to fitting the Card, and the second shot shows the new card in place.

For reasons explained above, it was neccesary to run the driver installation disk (provided in the box), and this resulted in a further reboot of the server.

My HP RDX Backup System is now connected to a much faster port and the backup times have reduced accordingly.

All in all, a successful upgrade.

PS, don’t forget to use a USB3 lead when connecting your External Drive, or you will still be running at USB2 speeds. 😉