Windows | boltblog

Wiping an (Azure) SQL Server database, deleting all tables

Unknownbolt | 2015-10-28

Just for personal reference USE [mydb] GO WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE=’FOREIGN KEY’)) BEGIN DECLARE @sql0 NVARCHAR(2000) SELECT TOP 1 @sql0=(‘ALTER TABLE [‘ + TABLE_SCHEMA + ‘].[‘ + TABLE_NAME + ‘] DROP CONSTRAINT [‘ + CONSTRAINT_NAME + ‘]’) FROM information_schema.table_constraints WHERE CONSTRAINT_TYPE = ‘FOREIGN KEY’ EXEC (@sql0) PRINT @sql0 END GO DECLARE @sql1 NVARCHAR(2000) […]

Hardening IIS and scanning for SSL-related vulnerabilities – IISCrypto

Unknownbolt | 2015-09-11

I stumbled upon this great tool to set all the relevant registry entries (WTF?) to allow only the cryptographic algorithms that are considered secure to be negotiated with the server. I archived it here (CLI version), for my personal reference, but if you prefer (and you should, frankly) you can get it from the author’s […]

Using MERGE to insert/update in SQL Server

Unknownbolt | 2015-06-19

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 […]

MSSQL Mass Delete

Unknownbolt | 2015-05-29

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 […]

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

Unknownbolt | 2015-04-05

wlsetup-all-en_GB wlsetup-all-en_USSupport 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 […]

Deleting duplicates in SQL Server before adding a Unique Index

Unknownbolt | 2015-03-27

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]) FROM [mydata].[dbo].[Properties] ) DELETE FROM custom WHERE RN > 1

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

Unknownbolt | 2015-03-20

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 […]

Disable the startup page in Vistual Studio 2013 Community Edition (and probably all others)

Unknownbolt | 2015-03-20

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.

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

Unknownbolt | 2015-03-03

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 […]

Weakening Windows Server 2012

Unknownbolt | 2015-02-03

Sometimes, when I’m just screwing around with some software, Windows security just gets in my way. Here are the things I wanted gone this time, and where to find them: Strong password enforcement and password aging: Administrative tools => Local Security Policy (secpol.msc) => Account Policies => Password Policy Ctrl+alt+del to login: Administrative tools => […]