Two Things

Gaming and Programming.. maybe programming for some games, who knows. Either way, I'm a geek, right?

32 bit COM DLL in 64-bit SQL 2008+

Old VB6-written COM object that needs to be accessed in queries from a 64-bit SQL 2012 database.  There may be many ways to do this, or this might be the only way.. and involves some registry magic.

1. Write a wrapper in C#

A simple class to load up the object (assuming the DLL is registered on the server of course), using System.Activator to spin up an instance of the DLL and a bit of reflection to interact with it.  This is out of scope for this article but I can share if needed.

2. Compile the wrapper as a DLL using csc.

csc is inside your framework folder somewhere.  Create the DLL using the .cs file you wrote above.  Example:  "csc /target:library mywrapper.cs"

3. Add the assembly into SQL Server.

CREATE ASSEMBLY MyCOMObject FROM 'C:\somewhere\mywrapper.dll' WITH PERMISSION_SET = UNSAFE
GO

In this case, "UNSAFE" is SQL's version of "Full Trust".  The default is "SAFE", and "EXTERNAL_ACCESS" does not give enough permissions for this DLL to interface with other DLLs (our COM object) so "UNSAFE" is the proper level of trust for this.

4. Registry magic to make the assembly work.

1. run regedit
2. find the name of your COM object  --it should be at HKLM\Software\Classes\NameOfCOMObject
3. Copy the Clsid value, brackets and all.  e.g. {C869B444-52AB-3EEE-3332-555B021ABCD2}
4. Navigate to HKEY_CLASSES_ROOT\Wow6432Node\CLSID\YourClassIDFromStep3
5. Add a string value, named AppID with value of the ClassID
6. Navigate to  HKEY_CLASSES_ROOT\Wow6432Node\AppID\
7. Add a new Key named the same as the ClassID
8. Inside that new Key that you just added, add a string value named DllSurrogate with no value
9. Navigate to HKEY_LOCAL_MACHINE\Software\Classes\AppID\
10. Create a new Key named the same as the ClassID.  No additional values are needed.

5. Grant Permissions to local SQL accounts.

1. run dcomcnfg
2. Expand Component Services / Computers / right click My Computer and Properties
3. On the COM Security tab, Access Permissions > Edit Default
4. Add MSSQLSERVER and SQL Server Distributed Relay Client with LocalAccess, then OK
5. On the COM Security tab, Launch and Activation Permissions > Edit Default
6. Add MSSQLSERVER and SQL Server Distributed Relay Client with Local Launch and Activation, then OK

6. Create a Function and Test.

CREATE FUNCTION CLR_Test(@input nvarchar(MAX))
RETURNS NVARCHAR(MAX)
AS EXTERNAL NAME MyCOMObject.NameSpace.FunctionName
GO

SELECT dbo.CLR_Test('whatever')

If you get an access denied error, check your Event Viewer / System Log to see which user account is accessing the COM object.  You will need to repeat step 5 to grant them the Component Services permissions.

7. Lastly

For each database that you register your assembly to, you need to turn on the TRUSTWORTHY flag.

ALTER database SET TRUSTWORTHY ON

You are encouraged to do your own due diligence to see what all of these options do and whether they work for you in your environment.  This is just how I solved it in my environment.  It was a pain but, hey, after the pain comes the pleasure, and being able to use an ancient COM library inside a SQL statement does feel good, even if it's cheating.  

Late Night LUA Reflections - WildStar Style

Small post.  Couple things I had to bang my head against before figuring out while working on my WildStar addons this weekend..

OnSave()

This little goody is what you can use to persist data from one load to the next.  It couples with OnRestore() which is nice and all, but only if OnSave() works.  It seems that when you do /reloadui, OnSave() might get called, but if you don't already have a saved settings file on your HD, it won't actually save anything.  A file is created when you log out to Character Select (or log out completely).  From that point forward, you can /reloadui all you want to and the settings file will update.  This is probably a bug.

ICCommLib

This is the addon communication library, not meant for humans, just for addons to pass data back and forth.  It has quite a few bugs, or just poor design choices.  One really can't tell with Carbine and Wildstar which is which.  One problem, after you connect to a channel, there's no way to leave it.  You will continue to get updates from that channel until some undetermined time when GC throws you out or maybe after you reload and the addon gets registered again.  In the interim you have to trap the channel at the message received level to make sure you want to process that message.

A second problem, when you send messages you get no echo of it, that part is fine, really.  Just need something like Rover or a second account to make sure data is passing.  The problem comes in that you have to wait about 3 seconds after joining the channel otherwise the message will fail to send.

LUA Uses References. Who Knew?

Well probably a lot of people knew.  I've started messing around in WildStar's addon universe, which is based in LUA.  I haven't touched LUA since the original release of World of Warcraft when I wrote a crowd-control addon to keep track of my warlock's mesmerizing targets.  So WildStar uses LUA as well.

I had made an assumption since LUA is a scripting language that it was not very object-oriented and things like values and references weren't really relevant.

Take this bit of code:

local TableOne = {}
TableOne.Fruit = "Apple"
local TableTwo = TableOne
TableTwo.Fruit = "Banana"
Print(TableOne.Fruit)

