Saturday, July 13, 2013

A Little Strangeness in the Way A Script is Run by Registry

An observation from today.

Currently net.exe is executed when the user logs in and maps a network drive (may also use credentials based on the user, etc.). This is achieved as follows:

Scenario 1. Normal conditions

Under the registry key HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Run. Create a string value name MountDrive and data net.exe use b: /persistent:no \\localhost\mydrive

Behaviour: Command runs, drive is mapped.

Now suppose command fails occasionally. To understand why, I would like to redirect the output of net.exe into the text file. That should be easy to do.

Scenario 2. Modified Value in Registry

I modified the registry entry to

net.exe use b: /persistent:no \\localhost\mydrive >>C:\netlog.txt

Behaviour: Command runs, drive is mapped, but no output file. If I run the same command from command line, the drive is mapped and output file is created (as expected).

Next, I came up with a workaround

Scenario 3. Run a cmd file from Registry.

  • create map.cmd with the contents net.exe use b: /persistent:no \\localhost\mydrive >>C:\netlog.txt
  • place it on C: drive
  • modify the registry entry to C:\map.cmd

Behaviour: Command runs, drive is mapped, output file is created with the contents The command completed successfully. Why does it behave like that - I don't know.

by . Also posted on my website

Tuesday, July 9, 2013

In SQL Query, Only Return Every n-th Record

A little SQL trick that helps in some cases. In my case, I wanted to select some data that is logged into the table every several seconds, and then quickly plot it in Excel, but over a date range of a few months. So, I run a query

SELECT "timestamp", valueiwant
FROM mytable
order by timestamp

And that potentially leaves me with thousands or hundreds of thousands of rows. However, to visualise a trend over time, I don't need to plot each and every value on the graph. I'll be happy with 1/10 or even 1/100 of records. Here is how I can use ROW_NUMBER to achieve that.

SELECT * FROM
(
 SELECT "timestamp", instrumenttimestamp, ROW_NUMBER() OVER (order by timestamp) AS rownum
    FROM mytable
 order by timestamp
) AS t
WHERE t.rownum % 25 = 0

Row number returns the sequential number of a row in the result set. The WHERE clause then checks if the number is a multiple of 25, therefore only rows 25, 50, 75, 100 etc. will be returned by the outer query.

References

ROW_NUMBER (Transact-SQL)
Return row of every n'th record
by . Also posted on my website