Windows 10 annoyances

I've had a migration to Windows 10 forced upon me, and I've decided to keep track of all the little niggles I find with it. It feels like a half-finished change for the sake of change.

1) My Alt-V-D shortcut no longer works, to switch an Explorer window into Details view. Also the navigation within the window is constantly surprising me (disappointing being a better word) with how things never seem to behave quite like I would expect (being used to XP) e.g. when changing the sort order to most recent, XP takes me straight to the top (most recent) with what was the selected file being hidden off the visible part of the list of files, but using up/down arrows would take me back there to that file. With Win 10 the currently selected file remains with focus so I have to manually move the scroll bars back to the top. Ugh. And my preferred Tree View method of navigating folders doesn't seem to have the nodes expanded like I'm used to.

2) Scroll bars - why the did they reverse the meaning of the colours of bar itself and the rest of the control? I'm used to the light part being the movable bar, on a dark background. Now this is the other way around for no good reason, very confusing and irritating.

3) Start Menu  - horrible.'Nuff said.

4) Task Bar - I find it a lot easier to see what's running if the name of the program is there, now I have to stare and give it some real thought to work out what's open and which little picture means what. This is pathetic IMHO. No option to bring back the more easily read descriptions. And when I tried to eject my USB stick it was annoying to figure out which little squiggle I needed. I also have an option to eject my solid state main drive - I dread to think how things would seize up if I tried that!

5) Explorer again - select some files, then change the sort order..... whoops, there goes the selection lost into thin air. WTF?! What if you want to sort into date order, select the newest, then sort into alpha order for a quick look at other files with similar names to see if you're replacing older ones? No luck with Win 10 :(

6) Can I copy my music onto my phone and reorganise it? No..... I can copy files onto the SD card, but moving them into subfolders gives me a "Waiting.. (phone type) is busy" that never ends until I cancel it. And then the file has vanished, deleted! I now have to move files to my desktop, then move them back to the phone's subfolder, doing it in 2 steps!

7) With a Remote Desktop session fullscreen on my left screen, the taskbar is hidden. In the past I would click on the desktop on my right screen, and the taskbar would reappear. Win 10? No!

More to come, I'm sure........

Meanwhile Cortana is always imploring me to ask her anything - I'm constantly trying to resist asking the obvious question that she might find a bit insulting!

A musical interlude : Katalyst - Fusion

Isn't it annoying when you hear piece of music and Google doesn't know about the lyrics? And Shazam doesn't have it either? As a public service, here are the words that might lead you to Katalyst's track "Fusion". In return, if anyone can fill in the ????? blank, please comment with what it is, and preferably the source too! Thanks.

https://www.youtube.com/watch?v=fv-DLB9SfQw



When you're home - what would you put on your sound system?
You hear that? Yeah.

Don't give ?????? Give it to me!

Well as you can tell, it's not all funk.
It's a fusion.
Funk fusion.

This recipe includes includes a tablespoon of rock, and a tablespoon of funk, simmering over jazz.

It's a fusion - just just like it said, of all those type of different musics.

How to move a Firefox profile to reduce the size of your roaming profile

I found that logging into Windows was taking an age in the office, as my roaming profile had crept up to 3GB, which naturally takes some time to bring over the network. Rooting around to see what I could move to a local hard drive folder on C:, I noticed just how many files were under the Mozilla folder containing my Firefox and SeaMonkey profiles.

At first sight the advice on how to move a profile looked so involved that I gave up. One day later when things were quieter I tried again, and it's actually remarkably simple!

Here's what you need to know : the Firefox profile is just a folder full of files and subfolders. It's not quite as easy as moving this folder though, as the browser needs to know where to look. Firefox keeps its own record of where the profile is, and this is easily changed with the -p option.

To find the profile folder

Use the menu option : Help... Troubleshooting Information... and there you'll find a button [Show Folder]. Click this, and close the browser, keeping the folder open  (it should be somewhere under your profile if you're in the same boat that I was)

To create a new folder

Create the folder where you would like it to be on your C or D drive, etc.
Now you just have to tell the browser where it is. Use the Start.. Run.. method to open an application, and run : Firefox -p
The -p parameter tells Firefox (and SeaMonkey, also from Mozilla) to open in Profile Manager mode. Here you'll find a simple dialog that should need no further explanation really. Just add a new profile, point it to your new folder, and away you go - launch the browser and verify that your settings have vanished.

Copy the profile

