2007/09/30

Shorewall does not start at boot time

I am neither shell scripting nor security guru, but I think that there is an error in the /etc/init.d/shorewall script that installs with the version 3.2.6-2 of Shorewall.

I installed Shorewall using Synaptic today, configured it and tested that I can start it manually. Then I restarted the computer and found out that there are no rules defined in any iptables chain (sudo iptables -L). I used the sudo invoke-rc.d shorewall start command to check what's happening during boot time and I saw the following error:

  Please read about Debian specific customization in
  /usr/share/doc/shorewall/README.Debian.gz.


After a few minutes of checking by trial and error I knew there was a problem with a piece of code right after the # check if shorewall is configured or not comment and after a few more minutes I modified it and the problem was solved. Below is the modified version - the script was looking for a wrong file and checking a non-existent variable.
# check if shorewall is configured or not
if [ -f "/etc/shorewall/shorewall.conf" ]
then
. /etc/shorewall/shorewall.conf
if [ "$STARTUP_ENABLED" != "Yes" ]
then
not_configured
fi
else
not_configured
fi

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.

Improved sub-pixel rendering

2007/05/12

Regular expressions are easy! You only need Regulazy

I think that this tool is really, really great. Regulazy is an absolute (if you know me, you know that I don't get overexcited like this very often ;) must-have for anybody who needs to create regular expressions, but is not a regex guru.
Regulazy is a visual Regex Creation tool for beginners.
It contains an easy "point and click" user interface, and allows creating regular expressions for simple searches almost instantly without requiring Regex syntax knowledge from the end user!
And if you need a more advanced and powerful tool, check out The Regulator. I have used it a few years ago and found it great, too.

2007/05/08

'Alter table failed because unique column IDs have been exhausted for table MY_TABLE' error

Below you'll find a excerpts from my conversation with Peter Yang from Microsoft in microsoft.private.directaccess.sqlserver newsgroup.

Me:
When I try to add a column to MY_TABLE, the ALTER TABLE statement fails with the following error:

Alter table failed because unique column IDs have been exhausted for table 'MY_TABLE'.

1. Can you please explain this exactly means?
2. What is the 'column ID'? (I suppose it is some column in some system table)
3. Is there any way to solve this problem apart from dropping and re-creating the table?
Peter Yang:
Since column id is not reused and the server also defines the maximum number of column id (MAXCOLID == 32000), then trying to alter table add column, alter table drop column until beyond 32000 iteration will be failed. You shall see the error message you encounter under the situation.

For example, using the following script shall reproduce the problem:
CREATE TABLE T1 (C int, C0 varchar)
GO

CREATE CLUSTERED INDEX IDX1 ON T1(C)
GO

DECLARE @i int
SET @i = 1
WHILE @i <= 32770
BEGIN
IF @i % 2 = 1
BEGIN
ALTER TABLE T1 ADD C1 varchar   
ALTER TABLE T1 DROP COLUMN C0    
END    
ELSE   
BEGIN   
ALTER TABLE T1 ADD C0 varchar   
ALTER TABLE T1 DROP COLUMN C1  
END  
SET @i = @i + 1
END 
GO 

DROP TABLE T1
GO
To work around the issue, you may need to create a new table with the columns you want other than alter the table since it has reached maximum ids of the server.
Me:
I just to want to make sure that re-creating the entire table is the only way to solve this problem. There is no way to reset the column id value, is there?
Peter Yang:
Thank you for your reply. Based on my research, there is no method to reset columnID value for a table and columnIds are not reused. This is by design behavior and hard coded right now.

UPDATE:

I am not the only one who encountered this problem. A guy from work showed how to determine the maximum used COLUMN_ID for a chosen table.
-- SQL Server 2000
SELECT 
name AS TABLE_NAME
, info AS MAX_COLUMN_ID_USED
FROM sysobjects
WHERE id = OBJECT_ID('put_the_table_name_here')

-- SQL Server 2005
SELECT 
name AS TABLE_NAME
, max_column_id_used AS MAX_COLUMN_ID_USED
FROM sys.tables
WHERE object_id = OBJECT_ID('put_the_table_name_here')

UPDATE 2:

My suggestion submitted to Microsoft - if you have this problem too, rate it!

2007/05/03

Album Cover Art Downloader does not start

Today I've run into my first problem caused by upgrading Ubuntu to version 7.04 - Album Cover Art Downloader (version 1.6.0) stopped working.

If you try to run Album Cover Art Downloader (by typing albumart-qt in the console) and get the following error:
Traceback (most recent call last):
File "/usr/bin/albumart-qt", line 145, in 
sys.exit(runGui())
File "/usr/bin/albumart-qt", line 77, in runGui
import albumart_dialog
File "/usr/lib/albumart/albumart_dialog.py", line 32, in 
from pixmap import getPixmapForPath
File "/usr/lib/albumart/pixmap.py", line 4, in 
import albumart_images
File "/usr/lib/albumart/albumart_images.py", line 6
SyntaxError: Non-ASCII character '\xa0' in file 
/usr/lib/albumart/albumart_images.py on line 6, but no encoding declared; 
see http://www.python.org/peps/pep-0263.html for details
open the /usr/lib/albumart/albumart_images.py and in the first or second line in the file add:
# coding: latin-1

2007/04/25

Sending mail using System.Net.Mail.SmtpClient and Gmail

This code snippet:
MailMessage message = new MailMessage();
message.From = new MailAddress("somebody@gmail.com");
message.To.Add(new MailAddress("somebody-else@some.server.com"));
message.Subject = "testing...";
message.Body = "This is a test.";

SmtpClient smtp = new SmtpClient();
smtp.EnableSsl = true;
smtp.Send(message);
along with this app.config file:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <system.net>
    <mailSettings>
      <smtp>
        <network
          host="smtp.gmail.com"
          port="587"
          userName="somebody@gmail.com"
          password="some-password"
          defaultCredentials="false"
        />
      </smtp>
    </mailSettings>
  </system.net>
</configuration>
allows to send e-mail from .NET code using Gmail as an SMTP server.

It is crucial to remember to:
  1. set EnableSsl to true;
  2. use port 587 (not 465, clicky).

2007/04/23

Upgrading Ubuntu 6.10 to 7.04 on ASUS A6RP-AP069 laptop

If you are expecting a lengthy post with detailed list of encountered problems and their solutions, you will be disappointed, because... there were none.

We just clicked the Upgrade button in Update Manager and left the computer for a few hours to download the updates. Then, after the installation began, we answered 3 or 4 questions whether to keep or replace some configuration files (we replaced them all, but kept the copies of modified files just in case) and that's it.

After restart we had a laptop running Ubuntu 7.04 and everything - wireless networking (with WPA), sound, graphics - was working properly. No problem at all.

2007/04/08

Converting datetime to YYYY-MM-DD and YYYY-MM-DD hh:mm:ss formats

I know this is trivial, but somehow I keep forgetting this.
-- YYYY-MM-DD
SELECT CONVERT(varchar(10), GETDATE(), 120)

-- YYYY-MM-DD hh:mm:ss
SELECT CONVERT(varchar(19), GETDATE(), 120)
UPDATE:

Depending on the input date format, an additional step may be necessary.
SELECT CONVERT(varchar(10),
  CONVERT(datetime, '20080730 00:00:00.000', 120), 120)

Importing data from DBF files using SSIS - configuring Connection Manager

This is an example Connection Manager configuration:

Importing data from DBF files using SSIS - configuring Connection Manager

The most important thing to remember is that the Data Source property should only contain a path to the folder with DBF files (e.g. c:\temp\3\), not a full path to the file (e.g. c:\temp\3\sales.dbf).
The particular file that should be used is selected later in the Name of the table or the view combo box in OLE DB Source configuration.

2007/04/06

Importing data from DBF files using SSIS on 64-bit SQL Server 2005 - errors during package execution

If you are trying to import data from DBF files using Integration Services (SSIS) and you are getting errors similar to:

Error: The AcquireConnection method call to the connection manager "DBF files" failed with error code 0xC0202009.

Error: component "DBF file source" (1) failed validation and returned error code 0xC020801C.

Error: An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".


check whether you are not running 64-bit version of SQL Server 2005. If so, go to project properties and set the Run64BitRuntime property to false.

I found it here.

UPDATE:

If you are then using dtexec.exe utility to run such a package and encounter similar errors, read this thread and use 32-bit version of dtexec.exe.

2007/03/28

HOWTO Inserting a file into image column using only Transact-SQL (SQL Server 2005)

CREATE PROC dbo.InsertBlob
  @id int
, @path varchar(255)
AS
  DECLARE @sql nvarchar(MAX)
 
  CREATE TABLE #BlobData(BlobData varbinary(max))
 
  --insert blob into temp table
  SET @sql =
      N'
      INSERT INTO #BlobData
      SELECT BlobData.*
      FROM OPENROWSET
          (BULK ''' + @path + ''',
          SINGLE_BLOB) BlobData'
  EXEC sp_executesql @sql
 
  --update main table with blob data
  UPDATE dbo.SOME_TABLE
  SET SOME_BLOB_FIELD = (SELECT BlobData FROM #BlobData)
  WHERE SOME_TABLE_ID = @id

  DROP TABLE #BlobData
GO
I found it here and I am happy I can finally stop using textcopy.exe tool.

HOWTO Getting 16x16 icons for file types

1. Download this sample.

2. Put a button and a textbox on the form.

3. Use a snippet similar to:
Dim types As String() = txtFileTypes.Text.Split(",")
For i As Integer = 0 To types.GetLength(0) - 1
  Dim im As Image = _
  Me.FileSystemImages.SmallImageList.Images( _
  FileSystemImages.GetTheFileIconIndex("*." & types(i)))
  im.Save(Application.StartupPath & "\" & types(i) & ".bmp", _
  Imaging.ImageFormat.Bmp)
Next
and you have a tool that allows you to retrieve 16x16 icons for different file types.

2007/03/19

Skype may prevent Outlook from closing properly

If you use Skype with the View / View Outlook Contacts option checked, Outlook does not close properly after you hit Alt+F4 or click the Close button. It disappears from the taskbar, but the OUTLOOK.EXE process continues to run and the Outlook icon stays in the system tray.

I think this is because Skype holds some kind of lock on Outlook's address book, but that's only my guess.

Here you can find some other applications that cause similar behaviour.

2007/03/06

A job step seems to hang when executing a console application

An application executed in an Operating System (CmdExec) job step has to be a console application and it cannot ask for any user input.

Even a simple Press any key to continue..., used (when the application is executed by a user, not SQL Server Agent) to prevent the command prompt window from closing before the user can see the results, will keep such a job running forever.

2007/03/02

Clipboard stops working while using remote desktop connection

If the clipboard suddenly stops functioning, when you are connected to a terminal server or other remote computer, you can try to do the following:

1. open Task Manager on the remote machine and kill the rdpclip.exe process;
2. start the rdpclip.exe application again (you can find it in c:\WINDOWS\system32\).

2007/02/10

Working with multiple terminal sessions simultaneously

If you often work with many terminal sessions opened in the same time, you should try Terminals.

My two favourite features of Terminals are:
  1. the tabbed view of the sessions that helps to unclutter the taskbar,
  2. the ability to create Favorites (session definitions) so I no longer need to have to store that information in separate files.
You can find some screenshots here.

Reading mail from root@localhost.localdomain using Thunderbird (a quick note)

1. Create a Unix Mailspool (Movemail) account.

2. Run the following command:
  sudo chmod 777 /var/spool/mail

2007/01/27

VMware, Ubuntu 6.10 & USB flash drives

Before Ubuntu 6.10, I had no problems to access USB flash drives from Windows running under VMware (guest system) with Ubuntu as a host system.

And then Ubuntu 6.10 came and the problems came, too. Sometimes, when I plugged the drive, Windows detected it and I could access the data on it (rarely) and sometimes Windows didn't detect it at all or did detect it but there was no access to the data (usually). And I could see no pattern in this behaviour.

After a few months I finally found this article 3862823 in the VMTN Knowledge Base - USB Devices Are Not Available on Some Linux hosts, the VM > Removable Devices > USB Devices Menu Is Empty.

Adding the following line:
  usbfs /proc/bus/usb usbfs auto 0 0
to the /etc/fstab file solved the problem.

2007/01/25

HOWTO: Edit properties of DataGrid columns (.NET 1.1)

Editing DataGrid properties
It's probably obvious for everyone working with Visual Studio 2003, but it doesn't work this way in VS 2005 (which I usually use) and as a result it recently took me a while to find this option.

Installing Ubuntu on ASUS A6RP-AP069 notebook (part 3 - WPA)

I don't know why, but WPA doesn't work on Ubuntu out-of-the-box. WEP does and we were using it for some time, but recently we finally decided to switch to WPA.

First we found these instructions, but for some reason they didn't work for us. And even if they did, this method is way too complicated.

After some further searching, we found this post. Really simple and it works! Although, after having followed the steps 1, 2 and 3 listed there, we also had to reboot the machine and add ifup eth1 to /etc/init.d/bootmisc.sh file.

2007/01/12

Reading data from a sorted view (Microsoft SQL Server)

If you read data from a sorted view (a view that contains an ORDER BY clause in its SELECT statement) into a DataTable (ADO.NET) or a ADODB.Recordset ('old' Visual Basic 6 ADO), it will not sorted in the DataTable/Recordset.

2007/01/01

Installing Ubuntu on ASUS A6RP-AP069 notebook (part 2 - headphones)

If you hear no sound after connecting headphones or external speakers to the headphones jack, try installing alsa-driver-1.0.14rc1, alsa-lib-1.0.14rc1 and alsa-utils-1.0.14rc1 packages and remove the options snd-hda-intel model=uniwill-m31 line from the /etc/modprobe.d/alsa-base file (if you added it before).

Thanks for the advice, mario_7!