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
field1 = source.field1,
field2 = source.field2,
WHEN NOT MATCHED THEN
INSERT ( idfield, field1, field2, ... )
VALUES ( 7, source.field1, source.field2, ... )
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
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 22.214.171.124 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.
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.
WHILE @@ROWCOUNT > 0
DELETE TOP(1000) FROM [dbo].[mytable]
WHERE timestampUtc < '2015-05-29'
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.
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.
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″.
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.
Quick reference on how to remove only the duplicate rows when attempting to add a unique index:
WITH custom AS (
SELECT [projectId], [zoneId], [type], [name], RN = ROW_NUMBER() OVER (PARTITION BY [projectId], [zoneId], [type], [name] ORDER BY [projectId])
DELETE FROM custom WHERE RN > 1
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:
Tired of the startup page, that never contains anything useful anyway and just takes time to load?
Go to Tools => Options => Environment => Startup and disable that page for good.
noah.org has an excellent writeup on this.
Link here, archive here.
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_addserver 'bar', local;
Restart the server and run the SELECT query again to verify the change.
More information available here, archived here.