Now, without the browser running, delete everything in the new folder, and copy everything from the original profile folder. Launch with -p again, select the new local profile and verify all your settings are back to normal.

Delete the old profile

If you're happy with the new profile, you can exit, go back in with -p and delete the old profile. You may now find that logging in to Windows is significantly quicker!

---

What about Chrome?

Chrome can be persuaded to use a profile in another location by changing the shortcut properties target and adding on your new path (you may need to change this in several locations depending upon how many ways you launch Chrome - e.g. start menu, pinned launcher, desktop icon.

"C:\Program Files\Google\Chrome\Application\chrome.exe" --disk-cache-dir="E:\YourPath" --user-data-dir="E:\YourPath"

However, this doesn't change the default call to the exe when a URL is launched from another app or the start menu's run box - Chrome is launched using the bare exe path on its own as stored in the Windows registry (HKEY_CLASSES_ROOT\http\shell\open\command and HKEY_CLASSES_ROOT\https\shell\open\command). I shy away from trying to modify this, as there are so many other references to chrome.exe in my registry (apart from the "chrome.exe,0" ones used in relation to grabbing the icon) that I'd rather not meddle and risk breaking it!

And another 'however' - having carefully updated my shortcut targets, I opened a www URL from the start menu run box, and up came Chrome using the old (default) profile location. Then, having closed it, I launched Chrome again from the icon pinned to the task bar and it had lost the settings I had made. The disk-cache-dir and user-data-dir param.s had vanished. Sigh!

There is another method, adding a string value called UserDataDir under
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Google\Chrome
but this overrides any use of the --user-data-dir command-line option so you won't be able to use different profiles.

Another approach (Win 7 and later) involves placing a symbolic link in the default location to redirect file access elsewhere - not much help for XP diehards.

And then there's the method of enabling a profile switch to Chrome's interface - browse to
chrome://flags/#enable-new-profile-management
 - but this means running Chrome first, before changing the profile. This is hopeless if some other app launches a URL and it's already using the default profile path before you get a chance to change it!

Needless to say, I'm not a great fan of Chrome and I don't use it unless I have to! Having this much disregard for a basic requirement is not a great indicator of software quality, is it?

Workaround for Excel's bug with too many cells selected

Sometimes you may find that Excel selects too many cells for you when you click in one cell and get several cells selected at once. After some research, I can confirm that it's quite repeatable and there are various ways to avoid Excel selecting too many cells at once.

The problem only occurs in Page View (not Normal View) and when the "White Space" (between the pages) is at the bottom of the window, with none of the cells of the next page visible.

So to get back to a single click selecting just one cell, you have a few options :

• Change view back to Normal View
• Zoom in/out or scroll, so that the White Space isn't in the screen's problem area
• Right-click instead - this always selects a single cell, but you'll need to then press Esc to dismiss the options, leaving the cell selected.Only a solution for a single cell selection, obviously - not so useful if you really want several specific cells selected without Excel giving you even more!
• Click on the grey space between pages and select the option to "Hide White Space". Your view of the pages will change somewhat - it's more of a grid and the pages don't look like real pages without the margins. But, if you can get used to it, the cell selection bug will have gone away.

Please leave a message if this helps!

Handy HTA to format SQL results as Table for email

Have you ever tried to email the results of a SQL query in SSMS and found that the format is a mess, just tab delimited text?  I've just searched for neat way to get the results to show as a table in a new email, and it seems that plenty of people have resorted to messing about in Programmability to wrangle SQL into creating HTML formatted text.

However, I remember from my experiments with PHP that if you cut-n-paste from a table on a webpage, the table format is preserved onto the email. I decided to play with a new HTA to show the clipboard contents....

... convert to HTML and show that in a new webpage (another HTA just for simple display purposes). From there, with any CSS tarting up you like, it can then be copied into an email, looking most spiffing. Shame that the tr:nth-child(odd/even) CSS doesn't work in HTA/MSIE for alternating row colours!

 The HTA below gives results like this :

 I hope you find this useful, either functionally or for educational purposes :)

Instructions

1. Ctrl-C your SSMS SQL query results
2. Double-click the HTA file/shortcut
3. Observe the clipboard data is correct, press the button
4. Marvel at the new HTA showing your data looking more presentable
5. Cut-n-paste to your email. Optional step: Allow yourself a smile.

With thanks to Notepad++ and the NppExport.."Copy HTML to clipboard" plugin for the syntax colouring, here is my HTA file :  Clip Table.hta

