Hi all

I had a table for metadata that I had 25 rows to add to. Simple solution, right? Nope.

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '5', 'red, Likely, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '6', 'red, Almost Certain, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '7', 'red, Unlikely, Catastrophic');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '8', 'yellow, Occasional, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '9', 'yellow, Likely, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '10', 'yellow, Almost Certain, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '11', 'yellow, Rare, Catastrophic');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '12', 'yellow, Unlikely, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '13', 'yellow, Occasional, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '14', 'yellow, Likely, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '15', 'yellow, Almost Certain, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '16', 'yellow, Rare, Major');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '17', 'yellow, Unlikely, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '18', 'yellow, Occasiona,l, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '19', 'yellow, Likely, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '20', 'yellow, Rare, Moderate');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '21', 'green, Unlikely, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '22', 'green, Occasional, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '23', 'green, Rare, Minor');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '24', 'cyan, Moderate, Negligible');
INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '25', 'cyan, Rare, Negligible');

That only greets me with “Extra characters after SQL”. Argh. Trying multiple value lines fails too:

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc) VALUES('risk', '5', 'red, Likely, Major'),
('risk', '6', 'red, Almost Certain, Moderate');
('risk', '7', 'red, Unlikely, Catastrophic');
('risk', '8', 'yellow, Occasional, Major');
('risk', '9', 'yellow, Likely, Moderate');
('risk', '10', 'yellow, Almost Certain, Minor');
('risk', '11', 'yellow, Rare, Catastrophic');
('risk', '12', 'yellow, Unlikely, Major');
('risk', '13', 'yellow, Occasional, Moderate');
('risk', '14', 'yellow, Likely, Minor');
('risk', '15', 'yellow, Almost Certain, Negligible');
('risk', '16', 'yellow, Rare, Major');
('risk', '17', 'yellow, Unlikely, Moderate');
('risk', '18', 'yellow, Occasiona,l, Minor');
('risk', '19', 'yellow, Likely, Negligible');
('risk', '20', 'yellow, Rare, Moderate');
('risk', '21', 'green, Unlikely, Minor');
('risk', '22', 'green, Occasional, Negligible');
('risk', '23', 'green, Rare, Minor');
('risk', '24', 'cyan, Moderate, Negligible');
('risk', '25', 'cyan, Rare, Negligible');

“Expected ;” it says. Then after a bit of googling, I get the idea to use subqueries with unions instead! Yeah… uh, no.

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc)
SELECT 'risk', '5', 'red, Likely, Major'
UNION SELECT 'risk', '6', 'red, Almost Certain, Moderate'
UNION SELECT 'risk', '7', 'red, Unlikely, Catastrophic'
UNION SELECT 'risk', '8', 'yellow, Occasional, Major'
UNION SELECT 'risk', '9', 'yellow, Likely, Moderate'
UNION SELECT 'risk', '10', 'yellow, Almost Certain, Minor'
UNION SELECT 'risk', '11', 'yellow, Rare, Catastrophic'
UNION SELECT 'risk', '12', 'yellow, Unlikely, Major'
UNION SELECT 'risk', '13', 'yellow, Occasional, Moderate'
UNION SELECT 'risk', '14', 'yellow, Likely, Minor'
UNION SELECT 'risk', '15', 'yellow, Almost Certain, Negligible'
UNION SELECT 'risk', '16', 'yellow, Rare, Major'
UNION SELECT 'risk', '17', 'yellow, Unlikely, Moderate'
UNION SELECT 'risk', '18', 'yellow, Occasiona,l, Minor'
UNION SELECT 'risk', '19', 'yellow, Likely, Negligible'
UNION SELECT 'risk', '20', 'yellow, Rare, Moderate'
UNION SELECT 'risk', '21', 'green, Unlikely, Minor'
UNION SELECT 'risk', '22', 'green, Occasional, Negligible'
UNION SELECT 'risk', '23', 'green, Rare, Minor'
UNION SELECT 'risk', '24', 'cyan, Moderate, Negligible'
UNION SELECT 'risk', '25', 'cyan, Rare, Negligible';

I get a nice and unhelpful “syntax error” pointing to the first subquery. Huh, guess you need a table to select from for unions. let’s give that a spin:

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc)
SELECT 'risk', '5', 'red, Likely, Major' FROM dual
UNION SELECT 'risk', '6', 'red, Almost Certain, Moderate' FROM dual
UNION SELECT 'risk', '7', 'red, Unlikely, Catastrophic' FROM dual
UNION SELECT 'risk', '8', 'yellow, Occasional, Major' FROM dual
UNION SELECT 'risk', '9', 'yellow, Likely, Moderate' FROM dual
UNION SELECT 'risk', '10', 'yellow, Almost Certain, Minor' FROM dual
UNION SELECT 'risk', '11', 'yellow, Rare, Catastrophic' FROM dual
UNION SELECT 'risk', '12', 'yellow, Unlikely, Major' FROM dual
UNION SELECT 'risk', '13', 'yellow, Occasional, Moderate' FROM dual
UNION SELECT 'risk', '14', 'yellow, Likely, Minor' FROM dual
UNION SELECT 'risk', '15', 'yellow, Almost Certain, Negligible' FROM dual
UNION SELECT 'risk', '16', 'yellow, Rare, Major' FROM dual
UNION SELECT 'risk', '17', 'yellow, Unlikely, Moderate' FROM dual
UNION SELECT 'risk', '18', 'yellow, Occasiona,l, Minor' FROM dual
UNION SELECT 'risk', '19', 'yellow, Likely, Negligible' FROM dual
UNION SELECT 'risk', '20', 'yellow, Rare, Moderate' FROM dual
UNION SELECT 'risk', '21', 'green, Unlikely, Minor' FROM dual
UNION SELECT 'risk', '22', 'green, Occasional, Negligible' FROM dual
UNION SELECT 'risk', '23', 'green, Rare, Minor' FROM dual
UNION SELECT 'risk', '24', 'cyan, Moderate, Negligible' FROM dual
UNION SELECT 'risk', '25', 'cyan, Rare, Negligible' FROM dual