As a regular scripting language I expected "Apple" since I thought line 3 made an independent copy of the table.  I was wrong though, it's a reference pointer, and the printout will be "Banana."

If you want to have a copy of a table that is separate from the original you have to write a function that does a shallow or deep copy as you need it in your specific script.  LUA doesn't have an innate table copy function because there are apparently several nuances that you have to be aware of.

 

Picking a Gaming Mouse

Since the dawn of time I've used a Kensington ExpertMouse as my input device of choice.  They've gone through some minor changes over the years but the basic design is the same:  a "large comfortable ball" that is the size of a pool (billiard) ball that you control with your middle 3 fingers, and four buttons accessible via thumb and little finger.  Coupled with the proper armrest, this is heaven.  You don't have to pick it up to reposition it.  You don't need a mousepad.  Unlike trackballs that you control with your thumb, the Kensington EM being controlled by your index, middle, ring, or any combination thereof, provides an unmatched level of precise control.

Unfortunately the "majority" of the gaming market uses a mouse, so the concept of a "gaming trackball" has not made it past manufacturer's drawing boards.  What defines a "gaming trackball?"  It might be subjective, but to me it would be one that not only offers switchable DPI but the most important element:  lots of accessible buttons.  My Kensington with its 4 buttons and questionable programmability doesn't cut it.

Playing games that don't require a lot of fast-paced movement, you can get by with a trackball.  Shooters and some of the newer MMORPGs however, require a level of control that makes using a trackball questionable.  With that in mind, I set out to reluctantly add a mouse to my desk.

There are a ton of reviews on each of these mice, so I will keep my comments rather brief about each - and about what I like or dislike about them.  I'd wholeheartedly advise reading more reviews and, most importantly, get your hands on one before spending money.  Like keybindings, choosing a mouse is subjective and something simple like the shape of your hand or how you try to hold a mouse might sway you into a different model.

MadCatz Cyborg R.A.T Series

I picked up a R.A.T9 a couple of years ago for my FPS games.  It is completely customizable and looks like a little Transformer on your desk.  You can change its grips, adjust its spacing, and adjust its weight to perfectly work in your hand.  Side buttons allow for DPS switching (sniper mode) so you can get fine-control when lining up a long range headshot then go back to speedy movement. 

Pro: Customizing.  Extra rechargeable battery to swap in (the 9 is wireless).  Comfy.  Unique looks.
Con:  Not many buttons, but for Shooters this isn't a huge deal.  Also, many complaints about the software and MadCatz products in general.

Would I buy it again?  Before this weekend I would have said yes if I only played shooters.  MMORPG, no.   But after this weekend, just no.

Logitech 502 Proteus Core

I think this is Logitech's answer to the Cyborg RAT problem.  It is somewhat customizable but not to the level of the RAT.  Has the same three buttons on the thumb, one positioned perfectly for a sniper mode DPI switch.  Has weight change.  But overall it just feels a ton better than the RAT.  The buttons feel more responsive and their placement is better.  If I needed a few-button Shooter mouse, the Proteus Core would be on my desk right now, no doubt.

Razer Naga

Looking at MMORPG mouse choices, the Naga seems to be on everyone's list, but I think that might only be because it was the first(?) to feature a panel of 12 buttons for your thumb.  12 friggin buttons.  On your thumb.  Support has been questionable, build quality has been questionable, the software to program the buttons has been questionable.  I did not buy one though based on ergonomic comparisons.

Namely, the 12 buttons are on a smooth plane.  This is good that you don't callous your thumb, but it's bad in figuring out which button you're about to press.  The latter is the ultimate reason I passed this one over, but it might only be because I compared it to..

Logitech G600

An obvious answer to the Naga, the G600 is Logitech's 12-thumb-button creation, only they used their brains a bit.  As their current slogan goes, "Science Wins."  The 12 buttons on the side of the G600 are arranged in two sets of 6, with each set being tilted inward to form kind of a valley for your thumb to rest in.  Add to that a nub on one of the 6 buttons and you can most certainly tell what your thumb is about to press.

In addition, they added a third mouse button up top.  Most all mice have two - left and right.  G600 has a third for your ring finger.  By default it is used as a "G-Shift" which allows you to program two sets of commands onto the buttons and "shift" between them with a simple click of your finger.  That turns this "19 button mouse" (left + middle(x3) + right + 12 side buttons +2 under the scroller) into 38 buttons.  They advertise it as 20/40 but one of them is being used as the shifter so they can't say that.  Also the two under the scroller are kinda useless, IMO.  Still, LOTS OF BUTTONS.

Pro:  LOTS OF BUTTONS.  More ergo for your thumb.  Shifting = MORE BUTTONS.  Ring finger button can be used for something else.  Programmability of all Logitech products using their LUA-based software is wonderful.  Did I mention the buttons?  You could type on this thing if you really wanted to.  The wired cord is braided.

Con: Overall ergonomics.  Wired mouse.

I couldn't find a spot to place my thumb that made all 12 buttons accessible during combat.  The natural resting place was inside the valley of one of the sets of 6, making the other valley harder to reach.  Also, my ring finger wanted to be the right mouse button - something in my head said "click the RMB" and my ring finger did just that.  I could have remapped things so that ring was the RMB and the ex-RMB was something else, but I found that I was also clicking it when moving the mouse around trying to push the thumb buttons.