<head> <title>My SQL Table pretty-upper</title> <HTA:APPLICATION ID="oHTA" APPLICATIONNAME="HTA Test" SCROLL="no" SINGLEINSTANCE="yes" WINDOWSTATE="maximize" > </head> <SCRIPT LANGUAGE="javascript"> function NoContextMenu(){ alert("Sorry, but right-click is not allowed."); }; function replaceAll(find, replace, str) { return str.replace(new RegExp(find, 'g'), replace); }; function Window_onLoad(){ // Use the following line to disable right-click menu and prevent View Source document.oncontextmenu = new Function ( "NoContextMenu(); return false" ); // Resize and center window.resizeTo(screen.availWidth*.75, screen.availHeight*.75); window.moveTo(screen.availWidth*0.125, screen.availHeight*0.125); // Read Clipboard, convert tabs and newlines to HTML if (window.clipboardData) // Internet Explorer { txt_str1.value = window.clipboardData.getData("Text"); contents = window.clipboardData.getData("Text"); contents = replaceAll("\t","</TD><TD>",contents); // Tabs : table column boundaries contents = replaceAll("\r\n","</TD></TR>\r\n<TR><TD>",contents); // CrLf : new table row contents = "<TR><TD>" +contents +"</TD></TR></TABLE></HTML>"; // wrap up, except for TABLE/CSS }; }; function fn_run_button(){ txt_str1.value = contents; // Write a new smarter table in new HTA in %TEMP% folder var oWSS = new ActiveXObject("wscript.shell"); hta_file = oWSS.ExpandEnvironmentStrings("%TEMP%") + "\\testhta.hta"; // alert(hta_file); // line by line alternative : var a = escaped.split("\r\n"); for (var i = 0; i < oFil.length; i++) { *use* a[i] } // Write to file ( \x22 in the CSS is escape code for " ) var oFSO = new ActiveXObject("Scripting.FileSystemObject"); var oFil = oFSO.CreateTextFile(hta_file, true); oFil.WriteLine("<HTML><style type=\x22text/css\x22>"); oFil.WriteLine("#box-table { font-family: \x22Lucida Sans Unicode\x22, \x22Lucida Grande\x22, Sans-Serif;"); oFil.WriteLine(" font-size: 12px; text-align: left; border-collapse: collapse; border-top: 3px solid #9baff1; border-bottom: 3px solid #9baff1; }"); oFil.WriteLine("#box-table th { font-size: 13px; font-weight: normal; background: #b9c9fe; border-right: 2px solid #9baff1; "); oFil.WriteLine("border-left: 2px solid #9baff1; border-bottom: 2px solid #9baff1; color: #039; }"); oFil.WriteLine("#box-table td { border-right: 1px solid #aabcfe; border-left: 1px solid #aabcfe; border-bottom: 1px solid #aabcfe; color: #669; }"); oFil.WriteLine("</style> <table id=\x22box-table\x22 >"); oFil.WriteLine(contents); oFil.Close(); alert( "continue...") oWSS.run("cmd /C start " +hta_file, 0,false); oWSS = null; window.close(); }; window.onload=Window_onLoad; </SCRIPT> <BODY bgcolor="#90BBEE" > <font face="Lucida Sans"> <CENTER> <h1>SQL Tables made better for emailing</h1> This creates a new HTA from the clipboard data, which looks better and can be pasted into an email. <P><P> Clipboard <BR> <textarea name="txt_str1" cols="120" rows="40" ></textarea> <P> <input type="button" value="Create smarter table" name="run_button" onClick="fn_run_button()"> </CENTER> </BODY>

Do the SQL Shuffle

A little puzzle arose when I was looking an anonymising client data. At one point I wondered if simply shuffling around some fairly innocuous text fields would be enough to separate some items from other parts of the data. The answer was no, but I still spent some time in the evening figuring out how I'd do it if I needed to - remind me to Get A Life at some point! :)

How to shuffle?

Suppose you have a simple table with an ID field, and a text description. You want to shuffle the 'des' data around so that they all still exist, but in different rows to where they started.

ID			DES
11			line 11
12			line 12
13			line 13
14			line 14
15			line 15
16			line 16
17			line 17
21			line 21
23			line 23

You can play with this using a temporary table (don't forget to DROP it later) :

---

-- create temp test data
CREATE TABLE #DataTable (
ID int,
Des varchar(10) );

WITH DATA AS (
   SELECT 11 AS ID, 'line 11' AS dez UNION ALL
   SELECT 12, 'line 12' UNION ALL
   SELECT 13, 'line 13' UNION ALL
   SELECT 14, 'line 14' UNION ALL
   SELECT 15, 'line 15' UNION ALL
   SELECT 16, 'line 16' UNION ALL
   SELECT 17, 'line 17' UNION ALL
   SELECT 21, 'line 21' UNION ALL
   SELECT 23, 'line 23' )
INSERT INTO #DataTable (ID, Des)
SELECT * FROM DATA

--  Insert Shuffle code here :)

