Generating a range of numbers and dates in T-SQL

June 3, 2018

Act 1

Once upon a time, there was a question on SO:

We’ve got start and end dates, and we’d like to generate a list of dates in between.

How would you create a set of dates that are within a user-defined range using T-SQL?

to which I responded, back in 2009:

select dateadd(day, number, @dtBegin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null
    ) n
where dateadd(day, number, @dtBegin) < @dtEnd

Over the years, commentators asked that my answer be fixed, as it was not totally corrected.

So I took the time to check the SQL statement on all versions of SQL Server that I could access, 2008, 2012, 2016, and the result was always correct.

However, I also looked at the internal stored procedures, and found that the access master..spt_values with a condition WHERE type=, instead of WHERE name is NULL.

As I stated:

However, as I tried to analyze the code that MSSQL internally when querying from spt_values, I found that the SELECT statements always contain the clause WHERE [type]='[magic code]'.

Therefore I decided that although the query returns the correct result, it delivers the correct result for wrong reasons:

There may be a future version of SQL Server which defines a different [type] value which also has NULL as values for [name], outside the range of 0-2047, or even non-contiguous, in which case the result would be simply wrong.

So my “official” solution now is

declare @dt datetime, @dtEnd datetime
set @dt = getdate()
set @dtEnd = dateadd(day, 100, @dt)

select dateadd(day, number, @dt)
from 
    (select number from master.dbo.spt_values
     where [type] = 'P'
    ) n
where dateadd(day, number, @dt) < @dtEnd

Act 2

However, I recently came across a problem when a customer tried to deploy a T-SQL database I had developed to Azure, and reported that Azure does not support querying master..spt_values, because it does not allow cross-database queries.

So on to the internets and see what solutions people come up with.

Based on this View which generates the number of all integers in T-SQL I created a view to mimic the original spt_values table:

CREATE VIEW dbo.spt_values
AS
    WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
    , Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
    , Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
    , Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
    , Int16(z) AS (SELECT  TOP 2048 0 FROM Int8 a CROSS JOIN Int4 b)
 SELECT CAST (NULL AS NVARCHAR(35)) [name],
	ROW_NUMBER() OVER (ORDER BY z) -1 AS [number],
	CAST ('P' AS NCHAR(3)) [type],
	CAST (NULL AS INT) [low],
	CAST (NULL AS INT) [high],
	0 [status]
    FROM Int16

The column definitions of this view are based on the definition of spt_values in the master database

CREATE TABLE [dbo].[spt_values](
	[name] [nvarchar](35) NULL,
	[number] [int] NOT NULL,
	[type] [nchar](3) NOT NULL,
	[low] [int] NULL,
	[high] [int] NULL,
	[status] [int] NULL
) ON [PRIMARY]

Act 3

During research, I often came across mentions of “14 different solutions” to enumerate dates or integers, pointing to http://www.projectdmx.com/tsql/tblnumbers.aspx, a site which is long gone. Fortunately, it is backed up in the Wayback Machine, dated 20120620.

Advertisements

Moving and Upgrading Bugzilla

May 27, 2018

I had to migrate and upgrade a Bugzilla installation to a newer machine with Ubuntu LTS installed. Fortunately, Bugzilla has documented the steps in Moving Bugzilla Between Machines, but unfortunately the documentation does not seem to be uptodate.

First I had to make sure the latest update to the LTS was installed:

sudo apt update
sudo apt upgrade
sudo apt dist-upgrade

This resulted in

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description: Ubuntu 16.04.4 LTS
Release: 16.04
Codename: xenial

Next, I following the Quick Start installation guide

$ sudo apt-get install git
[sudo] password for herbert:
Reading package lists... Done
Building dependency tree
Reading state information... Done
git is already the newest version (1:2.7.4-0ubuntu1.3).

The next recommended step brought up the error message

Package apache2-mpm-prefork is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package ‘apache2-mpm-prefork’ has no installation candidate

and I had to remove the package apache2-mpm-prefork from the command