Over time I imagine I could get used to it, but did I really need 40 buttons on a mouse?  Would I remember where all 40 things were?  Probably not.

Logitech G602 and 700S

Going to lump the G602 and G700S together because they are very similar.  These are a more traditional ergonomic mouse design but with extra buttons.  They are sculpted to be easy to hold, and give your thumb a place to rest.  Each has multiple thumb buttons (NOT as many as the G600), each has multiple buttons next to the LMB, and each is wireless.

Thumb Buttons:  602 has 6, 700S has 4.  The two extra are "nice" but not a dealmaker for me.. why later.

Extra top buttons next to LMB:  602 has 2, 700S has 3. The 602's are kinda flat and squishy.  The 700S buttons are angled and raised enough to let you rest your finger on the middle one if you wish and easily click the top/bottom one from there.

Scroll Wheel:  The 602 feels a little on the "cheap" side.  The 700S has a nicer side-to-side click response (three-way button).

Wirelessness:  As a bonus, the 700S has a (non-braided) USB charging cable for its rechargeable (replaceable too) AA battery, and while it is plugged in it disables wireless and acts as a wired mouse.  So in a pinch if your battery dies, plug it in and keep going, just tethered.

Overall:  Prefer the 700S.  The buttons could feel a little more responsive (as it is they are kinda squishy too) but it works fine.

Others

Corsair, SteelSeries, and a few others have gaming mouse offerings but as they did not have any demos at Best Buy, I can't offer any opinion on them.

Logitech Programmability

There are some glitches with the Logitech products depending on what you do with them.  There's a certain glitch that will cause your mouse to double-click instead of single-click if you try to customize it in a certain way.  If you run into this, though, there's ways around it.

There's also ways of adding "G-Shift" abilities to any Logitech product.  Want your 6 button mouse to have 30 functions in World of Warcraft?  It's possible.  Want to "G-shift" your mouse from your G13 gamepad or Gxxx keyboard?  It's possible.  This is why I ultimately picked a mouse with 9 usable buttons over one with 18.

The folks over on the Logitech forums work wonders through scripting advice and whatnot.  And by folks I mean the users.  The official Logitech people aren't much help.  The aforementioned double-click bug has apparently been around since 2012 and they haven't done anything about it.  if it weren't for the userbase coming up with their own fix, I would imagine Logi would be hurting.

Strafing in Games - WASD sucks

Background

For years and years I played games that could easily be controlled by a keyboard alone - using the mouse to only interact with the UI.  I blame EverQuest for this back in 1998 - as it started me down the path of movement using my right hand on the Numpad, abilities using my left hand on the keyboard.  And so my brain grew used to "movement on the right, abilities on the left".  I never really got into strafing because PVP was never a huge interest and, while it can be argued that strafing is good in PVE as well in some cases, I still never saw a need.

The few FPS games I played forced me to move my hand from the numpad over to the traditional WASD key arrangement for movement, using the mouse to move the camera around.  While awkward, this worked out alright because I could bind the few abilities I needed to activate onto my mouse, or in the immediate vicinity of WASD and didn't give it another thought.  I strafed a little.  It was alright.

Enter Wildstar.  A MMORPG where moving AND activating abilities at the same time is for the most part required.  No problem.  My old Numpad habit works out fine - mobs don't dance around quite as much as players do and I can dodge and dash around their telegraphs.  Problem is, even on a Wildstar PVE server there are some PVP elements - some of which look fun.  PVP generally requires strafing - more specific, circle-strafing.  Wildstar also has a lot of things to bind.  While it is certainly not like EverQuest 2 or World of Warcraft where you can literally use dozens of spells during the course of combat, there are 8 abilities plus a few slots (gadget, medishot) plus some special movement (sprint, jump, directional dash) PLUS any extra stuff you jam on your action bars and put a hotkey on that can come into play while in combat.

Strafing + Abilities = Finger Gymnastics

There are some hardware choices you could make to allow you to strafe and activate abilities at the same time.  There are gaming mice that have multiple buttons.  There are special gamepads or keyboards that have a good arrangement of programmable keys.  But I want to stick to a regular keyboard and a regular mouse for this.

Strafing locks down one of the fingers on your left hand.  Your left hand is what also activates your abilities.  This presents an ergonomic and logistic problem in games like Wildstar where you have a minimum of 10 things that you could need to press while simultaneously strafing.

Since I've been a keyboard jockey in MMOs forever, and only dabbled in using WASD + mouse in FPS games, I have to retrain my brain from "movement = right hand, abilities = left hand" to being more ambidextrous.  I then started looking at keybinding arrangements - both defaults provided by the game developers, and customizations that people use.  A question came to my brain:  "How do you hit [that key] while strafing?"

Ergonomics and Home Key Arrangement

I decided to put my nerd hat on and map out some popular home-key arrangements to see what options they provided for placement of other abilities.  Home Keys are where you rest your fingers normally on the keyboard.  When typing, you're taught to rest your left hand's fingers on ASDF.  When controlling a game, however, you usually shift your hand somewhere else.

