You are here

Joeri's blog

SQL: slow query with parameters

In my C# reporting program I had a query which was giving me timeouts. The part that gave me trouble was something similar to:


FROM invoices

WHERE processdate BETWEEN @from AND @until
AND printdate > accountingdate

If I defined the @from and @until variables in the query, i.e. if I prepend it with:

DECLARE @from datetime = '2015-01-08'
DECLARE @until datetime = '2015-01-14'

everything ran very fast as expected.

But when I run the query from my C# program the parameters get added like this:

SQL: comma separated values to table

With MS-SQL it is possible to convert a string with comma separated values (e.g "one,two,three,four") to a table with multiple records.

The trick is to convert the string to XML and then use the XML nodes and value methods to get the list.

DECLARE @string varchar(max) = 'one,two,three,a,b,c'
DECLARE @xml XML = CONVERT(xml, '<root><row>' + REPLACE(@string, ',', '</row><row>') + '</row></root>')

SELECT c.value('.', 'varchar(255)')
FROM @xml.nodes('/root/row') T(c)

Note that you are free to choose the "<root>" and "<row>" tag names. Likewise for the "T(c)" definition, I just used these because they are used in the Microsoft examples.

Fix Windows error "The name limit for the local computer network adapter card was exceeded"

This morning I wasn't greeted with happy faces at work. Some users could not log in, some got connection errors from Outlook and often some or all of their mapped drives were missing.

All this was happening on one of our (Citrix) terminal servers. Searching through the event logs I found warnings such as "The user 'X:' preference item in the 'Drive Mappings {F86B7E1E-0434-408F-AA1A-B616952AF5C2}' Group Policy object did not apply because it failed with error code '0x80070044 The name limit for the local computer network adapter card was exceeded.' This error was suppressed.".

Searching Google for the error code led me to Microsoft Article Essentially the server was regularly failing to create network connections.


Get the first day of a given week (ISO 8601)

Most programming languages provide a way to get the week number of a given date. For an SQL example, check out my previous article: SQL: year of an ISO week.

In C# you can use the following:

int week = CultureInfo.CurrentCulture.Calendar.GetWeekOfYear(date, CalendarWeekRule.FirstFourDayWeek, DayOfWeek.Monday);

But there's usually no function provided to get the first day of a given week and year. The shortest (but still readable) way that I can get up with in C# is the following:

Convert TIFF to PDF

To convert TIFF files to PDF in a program or script, one can use the versatile ImageMagick "convert" program ( To merge three TIFF files, you could use:

convert.exe page1.tiff page2.tiff page3.tiff output.pdf

You can also use it to convert a single multipage TIFF file into a PDF file, but for large files ImageMagick needs a lot of memory and processing might be slow.

12/12/12 12:12:12

Sure, 11/11/11 11:11:11 was cooler, but this is probably the last time I can write something like this. Unless there would be some advancements in Cryogenic Hibernation off course, then I'll write about it again starting in 2101 ;-)

Alfresco: increase session cookie timeout

By default, Alfresco uses credential cookies which are valid for only 60 minutes. This means that users may need to log in a few times during the day.

To increase the timeout, edit files /opt/alfresco-4.2.b/tomcat/webapps/share/WEB-INF/web.xml and /opt/alfresco-4.2.b/tomcat/webapps/alfresco/WEB-INF/web.xml:

   <session-timeout>1440</session-timeout><!-- 1 day -->

And restart Alfresco.

Increasing the value further is normally useless because the cookies sent by Alfresco (or actually Tomcat) are session cookies. These kind of cookies will be deleted when the user closes his browser.

Alfresco Share: force a specific interface language

Alfresco Share always shows its interface in the language requested by the user's browser. That's usually fine but sometimes we prefer to get everything in English, it makes writing and finding documentation easier.

We can force Alfresco to use a specific language by using Apache mod_header. First, configure an Apache virtualhost as outlined in my previous post.

Then, enable the headers module:

a2enmod headers

Add the following before the "RewriteEngine on" statement in /etc/apache2/sites-available/alfresco-reverse-proxy:

RequestHeader set Accept-Language: en

And finally reload Apache:

/etc/init.d/apache2 reload

This will add or replace an "Accept-Language: en" header in all HTTP requests.

Alfresco: redirect to HTTP port 80

By default, Alfresco or any other Tomcat application will run on HTTP port 8080. Because of this, users would need to go to, for example, http://alfresco:8080/share to reach the Alfresco Share website.

We can make this easier by configuring Apache proxy_ajp which will redirect port 80 to Tomcat and will make the "/share" path optional.

Install Apache and enable the necessary modules:

apt-get install apache2
a2enmod proxy_ajp rewrite

Create a virtualhost configuration file at /etc/apache2/sites-available/alfresco-reverse-proxy with the following content:

Alfresco Share: default AND search

It's weird that Alfresco Share uses an OR search by default. If we want to find files related to the budget of the marketing department we would have to search for

budget AND marketing

There's an article and a bug report with information on how to change the default behavior to an AND search, but they require you to modify the Java code, which is quite complicated.

As long as this problem is not fixed in Alfresco, you may find it easier to make some JavaScript changes. Just edit file /opt/alfresco-4.2.b/tomcat/webapps/share/components/search/search.js and modifiy the _performSearch function as following:


Subscribe to RSS - Joeri's blog