SELECT * FROM #DataTable;
DROP TABLE #DataTable;

---

After finding that Rand() only executes once per SQL statement and is therefore useless for a random value per row..... eventually I found that  Abs(CheckSum(NewID())) %90 is a handy way to get the kind of random values I was after. Deciding to try a multi-stage CTE approach, I got this far :

---

With d1 AS
(
SELECT  -- shove a random value in
  ID,
  Des,
  Abs(CheckSum(NewID())) % 90 as Randm
FROM #DataTable
),
d2 as
(
SELECT  -- now get what we need with order sequences
  ID as ID,
  Row_number() OVER (ORDER BY ID) AS OrigRow,
  Row_number() OVER (ORDER BY Randm) as NewRow,
  Des as ChangedDes
FROM d1
)
SELECT * FROM d2 ORDER BY OrigRow

---

This gave me the following, and you can play with it at
http://sqlfiddle.com/#!3/fcba5/4/0

ID	ORIGROW	NEWROW	CHANGEDDES
11	1	8	line 11
12	2	1	line 12
13	3	7	line 13
14	4	2	line 14
15	5	5	line 15
16	6	6	line 16
17	7	9	line 17
21	8	3	line 21
23	9	4	line 23

After some deep thought (very deep for that time of night) before retiring to my chamber, I came up with the idea of joining one row to another from the CTEs, from old order to new random order, and linking that to the original row being updated. :

---

-- Shuffle
With d1 AS
(
SELECT
  ID, Des, Abs(CheckSum(NewID())) % 90 as Randm
FROM #DataTable
),
d2 as
(
SELECT
  ID as ID,
  Row_number() OVER (ORDER BY ID) AS OrigRow,
  Row_number() OVER (ORDER BY Randm) as NewRow,
  Des as ChangedDes
FROM d1
)
UPDATE #DataTable
SET Des =
 (SELECT swap.ChangedDes
  FROM d2 swap
   INNER JOIN d2 old ON
     swap.NewRow = old.OrigRow
  WHERE old.ID = #DataTable.ID
 )
FROM #DataTable

---

That does the job nicely, and runs quickly enough too. See it in action at
http://sqlfiddle.com/#!3/fcba5/6/0

If you have any better ways to do it, I'm all ears. Comments welcomed, as always!

Anonymising Client Data