I say "Home Key" merely as a convenient phrase, but I hope everyone knows that under normal circumstances you only "Home" three of your fingers: on the Left, Forward, and Right key.  Your little finger usually rests somewhere else or hangs out in mid-air waiting to be useful.

I started with the defacto WASD that is engrained in the market.  It is the default arrangement in most every game.  Gaming keyboard developers give special attention to WASD in the form of different coloured keys, bumpers, backlighting, etc.  I then looked at ESDF as an option that I had seen in many players' posts - the reasoning is that F usually has a nub so it is easier to "find" when blindly throwing your hands down.  I then looked at WERD, which I found interesting after reviewing This Article and finally I started from scratch - ASDF and modified it into a movement pattern by shifting a finger up to EADF.

Testing Assumptions

I made a few assumptions for this.

1. Strafe Left + Strafe Right would be mapped to the regular Left and Right movement keys.  Turning is "usually" done with the mouse in this style of gameplay, so having separate strafing keys (Q and E on a WASD configuration by default) take up valuable key space.  This frees up two of the keys. 

2. The left thumb will hit Space and Left Alt without any gymnastics, so those two keys are not included in my results.

3. This is only for strafing.  While forward + backward are keybound and take up space, Forward movement is assumed to be done also with the mouse, and backward movement (backpedaling) is less common.  While moving forward via the mouse, all keys reachable by your left hand are viable.  Not needed in this study.

4. Lastly, I did not go any further than 6, T, G, and B on the key rows.  My reason is simple - I use an ergonomic keyboard that splits there.  If you use a flat keyboard you might be able to extend your reach to 7, Y, H, and N in some configurations.  If so, congratulations - maybe RDFG or RSFG is worth looking at for you as a possible home-key arrangement.

Testing

To create my results, I simply took each home-key arrangement and looked at what keys were easy to reach and comfortable while strafing in a specific direction.  If I could easily reach the key with no strain or unusual effort, it is marked "OK" on the diagrams. For keys marked as "MEH" on the diagrams, they are usable, but with mild strain or hand contortion to get to them.  Any keys that led to bumping fingers into each other, stretching too far, or otherwise playing Twister with my hand are marked as "NO."

For each home-key arrangement, I created a diagram for Left Strafe, Right Strafe, and a Combined diagram.  The Combined Diagram takes the key availability from both Left and Right strafe and merges them together.  A key viable for both (OK) is viable on the Combined.  If a key is questionable (MEH) on either Left or Right, it is MEH on the Combined.  And finally if a key is not viable (NO) on either Left or Right, it is not viable on the Combined.

Finally, I tallied up the keys at the bottom of the diagram.  NOTE:  The extreme left-hand keys from tilde (~) down to Control were not counted in the summary.  They are bindable but are usually for things like push-to-talk, targeting, modifiers, etc.  As an aside, you may consider making Caps Lock into another Shift key if you find your little finger keeps hitting it like some other people writing this article.

Strafing Flexibility vs NASCAR

If you look at the Combined diagrams you see all of the viable (OK) keys that you would want to place your mission-critical abilities.  Secondary-but-needed abilities you could place into the less-viable (MEH) keys.  Finally you'd need to put things on the NO keys that you are comfortable not using in combat, or knowing that you will have to stop strafing or maybe change direction of your strafe to use them.

NASCAR?  Yeh okay.  If you only want to strafe in one direction at all times, your options for viable keys increase.  I know some players that "only strafe right" or "only strafe left" which has its merits for this reason, but I would think in PVP if someone figures out you only circle them clockwise, they're going to adjust their tactics when they face you.

Just Get to It

For the results, you can examine the diagrams individually or in one huge side-by-side graphic.  I am sure someone will disagree with some part of this.  Their hand is bigger/smaller than mine.  They use a different keyboard.  Their mouse has 100 buttons controlled by a neural interface.  Their metacarpal bones are double-jointed.  Gravity works differently in their area.  They aren't over 40 years old.  Whatever.

Your Mileage May Vary.  This is all Subjective.  This is Presented Without Warranty.  The Opinions Expressed Herein are Just That: Opinions.

WASD Diagram

ESDF Diagram

WERD Diagram

EADF Diagram 1 (Left Strafe = Little Finger)

EADF Diagram 2 (Left Strafe = Ring Finger)

Side-By-Side Diagrams (Kinda Big)

Conclusion 1: WASD Sucks.. Mostly.

In most all cases, WASD simply sucks.  The only case when it sucks less is a Left-Strafe Only situation, but even then it still sort of sucks because five of your keys are uncomfortable.  The main reason it sucks is that it is parked too close to the edge of the keyboard and cuts off your little finger from being more useful.  Only your thumb gets to be lazy here.  Little finger can reach 1, Q, A, and Z just as easily as it can shift/tab/ctrl, but WASD removes this ability from the table.

And if you're trying to be flexible and use both strafes, WASD offers the least number of overall viable keys, and several uncomfortable ones at that.  Really, other than "it's always been done this way and that's how DOOM taught me to play in 1994", I see no real reason to use WASD at all.

Conclusion 2: To the Left

In all cases, strafing Left provides more keys than strafing Right. 

Conclusion 3: Stretch it Out

