Windows 10 Update 1709 woes

I tried many different methods of attack, but on every occasion, Windows 10 Update 1709 continued to fail.

The Error Message was a follows:

0x8007001F – 0x3000D

The installation failed in the FIRST_BOOT phase with an error during MIGRATE_DATE operation

Windows Update Installer 020.jpg

Now, I need to point out that my Workstation is connected to a Domain, and that we use Roaming User Profiles. So, if you don’t have this scenario, the solution won’t work for you unfortunately.

Bearing in mind the Error Message, my assumption was that it had something to do with the User Profiles.

In order to get the Workstation to update, I did the following:

[1] Remove ALL of the Roaming Profiles from the Local Workstation using “Control Panel => Advanced System Settings => User Profiles”

Only the Administrator Account (and the Default Profile) should remain.

Untitled 003

[2] Disconnect the Workstation from the Domain (make sure you know the Local Machine Administrator Password)

[3] Using “Regedit”, navigate to “HKLM\Software\Microsoft\Windows NT\CurrentVersion\ProfileList\” and remove any remaining Domain Accounts from the list. Don’t remove the Administrator Account (or any other local system accounts).

Untitled 001.jpg

[4] Optionally, clear out any “dead” account folders from within “C:\users”.

If you try the update again, you should find that it works.

When everything is complete, you can re-join the machine to the Domain.

Advertisements

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

170522 Office 365 Exchange Web Services 002
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.

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
SQL Having 002

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
SQL Having 001

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.