Using MERGE to insert/update in SQL Server

After finding various useless references on how to use the MERGE statement in SQL Server 2008, I found this one that I could use, so I copied it here for personal reference:

MERGE tablename AS target
USING (VALUES ('new value', 'different value'))
    AS source (field1, field2)
    ON target.idfield = 7
WHEN MATCHED THEN
    UPDATE SET
    field1 = source.field1,
    field2 = source.field2,
    ...
WHEN NOT MATCHED THEN
    INSERT ( idfield, field1, field2, ... )
    VALUES ( 7,  source.field1, source.field2, ... )

How to use a gateway outside of the local subnet on Linux

An interesting situation occurs when you have a very limited range of public IP addresses and want to pass all traffic through a common gateway without “wasting” a public IP on that – you may find yourself wanting to use a gateway with a private IP even through your network uses public ones.

So, in my case I was dealing with a /29 subnet, providing space for only 6 hosts, all of which were needed to host various services. The gateway, as a result of this, could not occupy one of the precious IP’s. The solution was to put the gateway on a static, but local, IP address, and create a route to it in /etc/network/interfaces.

iface eth0 inet static
    address 1.2.3.4
    netmask 255.255.255.248
    up   route add -host 172.30.0.1 dev eth0
    up   route add -net 0.0.0.0 netmask 0.0.0.0 gw 172.30.0.1 dev eth0
    down route del -net 0.0.0.0 netmask 0.0.0.0 gw 172.30.0.1 dev eth0
    down route del -host 172.30.0.1 dev eth0

While 1.2.3.4 has obviously been replaced for privacy reasons, this is pretty much the setup. The gateway is at 172.30.0.1, a private IP. Each time the interface is brought up or down, a default route to the gateway is automatically added and removed as appropriate.

MSSQL Mass Delete

To avoid holding up everything else when deleting massive amounts of data from an SQL Server table, you can use the TOP() function to delete the data in chunks.

SELECT 1
WHILE @@ROWCOUNT > 0
BEGIN
DELETE TOP(1000) FROM [dbo].[mytable]
WHERE timestampUtc < '2015-05-29'
END

The “SELECT 1″ primes the @@ROWCOUNT variable, so the while loop will run. Yes, there are indeed prettier ways of doing this, but it doesn’t really matter.

dovecot: master: Error: systemd listens on port 143, but it’s not configured in Dovecot. Closing.

Upgrading a mail server from Debian Wheezy (7) to Debian Jessie (8), the following errors were observed in the mail.error log:

dovecot: master: Error: systemd listens on port 143, but it’s not configured in Dovecot. Closing.
dovecot: master: Error: systemd listens on port 993, but it’s not configured in Dovecot. Closing

Looking for solutions, I found two pages talking about it:
https://sowhatisthesolution.wordpress.com/2015/04/27/upgrading-dovecot-debian-wheezy-to-jessie/ (local archive)
http://appbead.com/blog/fix-errors-reported-in-syslog-for-debian-8-jessie.html (local archive)

Seeing as this install of dovecot wasn’t supposed to be started by socket connections, but rather run all the time, and that it wasn’t actually listening to POP3 and IMAP, but rather their SSL equivalents, we simply disabled the dovecot.socket config, as suggested by the first of the two links:

# systemctl disable dovecot.socket
Removed symlink to /etc/systemd/system/sockets.target.wants/dovecot.socket.

Installing Windows Live Essentials on Windows XP – Error: OnCatalogResult: 0x80190194

Support for Windows XP from Microsoft has long since ended, and although Windows Update still kind of works (you have to run the “Express” option a few times, and install Microsoft Update, before “Custom” works), attempts to install Windows Live Essentials fail miserably with the cryptic error message “OnCatalogResult: 0x80190194″.

wlsetup

Downloading the installer manually was suggested on a few sites, however, downloading and running wlsetup-web.exe from Microsoft gave me the same error message on install. Cue the offline installer. However, the previous links to download the offline installer, such as http://g.live.com/1rewlive3/en/wlsetup-all.exe, are now dead. The Internet Archive came to the rescue, and I was able to download it from there.

I’ve archived my copy of it here, for future reference. Feel free to grab it, or use the archive.

Taking a screenshot of an entire page with Firefox, saved as a .png

I was recently asked for a way to do this, and since I figured more people might like to know, here’s how:
Go to Tools => Web Developer => Developer Toolbar (check it), or simply press Shift+F2, then at the command line that appears in the bottom of the browser window, type “screenshot –fullpage” followed by an optional filename ending in .png.

A screenshot of the entire page will end up in your default downloads directory.

Here’s an example shot of the OmniOS installation guide:
Screen Shot 2015-03-20 at 21.18.16

Renaming an MSSQL instance (Invalid Urn filter on server level)

If you rename a host on which MSSQL is running, the MSSQL server will not be renamed internally, causing the error message “Invalid Urn filter on server level” to pop up. What this means, in this case, is that the connected SQL server doesn’t match the name the client has for it.

You can check this by running the following query:

SELECT @@SERVERNAME AS 'Server Name';

To correct the server name, from (for the sake of example) “foo” to “bar”, run this query:

sp_dropserver 'foo';
GO
sp_addserver 'bar', local;
GO

Restart the server and run the SELECT query again to verify the change.

More information available here, archived here.