(I'm back working again after many long months unemployed, so I should be able to post little odds and ends here again)

If you want to demo your application using real data that you've brought back to the office from a client site, it's rather important to remove real names and addresses, etc., from the data before letting other parties set eyes on it. It won't inspire much confidence to show a potential client another client's data!

If you're using MS SQL Server then you may have the AdventureWorks database to hand, chock full of random names that can be used to overwrite the names and addresses that you wish to remove. Getting a random number to work in a simple UPDATE statement is a bit tricky because the RAND() is only evaluated once - so you'll get a different result each time but all the rows will be set the same :(

So I called upon my favourite CTE know-how and cooked up the following. It's a cascading multi-step CTE affair. First it retrieves the Max(ContactID)from the Contact table, so that the 0-to-1 random number can be scaled up to the full range of the table from which we'll be grabbing random names.

Next, we create a "ChangeData1" temporary table of IDs for the table we wish to change, which will also contain a random row ID from the Contacts table (a starting point to look one up) thanks to getting a random number from the checksum of a NewID(), modulus the maximum row number.

At this point you can then use the next stage of the CTEs to grab a real row number (using TOP 1 where the row ID > the random number) and JOIN to that row, and you'll have full access to the AdventureWorks row to change a number of fields in one UPDATE.

Or you can have this simpler example, where ChangeData2 becomes a list of IDs and one new field from the randomly selected AdventureWorks row.

---

USE [MyDatabase];

With AW_Names as  -- Get Maximum ID for range of random numbers
( SELECT Max(ContactID) as MaxID
  FROM [AdventureWorks].[Person].[Contact]
),
ChangeData1 as  -- Get ID numbers for table we want to change, and a random num for each row
(
 SELECT
   ID,
   ABS(CHECKSUM(NewId())) % (SELECT MaxID FROM AW_Names) as Rando
 FROM myTable
),
ChangeData2 as  -- Get ID numbers for table we want to change, and a name from AdventureWorks for each random field
(
 SELECT
   ID AS ChangeID,
   ( SELECT TOP 1 LastName FROM [AdventureWorks].[Person].[Contact]
     WHERE ContactID > Rando
   ) As NewField
 FROM ChangeData1
)
UPDATE myTable
SET
  Lastname = ChangeData2.NewField
FROM ChangeData2 WHERE ChangeData2.ChangeID=ID

---

It seems to run acceptably fast, even a more complex version that updates numerous fields at once, and randomises a Date of Birth column too. Thousands of records safely cleansed in the blink of an eye. This has become my starting point for a long script that will cleanse a number of tables.

Alternatively, you could take a look at Anonimatron, which was my first port of call before I decided to go it alone...

Anonimatron

This free software (a Java .jar) can whizz through tables and fields that you specify in an XML config file, using a variety of new data types (crazy ancient Roman names for example!) and can remember how it did it so that future runs will change the same fields to the same new values if you wish to keep certain parts of your data familiar.

See the author's blog for a guide : http://rolfje.wordpress.com/2013/11/03/anonimatron-quick-start/

Having updated my Java installation (so that .jar programs.can run), I then downloaded Anonimatron from http://sourceforge.net/projects/anonimatron/

I used the folder within the zip download to unzip into a folder :
C:\Program Files\Anonimatron\anonimatron-1.7\
I took a look at the batch file supplied, and converted it to Windows line feeds, and ran it. It complained about memory, not being able to "reserve enough space for object heap". Not a great intro to the joys of Java, but an opportunity to learn...

I eventually figured out what the -Xmx2G parameter was doing, so I tried -Xmx1600m instead, and got that much working. Keeping the original batch safe, I was now using a copy called ano.bat.

Realising that I wanted another batch to play with (to call the main one) I set up ano2.bat to call the first batch as required :
ano.bat -config config.xml -synonyms synonyms.xml

This was enough to do the trick, after I got it connecting to MS SQL, which called for the installation of a free 'JDBC' driver download and install.
Grab Microsoft JDBC Driver 4.0 for SQL Server  from http://www.microsoft.com/en-us/download/details.aspx?id=11774
and then place a copy of sqljdbc4.jar from that installation into
C:\Program Files\Anonimatron\anonimatron-1.7\jdbcdrivers
alongside the MySql and PostgreSQL ones.
If you wish to use integratedSecurity=true in the connection string, you'll need to copy sqljdbc_auth.dll to the main C:\Program Files\Anonimatron\anonimatron-1.7\  folder (or sort out the library path issues!)

Example

If this command line works :
sqlcmd -S myServer\test -d myDatabase -Q "SELECT ID,LastName FROM myTable"
then this XML file config.xml should work - in this case changing a name column to wacky Life of Brian names like Augusnus, Dibocus, Ebodabogubinus etc.!

---

<?xml version="1.0" encoding="UTF-8"?>
<configuration jdbcurl="jdbc:sqlserver://myServer;instanceName=test;DatabaseName=myDatabase;integratedSecurity=true;">

  <table name="myTable">
    <column name="LastName" type="ROMAN_NAME" />
  </table>

</configuration>

---

The command parameter -configexample is used to display help, running for me via the batch file:
ano.bat -configexample

This reveals the following possibilities for anonymising your data fields (and you can also write you own 'anonymizers' too, to bolt in)  :

DEFAULT_TYPES
java.lang.String
java.util.Date

CUSTOM_TYPES
RANDOMCHARACTERS
DUTCHBANKACCOUNT
UUID
ELVEN_NAME
ROMAN_NAME
PREFETCHCHARACTERS
RANDOMDIGITS
EMAIL_ADDRESS
STRING
BURGERSERVICENUMMER

The author is responsive to requests for assistance on his blog, or via the dev website, so it's worth considering Anonimatron if it seems to suit your needs. I'm going down the route of rolling my own, any other suggestions are welcomed if you'd like to comment. Thanks.