sudo apt-get install apache2 mysql-server libappconfig-perl libdate-calc-perl libtemplate-perl libmime-perl build-essential libdatetime-timezone-perl libdatetime-perl libemail-sender-perl libemail-mime-perl libemail-mime-modifier-perl libdbi-perl libdbd-mysql-perl libcgi-pm-perl libmath-random-isaac-perl libmath-random-isaac-xs-perl libapache2-mod-perl2 libapache2-mod-perl2-dev libchart-perl libxml-perl libxml-twig-perl perlmagick libgd-graph-perl libtemplate-plugin-gd-perl libsoap-lite-perl libhtml-scrubber-perl libjson-rpc-perl libdaemon-generic-perl libtheschwartz-perl libtest-taint-perl libauthen-radius-perl libfile-slurp-perl libencode-detect-perl libmodule-build-perl libnet-ldap-perl libauthen-sasl-perl libtemplate-perl-doc libfile-mimeinfo-perl libhtml-formattext-withlinks-perl libfile-which-perl libgd-dev libmysqlclient-dev lynx-cur graphviz python-sphinx rst2pdf

I cloned Bugzilla into /var/www using git

/var/www$ sudo git clone --branch release-5.0-stable https://github.com/bugzilla/bugzilla bugzilla

and edited the MySql config files as recommended

/etc/mysql/mysql.conf.d$ sudo nano mysqld.cnf

max_allowed_packet = 100M
ft_min_word_len = 2

Create a bugs user account and run mysql "GRANT ALL PRIVILEGES ...." to create the bugs database

Next, I created a backup of the original Bugzilla database, and proceded according to the Moving… documentation.

/var/www/bugzilla$ sudo ./checksetup.pl

Check setup brought up warnings about missing Perl modules.

/var/www/bugzilla$ sudo /usr/bin/perl install-module.pl --all
ERROR: Using install-module.pl requires that you install "make".

Solve this error by installing make:

$ sudo apt-get install make

The next run of Check Setup raised another error message:

/var/www/bugzilla$ sudo ./checksetup.pl