For my derived home-key arrangement, EADF, I let my fingers relax some by spreading out the left/right movement slightly, providing a gap and a convenient spot for another viable combat key.  I looked at keeping the ring finger on Left, as well as using the little finger on Left.  That's why there's two diagrams for EADF above.  While I prefer the number of keys provided by the little finger on Left, it locks up those side keys (Tab, etc) that might be useful.  But overall this feels more comfortable to my hand than squishing fingers all up.

Those with bigger hands might also appreciate a spread-out arrangement.  One player said he has to use EADG.  That's a bit too spread out for me to test, but mostly because my ergo keyboard splits at the G so H is not viable for me to be testing.

Conclusion 4: Dance Lessons

If you can strafe-hop from left to right comfortably enough and remember that ability #1 is only usable when strafing left, and ability #2 is only available when strafing right, you'll have no worries and you can use whatever arrangement you like, since you will have the whole slate of keys open for your use.

Final Conclusion

I don't have one.  It takes time to adjust to any new key arrangement, and since this is new for me it will take a week or so for my muscle memory to kick in.  Personally, after reviewing which keys are viable and which keys are not viable, I like EADF 2 or ESDF.  ESDF looks attractive if you're going to do Left-Strafe Only (one additional comfy key over EADF) but the non-viable W might a downer for me.  On EADF, the non-viable Z isn't a big deal during combat because for my hand, it seems more comfortable to move a finger up to W than down to Z.  We'll see though.

It's up to you to decide if you want to change your WASD habit and try something new.  It's also up to you to decide WHAT to put in these key spots.  I'm certainly not going to start making recommendations there.

But overall, I hope someone stumbling across this finds it useful.

Post Mortem

It's been a month or so since I wrote this.  I settled on ESDF because I didn't have to move my hand to type.  I also added a Logitech 700s to my desk, allowing me a bunch of buttons on my right hand.  There's still a couple of abilities that I have bound around ESDF but for the most part, my separation of concerns is still in place - movement with one hand, ability with the other.

SQL Connect Failure with Windows Authentication

Login failed.  The login is from an untrusted domain and cannot be used with Windows authentication.  (Microsoft SQL Server, Error: 18452)

That's how it starts.  The headache.

The Confusion

I work in a two-domain environment, and as far as I could tell the trusts were in place, my accounts on both domains were admin-level and I wasn't fat-fingering anything since I was trying to connect with Windows Authentication.

I was obviously logged into Windows successfully. 

I could connect to shared folders located on the server, which uses Windows Authentication.

I could connect to the server via Remote Desktop and connect to SQL on that server using Windows Authentication.

So what gives?  Why wouldn't my local SQL Management Studio (and Visual Studio too) connect to the server's SQL instance using Windows Authentication?

Not-So-Useful Troubleshooting

Looking on the server's Event Log, I found two entries for the authentication attempt:

EventID 17806 ERROR: SSPI handshake failed with error code 0xc0000413, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.

EventID 18452 INFORMATION: Login failed.  The login is from an untrusted domain and cannot be used with Windows Authentication.

They both seem to imply a trust issue, but the domains trust each other. 

Googling around didn't help much.  Lots of advice and things to look at and try, all of which I did.

Here's what I did to solve my situation, ignoring the 3 hours of conversation with our IT guys and surfing google.

Useful Troubleshooting

Using nltest in debug mode  (nltest /dbflag:0x2080ffff)  (NOTE: must run cmd as an administrator for this to work)

I captured the login and reviewed the log file (located in %sysroot%/Windows/Debug/netlogon.log) then turned off nltest debug to stop the spam collection (nltest /dbflag:0x0)

The log file showed something interesting - the windows account that was connecting to SQL was NOT the account that I was logged into Windows with, and it did not seem to be authenticating with the domain controller.

Huh?

Well, I use a VPN to connect to this network, and the account that I use to connect the VPN is on Domain A, but my server (and my windows login) is on Domain B.  I guess the VPN account is being used when I try to connect to the remote server via the VPN.

Long story short - my account on Domain A did not have the right to Authenticate on the server that I was trying to connect to.

The Fix

Went into AD, enabled Advanced Features (under View), found the server in the computer list, Properties, Security, added my Domain A account with Allow to Authenticate checked.

Problem solved.

 

SQL 2012 Import - Unknown column type conversion

Importing a flat file (CSV) into MS SQL 2012 should be a straightforward task, one would think.  That is, until you run into a brick wall that reads "Found 1 unknown column type conversion(s).  You are only allowed to save the package."

Thanks, SQL.

The problem, if you bother to look at the details of the offending column (it will be the one with the big red X next to it), is that SQL has no idea how to convert from the incoming column - usually DT_STR - into your desired destination column.  For me, my destination was a datetime2 column which seems to be DT_TIMESTAMP2 in the SQL conversion world.

