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.