SSIS package configuration files

SSIS package may ignore a configuration file (the dtsConfig file) and use variable values stored in the package (the dtsx file), if the configuration file contains one or more special Polish characters (even if there are used only in comments - <!-- the comments go here -->).

I have no idea when or why it happens, but I have a package that worked fine, then started to behave strangely and then it all came back to normal, when I removed special Polish characters from the comments in the configuration file. The bug is reproducible.

HOWTO: Format nicely an XML document (beautifying XML)

From time to time I have an XML file that needs to be formatted nicely. If the file is small, I usually do it manually.

However, lately I was working with SSIS. I created a package with an XML configuration file. And it came out that Visual Studio 'thinks' it is a great idea to put all content of the file in one line. Well, it is not, but I have already got used to such bugs in SQL Server 2005.

The configuration file was totally unreadable, but I remembered that I had already had such problem in the past. After some searching, I downloaded XMLStarlet (that I had also used last time).

All you have to do to format nicely an XML file is to type:

xml.exe format ugly_formatted.xml > nicely_formatted.xml

Of course, XMLStarlet can do much more.

Remote query timeout

If you are using linked servers and encounter the following error:

OLE DB provider "SQLNCLI" for linked server "SRV_2000" returned message "Query timeout expired".

check your remote query timeout setting.

To do it execute this query:
  sp_configure 'remote query timeout (s)'

or go to:

(if you're lucky enough to still be using SQL Server 2000 Enterprise Manager)

or to:

(if you have to use SQL Server 2005 Management Studio, that failed to accomplish such an easy task - not being worse that Enterprise Manager - I'll rant about this more someday)

It is also worth remembering that if you are executing a remote query in a step of a job and this fails because of the timeout, the step will be written to job's history with status 'Successful':

Executed as user: DOMAIN1\sqlagent2005. OLE DB provider "SQLNCLI" for linked server "SRV_2000" returned message "Query timeout expired".
[SQLSTATE 01000] (Message 7412). The step succeeded.

SQL Server Agent - multiserver administration (MSX/TSX configuration)

The sp_msx_enlist stored procedure adds the current server to the list of target servers available for multiserver operations. Only a SQL Server 2005 server can be enlisted against another SQL Server 2005 server.

That means that you cannot have a configuration with a SQL Server 2005 instance acting as a master server (MSX) and a SQL Server 2000 as a target server (TSX).

If you try to enlist a SQL Server 2000 instance into a SQL Server 2005 master server, you will receive an error like this:

The master server '<SRV_2005>' version 9.00.2047.00 is not compatible with the target server '<SRV_2000>' versions 8.00.2039.

(By the way, there is something wrong with this sentence, isn't it? I mean the word 'versions'.)

Management Studio showing negative values of free space in tempdb database (screenshots)

'Dedicated administrator connections are not supported. (ObjectExplorer)' error

Dedicated Administrator Connection (DAC) is a new feature of SQL Server 2005; it is a special diagnostic connection for administrators that can be used to connect to a server when standard connections are not possible.

If you are trying to connect to a server using the DAC with SQL Server Management Studio and you keep getting the following error:

Dedicated administrator connections are not supported. (ObjectExplorer)

it means that you are trying to connect Object Explorer using the DAC.

Object Explorer cannot connect using the DAC; only Query Window can. That means that you cannot press the New Query button; you have to use the Database Engine Query button.

HOWTO: Calculate MD5 checksum in Windows (and other checksums, too)

On Linux that's trivial - there is the md5sum program.

Windows does not come with such a tool, but you can use md5deep package.

md5deep is a cross-platform set of programs to compute MD5, SHA-1, SHA-256 Tiger, or Whirlpool message digests on an arbitrary number of files. The programs run on Windows, Linux, Cygwin, *BSD, OS X, Solaris, and should run on most other platforms. md5deep is similar to the md5sum program found in the GNU Coreutils package (...)

HOWTO: Perform a scheduled shutdown without installing additional software

The easiest way of performing a scheduled shutdown of a Linux computer, that came to my mind, was executing something like this:

  sleep 3600 && poweroff

However, this couldn't work, since root privileges are necessary to run poweroff command. So you have to use:

  sleep 3600 && sudo poweroff

But this will cause the computer to wait for 1 hour (3600 seconds) and then prompt for the root password. And what we are trying to avoid is sitting in front of the computer at that time.

But there is a very simple solution:

1. Type sudo visudo to edit the /etc/sudoers file.
2. Add the following line:

    your_username ALL= NOPASSWD: /sbin/poweroff

Now you can execute sudo poweroff without typing the password.

ISNULL, implicit conversion and integer data type

SELECT ISNULL(CAST(NULL as int), '+')
0

SELECT ISNULL(CAST(NULL as int), '-')
0

SELECT ISNULL(CAST(NULL as int), '/')
Server: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '/' to data type int.

To explain the above one must to remember that:

1. ISNULL returns the same type as the check expression (that's the 1st parameter),
2. varchar and char data types can be implicitly converted to int (if you don't know what it means, try executing SELECT 2 + '2'),
3. SQL Server treats '+' and '-' characters as a number - 0.

So this is what happens - ISNULL tries to return int data type and it converts the replacement value (that's the 2nd parameter); it succeeds in 2 cases, but fails in the 3rd one, because '/' is not a numeric expression.

Linux applications - languages

If a Linux application starts and the interface is translated into some language that you don't want to use (or even worse - it is partly translated, as it was in my case, so names in the menus were a mix of English and Polish words), you can go to:

/usr/share/locale/[language_folder]

and delete [application_name].mo file.

Rather a dirty hack, but it works.

You will need root privileges, of course.