All of this is powered by an XML file located called DtwTypeConversion.xml located at either C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn or C:\Program Files\Microsoft SQL Server\110\DTS\Binn  (older/newer version of SQL might use it also but they'd be in a different subfolder obviously.. if in doubt, search for the file).

Inside that file if you look down for a SourceType of DT_STR you will see all of the types it can convert to, and the "steps" for converting them.  You will see an entry for DT_TIMESTAMP but not one for DT_TIMESTAMP2.

Add one.

The conversion looks like you can configure several steps if you need to convert to intermediary datatypes before getting to your end goal.  For me, one step was enough.  I just copied the entry for DT_TIMESTAMP and changed it to DT_TIMESTAMP2.

After that, you will get the regular yellow exclamation point instead of the big red x, and you can run your import and move on to solving the next error.

Adventures in Interviewing #2: Shuffle This

Interviewing for a programming position usually involves some sort of programming test - either verbally, or on a whiteboard, or occasionally something more interesting.  I've dug around my emails for some of the more interesting (or challenging) ones.  I don't expect to get much feedback from these, but felt like posting them anyway.

This is the second in the series, which came from the posted job position itself, not from an interview.  They asked that you tackle this problem and send it in along with your resume.  Unfortunately they were in California and this was not a remote position - but at the time I was bored and the challenge looked interesting so I went ahead and completed it.

A client has an existing web-based system for delivering a test containing multiple choice questions. They ask that you modify this code so that the test questions are delivered in a random order every time the test is attempted. Furthermore, the order in which the answers to the questions are presented should also be randomized.

 

The existing framework for creating and displaying a test has been provided. Your task is to comprehend the client's code and data structure then implement the RandomizeTest function to perform the randomization of the question and answer order.

 

Please explain your work and thought process.

 

Some background on the project from the client that may affect your solution:

 

-There are over 600 of these tests deployed to hundreds of thousands of users
-There are never more then 20 questions or so per test, each with no more than 6 answers but the code should be able to handle an arbitrary number or both questions and answers
-This code is maintained by several developers in different organizations
-The code is only required to work in all modern browsers

And the existing code:

<html>
<head>
<script>

//definition of the test object
function Test(aryQuestions, aryChoices, aryAnswers){
	this.questions = aryQuestions;
	this.choices = aryChoices;
	this.answers = aryAnswers;
}


//displays the sample test in the browser with the correct answer highlighted
function WriteTest() {

	var tst = CreateSampleTest();	
	tst = RandomizeTest(tst);
	document.write("<table border=0 cellspacing=3 cellpadding=3><form name=test id=test>");
	for (i=0; i < tst.questions.length; i++) {
		document.write("<tr><td valign=top>&nbsp;</td>")
		document.write("<td><p>" + (i+1) + ".&nbsp;" + tst.questions[i]);
		for (j=0; j < tst.choices[i].length; j++) {
			var correctcount = 0;
			var setblue = "";
			for (k=0; k<tst.answers[i].length; k++) {
				if (tst.answers[i][k] == 1) {
					correctcount++;
				}
			}
			if (tst.answers[i][j] == 1) {
				setblue="class=blue"
			}
			if (correctcount == 1) {
				document.write("<br><input type=radio name=check"+i+" value="+j+" onclick='return false;'>");
				document.write("<span "+setblue+">"+tst.choices[i][j]+"</span>");
			} 
			else {
				document.write("<br><input type=checkbox name=check"+i+" value="+j+" onclick='return false;'>");
				document.write("<span "+setblue+">"+tst.choices[i][j]+"</span>");
			}
		}
		document.write("</td></tr><tr><td colspan=2><br></td></tr>");
	}
	document.write('</form></table>');
}

function CreateSampleTest(){

	var questions = [
		"What can you find in our office?",
		"All of our employees are expected to work no more than ____ hours per week.",
		"The end users of our products number in the _________",
		"Our company is a (choose all that apply):",
		"Tim likes to wear:"
	];

	var choices = [
		[
			"Dart Board",
			"Ping Pong Table",
			"Cubicles",
			"Laptops with dual monitors",
			"TPS reports, ummm yeah"
		],
		[
			"80",
			"40",
			"50",
			"60"
		],
		[
			"Tens",
			"Hundreds",
			"Thousands",
			"Millions",
			"Billions"
		],
		[
			"Great place to work",
			"Respected leader in its field",
			"Place where people don't matter, just results"
		],
		[
			"Capri pants",
			"Goth attire",
			"Sport coat",
			"T-shirt and shorts"
		]
	];

	var answers = [
		[1,1,0,1,0],
		[0,1,0,0],
		[0,0,0,1,0],
		[1,1,0],
		[0,0,0,1,0]
	];	
	
	return new Test(questions, choices, answers);
}

/***************************************************************/
//YOUR CODE HERE

function RandomizeTest(tstObject) {

}

/***************************************************************/

</script>
<style type="text/css">
.blue {font-size : 14px; font-family : arial, helvetica, sans-serif; color : #336897; font-weight:bold;}
</style>
</head>
<body>
<script>
WriteTest();
</script>
</body>
</html>

And that's it. We get to fill in the blank.

The Thought Process

Well as an overview it looks like we need to randomize the questions, randomize the choices of answers, and make sure that the "correct" answers stay in sync with the randomized choices.

Easiest way I could think to do that would be to use some extra arrays to hold the "new" order of the questions and choices after they've been randomized.

The Shuffler

Since this seemed like a real world challenge, to come up with a way to do the actual randomizing I adopted a real world method.. I hit StackOverflow until I found a method that I liked rather than re-inventing the wheel.  I settled on this method from http://dzone.com/snippets/array-shuffle-javascript

function shuffle(o){
    for(var j, x, i = o.length; i; j = parseInt(Math.random() * i), x = o[--i], o[i] = o[j], o[j] = x);
    return o;
};

Seemed simple enough.

Arrays

Since we'd be doing some shuffling around of items that needed to stay in sync, I figured we'd need a few holding arrays to maintain where the "original" array items ended up after they got randomized.  To do that, I crafted a little helper function to build an array of integers matching the number of entries in the original array -- example, for a five item array of choices or questions, the holding array would contain [0,1,2,3,4] before it was shuffled.

//create an array of integers, zero-based, up to n elements
function buildZeroIntArray(n) {
	var retArr = [];
	if (isNaN(n)) return retArr;
	for (var i = 0; i < n; i++) {
		retArr.push(i.toString());
	}
	return retArr;
}

After shuffling this helper array -- e.g. [3,0,4,1,2] it's just a matter of reconstructing the question, choices, and correct answer arrays into this new randomized order

The Result

So here's the final RandomizeTest function, using the above two helper methods and some verbose variable names!

//RandomizeTest accepts and returns a Test object. The questions in the returned object should be in a random order.
//The order of the choices within each question should also be randomized.
function RandomizeTest(tstObject){	
	//step 1.. before scrambling the questions we need to know how many there are and store their original order
	var numQuestions = tstObject.questions.length;
	var questionOrder = buildZeroIntArray(numQuestions);
	//for 5 questions we'll have the array [0,1,2,3,4]
	
	//step 2.. shuffle the order
	questionOrder = shuffle(questionOrder);
	//now we have our randomized set of questions.. e.g. [3,0,1,4,2]
	
	//step 3.. rebuild the questions array according to our new order
	var newQuestions = [];
	for (var q = 0; q < numQuestions; q++) {
		newQuestions.push(tstObject.questions[questionOrder[q]]);
	}
	tstObject.questions = newQuestions;
	
	//step 4.. for each question we need to retrieve the set of answers, shuffle the answers, and make sure the correct answers remain in sync.

	//define a couple of holding arrays
	var newChoices = [];
	var newAnswers = [];
	
	for (var q = 0; q < numQuestions; q++) {
		//store the current question # to save some array lookups
		var currentQuestion = questionOrder[q];
		//as with the question shuffle, build an array to shuffle the choices
		var numChoices = tstObject.choices[currentQuestion].length;
		var choiceOrder = buildZeroIntArray(numChoices);
		choiceOrder = shuffle(choiceOrder);

		//define temporary arrays to hold the shuffled choices & answers
		var holdChoices = [];
		var holdAnswers = [];
		for (var a = 0; a < numChoices; a++) {
			holdChoices.push(tstObject.choices[currentQuestion][choiceOrder[a]]);
			holdAnswers.push(tstObject.answers[currentQuestion][choiceOrder[a]]);
		}		
		
		//done with this question.. populate the parent holding array with the newly ordered choices
		newChoices.push(holdChoices);
		newAnswers.push(holdAnswers);
	}
	
	//populate the choices & answers with the reordered arrays
	tstObject.choices = newChoices;
	tstObject.answers = newAnswers;
	
	return tstObject;
}

Pretty as pie. If pie was a case sensitive client-side scripting language.

Adventures in Interviewing #1: The Coin Jar

Interviewing for a programming position usually involves some sort of programming test - either verbally, or on a whiteboard, or occasionally something more interesting.  I've dug around my emails for some of the more interesting (or challenging) ones.  I don't expect to get much feedback from these, but felt like posting them anyway.

This is the first in the series.

Implement a coin jar in C#. The coin jar will only accept US coinage and has a volume of 32 fluid ounces. Additionally, the jar has a counter to keep track of the total amount of money collected and has the ability to reset the count back to $0.00.

Evaluation

My first consideration is how complex to make this thing.  They did not give any guidance about time frames to complete it, whether they wanted actual code that would compile versus a text file of pseudocode, whether they wanted unit tests, etc.  The above was the sole beginning and end of it.  Since this was the third question in an overall coding "test", I decided to give them a working solution that would compile and met the requirements - and nothing further (no unit tests).  In hindsight, the lack of unit tests might have been my undoing for this specific position, but without any feedback from them on my chosen solution, I really don't know.

So now we need to figure out some stuff.  Since this seems to be a volume question, we need to know the volume of standard US coins, and the volume of the jar.  Mister Google helps us out there.

US Mint Coin Specifications
Fluid Ounce to Cubic Centimeter Calculator

Of course there's other considerations too - the capacity of the jar would also be influenced by its physical dimensions and whether the jar cylindrical or rectangular.  For the coins themselves, how the coins fall into the jar, and the mixture of coins have an impact on just how many the jar could "hold" before it became full.  But I think that these considerations are likely out of scope for an interviewing question, so I took the perfect storm approach - the coins would fall perfectly and magically arrange themselves inside a jar that changes its shape magically so that all available space would be used, as long as 32 fluid ounces of coins were involved.

Bases covered, time to start coding.  I decided to make this a console application, as it would be less code and could be sent back to them as a text document that they could cut/paste into VStudio or whatever to compile.

The Classes

So obviously we need a couple of classes here - a Coin class, and a Jar class.

public class Coin {
    public string CoinName { get; set; }
    public double Diameter { get; set; }
    public double Thickness { get; set; }
    public int CoinValue { get; set; }

    //Note: Volume is the cylindrical volume of the coin.  BoxVolume is the effective space occupied by the coin.
    //Not using Volume in this implementation but for sake of completeness it's present.
    public double Volume { get { return Math.Pow((Diameter / 2), 2) * Math.PI * Thickness; } }
    public double BoxVolume { get { return Diameter * Diameter * Thickness; } }

    public Coin(string name, double diameter, double thickness, int coinValue) {
      CoinName = name;
      Diameter = diameter;
      Thickness = thickness;
      CoinValue = coinValue;
   }
}

The class is pretty straight forward - a few relevant properties and a couple of calculations. I included two Volume calculations - one for the actual cylindrical calculation and a more simple "box volume" to see how much space the coin as a whole takes up. The actual volume would be useful if going into granular determination of how the coins mesh together, but for this project in my magic jar, I just wanted their basic volume.

public class Jar {
    //define the volume of the jar in cubic millimeters
    //source.. http://www.asknumbers.com/CubicCentimeterToOunce.aspx
    const double UsFluidOunceInCubicMM = 29573.5296;

    private double _volumeRemaining;
    private int _totalCoinValue;

    //storing the individual coins would allow for future functionality such as
    //listing a count by coin type, removing all quarters, etc.
    private List _bankedCoins;

    public Jar(int fluidOunces) {
      _volumeRemaining = fluidOunces * UsFluidOunceInCubicMM;
      _totalCoinValue = 0;
      _bankedCoins = new List();
    }

    public bool AddCoin(Coin coinToAdd) {
      //check to see if there's enough space in the jar to hold the coin
      if (coinToAdd.BoxVolume < _volumeRemaining) {
        _bankedCoins.Add(coinToAdd);
        _totalCoinValue += coinToAdd.CoinValue;
        _volumeRemaining -= coinToAdd.BoxVolume;
        return true;
      }
      return false;
    }

    //return the current value of the jar's contents
    public int GetCurrentValue() {
      return _totalCoinValue;
    }

    //empty the jar of coins
    public void EmptyTheJar() {
      _totalCoinValue = 0;
      _bankedCoins.Clear();
    }
  }

For the Jar class, again, some basic properties and a magic number to convert fluid ounces into cubic millimeters (the online calculator was in centimeters, but changed it to millimeters here to match the dimensions of the coins.. the alternative was to convert the coins into cubic centimeters.. six of one, half dozen the other..).  As an unneeded caveat, I added a collection to the Jar class to store individual coins, in case someone wanted to remove all of a particular coin type, or count up the dimes, etc.  The rest is pretty self-explanatory.. a method to get the current value inside the jar, and empty it -- the latter a requirement of the problem.

The Program

All that's left is to write the program to initialize everything and print out a few test statements.

class Program {

    //define the allowed coins.  using a dictionary for easier lookup.
    private static Dictionary<int, Coin> _allowedCoins = new Dictionary<int, Coin>();
    private static Jar _theCoinJar;

    static void Main(string[] args) {
      //define the jar for 32 fluid ounces
      _theCoinJar = new Jar(32);

      //define the coins.. just in case the dimensions change
      //current source.. http://www.usmint.gov/about_the_mint/?action=coin_specifications
      _allowedCoins.Add(1, new Coin("Penny", 19.05, 1.55, 1));
      _allowedCoins.Add(5, new Coin("Nickel", 21.21, 1.95, 5));
      _allowedCoins.Add(10, new Coin("Dime", 17.91, 1.35, 10));
      _allowedCoins.Add(25, new Coin("Quarter", 24.26, 1.75, 25));
      _allowedCoins.Add(50, new Coin("Half Dollar", 30.61, 2.15, 50));

      //usage
      AddCoin(10);
      AddCoin(5);
      AddCoin(25);
      AddCoin(50);
      AddCoin(75); //should be rejected.
      Console.WriteLine("The jar contains {0} cents.", _theCoinJar.GetCurrentValue()); //should be 90 cents
      _theCoinJar.EmptyTheJar();
      Console.WriteLine("The jar contains {0} cents.", _theCoinJar.GetCurrentValue()); //should be 0 cents
    }

    static void AddCoin(int coinValue) {
      if (_allowedCoins.ContainsKey(coinValue)) {
        if (_theCoinJar.AddCoin(_allowedCoins[coinValue])) {
          Console.WriteLine("{0} added to the jar.", _allowedCoins[coinValue].CoinName);
        }
        else {
          Console.WriteLine("The jar doesn't have enough space for a {0}.", _allowedCoins[coinValue].CoinName);
        }
      }
      else {
        Console.WriteLine("Please insert a standard US coin.");
      }
    }
  }

Set up a couple of global variables to hold the coin dictionary and the jar itself, define the individual coins, add a method to add the coins into the jar with some rudimentary error checking to see if the jar is full, or an invalid coin is dropped in.. and that's it.