TSQL: concatenated row-folding

A second table has details for a first table, but there may be one or more records. For example, banana split has ice cream and bananas, but orange dessert may just require an orange. Folding multiple rows into a string would let us see each recipe’s ingredients in one query.

select (select c1 + ' ' + c2 + ';' as 'data()'
        from tbl2 where tbl1.id = tbl2.id
        for xml path('')), cc1, cc2, cc3
from tbl1

Reference

Microsoft

If not for Visual Studio, I would not have learned git or version control. “Pro Git” is out there, but so are the numbers of my days, the time unspent, and an exhausting pattern of sleep and exercise. Without Microsoft to make the UI, I may have never understood branches, pushes, and the like.

I still cling to the open kernel and bootstrapped scrappers, because I don’t like the option of hidden things. Even if I never visit the source, certain decisions in proprietary have been made for you: you inhabit their mind-share universe, take on the customs of their vocabulary, and manipulate sigils layers removed. Why don’t we have certs for mathematics?

I guess I am doomed to be malcontent. I envy my peers who have embraced the ecosystem. They solve problems and do not mind the language or the tools. That is why my first question of people hiring programmers is “Do they have an ideology?”

Pickle

I chased a rabbit down a series of blind holes, the same spots with different patrons. Variations on a theme: StackOverflow, but nothing concrete. No one seemed to have quite the same issue. I thought it was permissions, the lupus of mysterious errors. In summary, why did a service terminate under Local System but not under a typical admin-level user?

On the test machine, TCP connects from Local System were rejected. As a normal user, that same traffic was approved. In both cases, expected behavior was failure. With the same code and initial conditions, it was very confusing to observe actual behavior as one or the other.

In the end, with enough zealous log messages, I realized I was not copying over the rebuilt DLL as well. So any changes made to the lib file were never transferred to the test environment. After that, process termination worked without complaint.

Quality

QA is not your test toilet. They are gate-crashers, edge-case craftsmen, meticulous researchers reproducing your famous claims. Each working line to us is sacred, a passing build is relief, and compilers are a nuisance. The same laziness, impatience, and hubris celebrated by our louder folk are three easy paths to file a defect. When it returns and reminds you of your mortality, you will realize a calibration is in order.

How should you then fulfill two roles when time is theft, thought is not keystrokes, and trying something is either bug repro or scrolling for an injection point? New projects require more startup speed than quality (if approved), and bug fixes need a delicate patch. Those seem to be the general rules.

Programmer, automate thyself. Each defect a perturbation of the universe, the interaction set gradually widening, until we achieve a nirvana of stasis: clicks on widgets, transactions into tables.

Review

A fundamental misunderstanding to resolve in a new team is the code review: is it an iterative, rapid-feedback process or an acceptance process? The latter implies implicit certification: it is the galley proof, the final set before the press. Everything before is discussion and design, ad-hoc and informal – nothing so much as sign-offs, but a paper trail, reviewers in-the-know, and a general consensus.

Failure to grok this is nearly fatal. Plopped reviews in an inbox queue violates least surprise; a sentence commit appears flippant; and consternation abounds while the selfsame try to accomplish their work. Understanding behavior quirks, openness to struggle, and admittance of failure have been foreign to me until now. For I thought the code would speak for itself.

At this level, the issue is not technical problems or acuity. These are assumed solved by dint of search or sweat. What matters is the individuals in a team to gel so that business requirements are fulfilled at the knife edge between velocity and quality. This means I still have a lot to learn about “programming.”

Install services without InstallUtil.exe

The sc command can install services! It comes with Windows.

> sc \\MACH002 create test binPath= mysvc.exe start= auto

Additional configuration can be done with the same utility. For all I know, target binaries were built following the Windows Services instructions on MSDN (using ServiceBase, including an installer, etc). Anything different may not work.

I still need to test whether LocalSystem is required for Environment.Exit(1) to work. Services have so many thorny aspects to them, even though they have no UI.

wevtutil instead of Event Viewer

You won’t be able to release IP or reset via netsh on a dedicated machine. You will need to find a way to simulate an exception that would cause a process exit. A throw is the easiest, and successful termination of a service shows up in the Event Viewer (under System section). On remote machines with existing traffic, it may lag too much to use the snap-in, so use this tool:

> wevtutil gl System /f:text | findstr "MyService" > %TMP%\evt.log

If you think of the NT kernel as a proprietary masterpiece, it is not hard to want for knowledge of it. CVEs abound and analytics demand good tools. These researchers do not pause at the boundary of free or not free; they simply act in the present. The omission of “server core” installs when *nix users bash performance begs for a measure of charity.

Simulate network disconnects

Service resiliency needs to survive disconnects from persistent resources like databases. The all-knowing cylinder stretches tendrils in TCP/IP, and network I/O is not the infinite fountain in which we conveniently abstract. Hiccups will happen, and a long-running service can become a zombie – unable to be killed except by remote login, to great consternation and hubbub.

I considered proxies, packet redirection, and blocking firewall outgoing ports, but all were either too complex or outright didn’t work. In the end, I used

ipconfig /release
ipconfig /renew

It’s scriptable and convenient. Throw it in while a service runs queries and observe the Event Viewer. The OS can manage restarts after failure, and MSDN provides a timer-based example.

This will not work on machines with static IP addresses.

Daemon deeds

An alternative to a scheduled task is to run a ticking service. In lieu of timed starts, the OS clock decides by intervals of sleep. It seems more powerful because the operation is now in a language other than batch script: the abstractions of event handlers and service installers; being able to write to the event log; the standard library and high-level programming constructs.

The development loop still requires a few tools: InstallUtil.exe (and the /u switch), the sc command, and net start/stop. Visual Studio is good for getting the project built, but testing still takes Event Viewer (eventvwr), Services (services.msc), and Task Manager (taskmgr) as needed.

Reliability is achieved with a plan to terminate the process. At the OS level, a service restart strategy can be as straightforward as

> sc failure "my service" reset= 3600 action= restart/60000

You get restart attempts in the Event Log for free.

Quality

Moving fast and breaking things is a public virtue, but test cases guarantee a return. The early tickets prided for next steps are marked with defects, and the work-plate doubles. Not the best move, but I want to grasp as much of the system as possible. Somewhere past this wilderness is the new, some quiet promise of time to originate novel things, named things, to be shared and adopted.

Lurking the test cases is a source of education. Out of necessity, system conditions must be manipulated and results verified. They need to act as both end-user and black box. Steps are planned out, manual algorithms: accountants bookkeeping an ordered process. More discipline seems present here than in the patched fixes, like lawyers for medics.

This is a fertile place; I needed a domain to excuse inaction. New languages present themselves, and this is a chance to learn a second handful.