Nope. At this point it would’ve been quicker to just enter the damn data. So let’s try our unioned subquery within ANOTHER subquery, giving the fields from the first query names so we can refer to them:

INSERT INTO metadata (metadata_group, metadata_text, metadata_desc)
SELECT metadata_group, metadata_text, metadata_desc FROM (
 SELECT 'risk' AS metadata_group, '5' as metadata_text, 'red, Likely, Major' as metadata_desc FROM dual
 UNION SELECT 'risk', '6', 'red, Almost Certain, Moderate' FROM dual
 UNION SELECT 'risk', '7', 'red, Unlikely, Catastrophic' FROM dual
 UNION SELECT 'risk', '8', 'yellow, Occasional, Major' FROM dual
 UNION SELECT 'risk', '9', 'yellow, Likely, Moderate' FROM dual
 UNION SELECT 'risk', '10', 'yellow, Almost Certain, Minor' FROM dual
 UNION SELECT 'risk', '11', 'yellow, Rare, Catastrophic' FROM dual
 UNION SELECT 'risk', '12', 'yellow, Unlikely, Major' FROM dual
 UNION SELECT 'risk', '13', 'yellow, Occasional, Moderate' FROM dual
 UNION SELECT 'risk', '14', 'yellow, Likely, Minor' FROM dual
 UNION SELECT 'risk', '15', 'yellow, Almost Certain, Negligible' FROM dual
 UNION SELECT 'risk', '16', 'yellow, Rare, Major' FROM dual
 UNION SELECT 'risk', '17', 'yellow, Unlikely, Moderate' FROM dual
 UNION SELECT 'risk', '18', 'yellow, Occasiona,l, Minor' FROM dual
 UNION SELECT 'risk', '19', 'yellow, Likely, Negligible' FROM dual
 UNION SELECT 'risk', '20', 'yellow, Rare, Moderate' FROM dual
 UNION SELECT 'risk', '21', 'green, Unlikely, Minor' FROM dual
 UNION SELECT 'risk', '22', 'green, Occasional, Negligible' FROM dual
 UNION SELECT 'risk', '23', 'green, Rare, Minor' FROM dual
 UNION SELECT 'risk', '24', 'cyan, Moderate, Negligible' FROM dual
 UNION SELECT 'risk', '25', 'cyan, Rare, Negligible' FROM dual
);

Success!

So there you have it.  Oh, and to create those queries?  Just dump the data into Notepad++ and do a replace with regular expressions.

Greetings

At work we run Windows Server 2008 R2 servers, with a bunch running published applications through RemoteApp and Desktop Connections. Great guns, especially once we figured out that we could drop them into our Windows 7 computers’ Start Menu and drop shortcuts on the desktop. Now it really feels like you’re running it on your computer! Neat.

The best part of it is being able to regulate who sees what through AD groups – allowing us to restrict access to apps that we have limited licenses for. So, naturally, every so often we get a request to provide access to an application secured like this, and we’re usually more than happy to provide access depending on licensing levels. So we add them to the group and get them to log off, then back on again… except the newly-provided app doesn’t appear! So we’d log in and manually refresh the feed by digging through the Control Panel… not fun, especially since the icons like to not appear all at once so we go to click it and end up clicking on Programs and Features instead.

So ho hi, ho hi, off Googling goes I. I find this post from a fellow by the name of “JayJay19671967″ who found the solution after finding questions of this nature nearly a year old with no response. It was one line:

rundll32 tsworkspace,TaskUpdateWorkspaces

That’s it. Takes all of 5 seconds. It’s in our login script, in the same section where it sets the connection up in the first place.

Greetings, and welcome to the first of my “Work Smarter, Not Harder” posts.

A bane in our side whenever we deploy a laptop is forgetting to set up the VPN connection – or the RAS phonebook mysteriously disappearing when we image a particular machine. This usually means trying to catch the poor bugger at a (usually remote) site to set it up manually. I, being a systems admin and therefore inherently lazy, beg to differ.

As it turns out, the RAS phonebook is stored in C:\ProgramData\Microsoft\Network\Connections\Pbk and is called “rasphone.pbk” – to deploy this VPN connection, simply create the folder and copy the already set up file in.

I hope this has proved to be helpful for you.