Reading docs on company time

The Help Viewer has good stuff on T-SQL, and I started looking through the query reference. It feels strange that SELECT can both initialize variables and do other things. Well, everything about SQL feels foreign. I have yet to see a tutorial that starts with

PRINT N'Hello world';

Maybe every author wants to avoid teaching SQL as an imperative language. I would treat it like Excel and VBA, except the first-class interface is the code part and not the data cells. Or it’s heresy to think so because databases need normalization and a careful design. Our edifice of persistence is a waterfall because no one wants to memorize ALTER. Yet intuition demands I master it.

Advertisements

Failed to decrypt ConfigurationProvider

The build can pass and all DLLs can be present, but launching the web project brings an error on the config. The <EncryptedData> section might work for one setting but not for the following <appSettings> element. The key is not to put the referenced files in C:\Users\<username> path. Possibly IIS does not have permissions to do things there.

Moving the config files to C:\some-folder made it work. This was not discovered by me but by a workmate, whose persistence and intuition make him very skilled. This was one of those problems that could not be easily found via StackOverflow. The fundamental process that begins this fault cascade starts with creating the TFS workspace in %USERPROFILE%.

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

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.

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.

Love and war in the palace of jade

Catching everything is not always good, especially for a Windows service: even when it blows up, the service remains in a running state. Hard to terminate, unstoppable without manual intervention, and hence resistant to restart promises. It’s easier to handle after concluding where in the program things should end.

The ServiceController just needs the service name to request a stop. The following tools are essential:

  • installutil and installutil /u from Visual Studio Developer Prompt (as Administrator)
  • Task Manager’s Services tab to watch the service transition states
  • net start and net stop

diff with Access

Concurrent VBA is one option for performance, but I had overlooked another idea: you don’t need to import the entire dataset of something if it only differs in small places. Of course, your schema should protect you from duplicate inserts – but iterating through half a million rows is still a wait on the user-side. A diff is quick and the subsequent insert takes a fraction of the original approach.

This idea came from the supe, and I felt I had learned a useful nugget. We almost went with version control (!) to manage imports, because one requirement was a historical view of the data. Fortunately, cygwin comes with diff and hopefully things will stay performant.

Actually, I was afraid today. We were discussing computer science ideas and I thought, “I’m not paid enough to think about this.” It was scary because I had somehow combined a disgruntled attitude and a certain jadedness to tamp on gaining skills. All I can say is I hope you never experience it.