2007/09/28

Listing users and all roles they're members of (SQL Server 2005)

I admit it's trivial but it took me awhile today to understand what data is shown in the sys.database_role_members catalog view, so I am posting this in case I forget.
SELECT
  DP1.name as ROLE_NAME
, DP2.name as [USER_NAME]
FROM sys.database_role_members DRM
INNER JOIN sys.database_principals DP1
  ON DRM.role_principal_id = DP1.principal_id
INNER JOIN sys.database_principals DP2
  ON DRM.member_principal_id = DP2.principal_id

2007/09/05

Disabling or enabling multiple jobs with a single click

If you navigate to <SQL Server instance>\SQL Server Agent\Jobs in SQL Server Management Studio and then right-click on some job, you'll see a context menu with Enable and Disable commands.

However, when you select more than one job and then right-click on the selection, a different menu shows and, of course [sic], it does not contain these commands.

No Enable/Disable command in the context menu

Luckily there is another way to do it - you can use Job Activity Monitor which uses the same context menu for a single job as well as multiple jobs.

Enable/Disable command in Job Activity Monitor

2007/09/04

SSIS imports NULLs instead of numeric values from an Excel file

Let's imagine that you have been assigned a task to import some kind of parts catalogue from an Excel file to SQL Server table. You decided to use Integration Services. One of the columns in the Excel file contains catalogue numbers. Most of them look like this XYZ123456 but some of them are plain numeric values.

This is what can happen:
Excel          SQL Server
CAT_NO         CAT_NO
---------      ---------
XYZ121156      XYZ121156
XYZ654321      XYZ654321
ABC120456  =>  ABC120456
CBA183456      CBA183456
ZYX123416      ZYX123416
123            NULL       (!)
ZXV654521      ZXV654521
This article from SQL Server 2005 Books Online explains this weird behaviour:
Missing values
The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. (In a tie, the numeric type wins.) Most cell formatting options in the Excel worksheet do not seem to affect this data type determination. You can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window.

2007/07/27

Preventing code from being generated in InitializeComponent() method for a property

When you place controls on a form, Visual Studio generates code in the InitializeComponent() method that sets values of their properties.

If you are developing a custom control and you don't want Visual Studio to generate such code for some of its properties, you should apply the DesignerSerializationVisiblityAttribute to such properties and place DesignerSerializationVisiblity.Hidden value on them.
[DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
public DateTime Value
{
    get { /* ... */ }
    set { /* ... */ }
}
More information about customizing the code generated by Visual Studio can be found here.

2007/07/26

Changing file permissions (chmod) without SSH/telnet access

If you need to change file permissions (chmod) for more than just a few files and your web hosting provider gives you only FTP access to your account (neither SSH/telnet nor a good web hosting control panel system), you can use the chmod function available in PHP.

You can either write a PHP script that calls chmod for each file that needs its permissions to be changed or use this snippet to call chmod recursively and change permissions for an entire folder tree.

2007/06/15

Eddie Vedder speaks Polish during a concert in Poland

I just found a video and a scan of the piece of paper with Eddie's speech.



Eddie's speech

But even with such help it still isn't easy to fully understand what he says. :)

And to have it all in one place, here's the setlist of the show they gave on 2007.06.13 in Chorzów, Poland.

2007/05/30

KeePassX looks ugly; making it look good again

If KeePassX (or other Qt application) looks really awful on your machine, the problem is probably the GUI Style chosen for Qt applications.

For quite a long time I couldn't find a way to change it, until I read this howto. I installed the qt4-qtconfig package and used the Qt Configuration tool (run qtconfig) to change the GUI Style to Plastique. You can see the results below.

Before
Ugly KeePassX

After
Nice KeePassX

2007/05/28

Neostrada ADSL + Linux + Siemens SpeedStream 4100 modem

If you have a Neostrada ADSL connection and a Siemens SpeedStream 4100 modem, it is really easy to configure it on Linux. You only have to:
  1. edit the /etc/network/interfaces file and configure the card connected to the modem (you need to set the IP address, the gateway, the netmask and the broadcast address - your provider should give you all this data)
  2. execute /etc/init.d/networking restart
I am by no means a network guru and I cannot exactly explain why it has to be done like this, but my guess is that the modem handles all the PPPoE stuff, so no PPPoE client (like RP-PPPoE) is necessary.

2007/05/16

A SQL CLR user-defined aggregate - notes on creating and debugging

At the end of this post you will find my very first attempt to use one of the new features of SQL Server 2005 - CLR integration. It is a CLR user-defined aggregate that produces a comma-separated (comma plus space to be precise) list of values.
Column1
--------
aaa
bbb       -->   aaa, bbb, ccc
ccc
My code is a modified version of an example that I found here.

Global variables

You may expect (at least I did) that if you have a global variable (e.g. intermediateResult in my example) and you assign it a value in the Init method then you will be able to use this value in the Terminate method. Well, that's not true.

If you need to use a value of some variable in the Terminate method, you have to serialize it in the Write method (to save the needed value) and then deserialize it in the Read method and assign to some variable (to restore the needed value).

You can of course save and restore more than one variable - use some kind of serializable object to store the values.

Debugging

There is an MSDN article that explains exactly how to debug a CLR user-defined aggregate - Walkthrough: Debugging a SQL CLR User-Defined Aggregate - nonetheless, I encountered one problem.

I started with a solution that contained two projects - one project contained the aggregate code and the other was for a SQL test script. It is possible to perform debugging in such configuration (I managed to do it, although I am not sure how), but it is a better idea to have a single SQL Server project with both the aggregate code and SQL test scripts. This is what the IDE expects, so following this advice can spare you lots of hassle.

For example, MSDN says that to debug a CLR user-defined aggregate you need to enable CLR Debugging, but it doesn't say that Application Debugging must also be enabled.

Application Debugging option

When I worked with a single project, Visual Studio enabled this option automatically when needed, while with the two-project solution I had to do it myself.
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable()]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToNulls=true,
    IsInvariantToDuplicates=false,
    IsInvariantToOrder=false,
    MaxByteSize=8000)]
public class Concat : IBinarySerialize
{
    #region Private fields
    private string separator;
    private StringBuilder intermediateResult;
    #endregion

    #region IBinarySerialize members
    public void Read(BinaryReader r)
    {
        this.intermediateResult = new StringBuilder(r.ReadString());
    }

    public void Write(BinaryWriter w)
    {
        w.Write(this.intermediateResult.ToString());
    }
    #endregion

    #region Aggregation contract methods
    public void Init()
    {
        this.separator = ", ";
        this.intermediateResult = new StringBuilder();
    }

    public void Accumulate(SqlString pValue)
    {
        if (pValue.IsNull)
        {
            return;
        }

        if (this.intermediateResult.Length > 0)
        {
            this.intermediateResult.Append(this.separator);
        }
        this.intermediateResult.Append(pValue.Value);
    }

    public void Merge(Concat pOtherAggregate)
    {
        this.intermediateResult.Append(pOtherAggregate.intermediateResult);
    }

    public SqlString Terminate()
    {
        return this.intermediateResult.ToString();
    }
    #endregion
}

2007/05/14

How to improve sub-pixel font rendering for Feisty (from Ubuntu Guide)

The fonts look way better out-of-the-box in Ubuntu 7.04 than they used to in previous versions (I wrote about this before - 1, 2), but they can still be improved.

How to improve sub-pixel font rendering for Feisty
This will dramatically improve the appearance of fonts with respect to the default Ubuntu install. The patched libraries are built against Freetype 2.3.x (not currently in feisty) and include David Turner's sub-pixel rendering patches.

In my opinion, the results are really impressive.