DBD::mysql::db do failed: Cannot change column 'setter_id': used in a foreign key constraint 'fk_flags_setter_id_profiles_userid' [for Statement "ALTER TABLE flags CHANGE COLUMN
setter_id setter_id mediumint NOT NULL"] at Bugzilla/DB.pm line 742.
Bugzilla::DB::bz_alter_column_raw(Bugzilla::DB::Mysql=HASH(0xc89c6dc), "flags", "setter_id", HASH(0xca65fc0), HASH(0xe69a248), undef) called at Bugzilla/DB.pm line 701
Bugzilla::DB::bz_alter_column(Bugzilla::DB::Mysql=HASH(0xc89c6dc), "flags", "setter_id", HASH(0xca65fc0)) called at Bugzilla/Install/DB.pm line 626
Bugzilla::Install::DB::update_table_definitions(HASH(0x97e6884)) called at ./checksetup.pl line 172

I found a couple of forums regarding this error (Mozilla Support, SO), and the problems seems to be caused by newer MySql versions refusing to change or delete a column which is referenced by a foreign key constraint.
I removed the constraint directly from the mysql prompt:

use bugs;
alter table `flags` drop foreign key `fk_flags_setter_id_profiles_userid` ;

After this, the Check Setup script completed without errors.

 


Solving SignalR $.connection.HubName is null or undefined

July 5, 2017

I added support for SignalR to one of the ASP.Net MVC applications I develop, mainly following this Microsoft tutorial.

I was surprised by the fact that you cannot install the SignalR package from the NuGet GUI (in VS 2013 at least), but need to switch to the Package Manager Console and type

install-package Microsoft.AspNet.SignalR

It worked nevertheless.

I also copied the JS initialization code from there

 $(function () { 
    // Declare a proxy to reference the hub. 
    var chat = $.connection.chatHub;
    chatHub.client.broadcastMessage = function (name, message) { ... };
}

(adjusted, of course, to the hubs defined in my project), only to find that the JavaScript console in Chrome Developer Tools displays the error message

Uncaught TypeError: Cannot read property 'client' of undefined
 at HTMLDocument. (38024:10393)
 at fire (jquery-1.12.4.js:3232)
 at Object.fireWith [as resolveWith] (jquery-1.12.4.js:3362)
 at Function.ready (jquery-1.12.4.js:3582)
 at HTMLDocument.completed (jquery-1.12.4.js:3617)

The error is raised by the line

chatHub.client.broadcastMessage = function (name, message) {

with chatHub being undefined.

I stepped through the JavaScript code and found that the (automagically generated) JavaScript file

< script src="@Url.Content("~/signalr/hubs")" >< /script>

defines the hubs correctly, but somehow the hub definitions are lost when the

$(function() {

is being executed.

It turned out to be a question of JavaScript scopes, and I moved the client callback definition out of the jQuery $(function(){}) directly into the


Broken Windows

June 12, 2017

Dear Windows, why do you place a broken shortcut into your search results?

changepassword1

changepassword2

Title obviously inspired by Broken Windows. Brought to you by “broken Windows”.

But who are those “We” who never seem to be able to accomplish anything?


Debugging Ajax’ed JavaScript and jQuery val() calls

June 8, 2017

I develop a web application which displays data in a read-only form, and loads the edit form upon pressing a button

$(function () {
  $(".btnEdit").click(function () {
    $.ajax({
      url: '@Url.Action("Form", new { id = Model.ID })',
      type: 'post'
    });
  });
});

Now I needed to debug the JavaScript code loaded by the call to $.ajax(), but Chrome does not seem to display the loaded response in its tree of Sources.

An answer on SO provided me with the solution: Simply add the line

//# sourceURL=@Url.Action("Form", new { id = Model.ID })

inside a <script> block in the AJAX-loaded HTML. This will add the requested URL under the (no domain) node

chrome ajax js

Now that I have access to the source file, I needed to find all invocations of the jQuery val() function, since I was tracking down a wrong value in an <input>.

Again SO provided a solution, which I added to my code

(function($){
  var originalVal = $.fn.val;
  $.fn.val = function(){
    var selectorPath = $(this).parents().map(function () {return this.tagName;}).get().reverse().join("->");
    console.log("val( #" + $(this).attr("id") + " " + selectorPath + " , " + JSON.stringify(arguments) + ")");
    var result =originalVal.apply(this,arguments);
    return result;
  };
})(jQuery);

Now that the calls to val() were logged to the Console, it was easy to find where the wrong value was set.


Installing Terratec S7 on 64-bit Windows 8.1

May 24, 2017

I got myself a Terratec S7 because my existing Cynergy S2 card started to show problems, and the situation is not resolved yet, mainly because the S7 does not appear to control the dish as it should in the PC I want it to run.

To figure out where exactly the problem is, I took the S7 and tried to install everything on a separate laptop, not contaminated by previous installations of any TV software 😉

I got the latest drivers for Windows 8.1, and of course DVBViewer, which superseded Terratec Home Cinema, well-known from the S2 installation.

I installed the drivers, but did not immediately notice that the installation had failed. Only when DVBViewer presented an empty list of supported hardware, and after installing the drivers again, I realized the failure (there is no visual clue as to whether installation was successful or not, you actually have to *read* *text* 😉 ).

The output of the Windows Error Report (accessible from the error entry in the Event Log) listed an error code E0000247

Version=1
EventType=PnPDriverImportError
EventTime=131400033808805347
Consent=1
ReportIdentifier=b04db504-3f95-11e7-8257-441ca8536b2a
Response.type=4
Sig[0].Name=Architektur
Sig[0].Value=x64
Sig[1].Name=Win32-Fehler
Sig[1].Value=E0000247
Sig[2].Name=INF-Name
Sig[2].Value=terratec_s7.inf
Sig[3].Name=Treiberpakethash
Sig[3].Value=b47a89cb47c6613e055bc51ffc8935d320b543dd
DynamicSig[1].Name=Betriebsystemversion
DynamicSig[1].Value=6.3.9600.2.0.0.256.48
DynamicSig[2].Name=Gebietsschema-ID
DynamicSig[2].Value=3079
FriendlyEventName=Die Treibersoftware konnte nicht installiert werden.

which hints at a certificate validation error (hint, hint).

The Device Install Log at C:\Windows\Inf\setupapi.dev.log was also rather clear on the issue:

sig: {_VERIFY_FILE_SIGNATURE} 10:56:13.520
sig: Key = terratec_s7.inf
sig: FilePath = g:\terratec s7\terratec_s7_rev.4_driver_1.0.0828.0_xp_vista_7_8\terratec s7 rev.4\bda driver 1.0.0828.0\64bit\terratec_s7.inf
sig: Catalog = g:\terratec s7\terratec_s7_rev.4_driver_1.0.0828.0_xp_vista_7_8\terratec s7 rev.4\bda driver 1.0.0828.0\64bit\terratec_s7.cat
! sig: Verifying file against specific (valid) catalog failed! (0x800b0109)
! sig: Error 0x800b0109: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.
sig: {_VERIFY_FILE_SIGNATURE exit(0x800b0109)} 10:56:13.567
sig: {_VERIFY_FILE_SIGNATURE} 10:56:13.571
sig: Key = terratec_s7.inf
sig: FilePath = g:\terratec s7\terratec_s7_rev.4_driver_1.0.0828.0_xp_vista_7_8\terratec s7 rev.4\bda driver 1.0.0828.0\64bit\terratec_s7.inf
sig: Catalog = g:\terratec s7\terratec_s7_rev.4_driver_1.0.0828.0_xp_vista_7_8\terratec s7 rev.4\bda driver 1.0.0828.0\64bit\terratec_s7.cat
! sig: Verifying file against specific Authenticode(tm) catalog failed! (0x800b0101)
! sig: Error 0x800b0101: A required certificate is not within its validity period when verifying against the current system clock or the timestamp in the signed file.
sig: {_VERIFY_FILE_SIGNATURE exit(0x800b0101)} 10:56:13.598

Since 64-bit Windows requires device drivers to be signed, this seemed to be a show stopper. However, you can disable signature verification by typing (in admin mode)

bcdedit /set testsigning on

which fortunately worked on my laptop.

After reboot, I was able to install the drivers, and Windows even is so nice as to warn you if you install unsigned drivers

terratec signed driver

(There is probably an alternative method by extracting certificates from the installer’s .cat files, but I didn’t have to try, so I can’t tell.)

Starting up DVBViewer again, it immediately recognized the S7, and a range scan found several hundred channels fine. I switched to a couple of channels that I also had on my TV, but there was no picture.

Forum talk mentions this issue, along with LAV filters, which I had come across earlier clicking through the files of the Home Cinema download page. So I downloaded TERRATEC_Home_Cinema_Codec_Patch.exe from there, installed it, and finally got a TV on my laptop.

 


Adding Dropdown Arrow for Chosen Plugin in Multiselect Mode

April 2, 2017

I use the jQuery Chosen plugin in a web application, and recently got the request to display a dropdown arrow in multiselect mode.

I found an answer on SO regarding styling of the dropdown arrow, but that covered only singleselect mode. At least a starting point 😉

Within minutes, I had a CSS-only solution for this particular problem:

<style type="text/css">
.chosen-container-multi.chosen-container .chosen-choices::after {
    background: url(@Url.Content("~/Content/chosen-sprite.png")) no-repeat 3px 4px;
    width: 16px;
    height: 100%;
    content: " ";
    position: absolute;
    right: 0;
    background-color: lightgray;
}
</style>

(Note that this is an ASP.Net MVC application, and @Url.Content() computes the URL relative to application root.)

Only after I solved this, I found that there is a closed (and unresolved) issue on github from 2011, with comments up to 2016, but no solution to a valid feature request…