Great directors and actors

Just a list for myself, because I keep forgetting names.

Directors
  • Darren Aronofski: Pi, Requiem For A Dream, The Wrestler
  • Christopher Nolan: Memento, Insomnia, Batman Begins, The Prestige, The Dark Knight
  • Clint Eastwood: The Bridges Of Madison County, Mystic River, Million Dollar Baby, Letters From Iwo Jima, Changeling, Gran Torino
  • David Fincher: Se7en, The Game, Fight Club, Zodiac, The Curious Case Of Benjamin Button
Actors
  • Christian Bale: Empire Of The Sun, Equilibrium, The Machinist, Batman Begins, Harsh Times, The Prestige, 3:10 To Yuma, The Dark Knight
  • Joseph Gordon-Levitt: Mysterious Skin, Brick, The Lookout

Oracle SQL Developer screen redraw glitch

Perform the following steps to fix the redraw, or screen refresh, bug that can occur in Oracle SQL Developer when used on Windows Vista and Windows 7. They occur for example when scrolling horizontally or vertically through the records of a table when the records don't fit the window.

Start with just the sqldeveloper.conf adjustment. If that doesn't fix it, then perform all steps.

Steps to solve the issue:

  • Add or replace the attribute of the following config file.
    ..\sqldeveloper\bin\sqldeveloper.conf
    AddVMOption -Dsun.java2d.noddraw=true
  • Install the most recent jdk.
    Last seen here: http://java.sun.com/javase/downloads/index.jsp
    Select the Java SE Development Kit (JDK). At this time, JDK 6 Update 16 is the most recent version. Install it in C:\Program Files\Java\jdk1.6.0_16
  • Add or replace the attributes of the following config files.
    ..\ide\bin\jdk.conf
    SetJavaHome C:\Program Files\Java\jdk1.6.0_16
    ..\ide\bin\ide.conf
    AddVMOption -Xmx256M
    AddVMOption -Dsun.awt.keepWorkingSetOnMinimize=true

Remove visual rings / circles from pen tablet in Windows 7

When using a pen in stead of a mouse in Windows, rings or circles may appear when you click. This happens at least in Vista and Windows 7. I am using a Wacom Graphire pen and wanted to deactivate those. This is not as straightforward as it seems. The rings are part of the Tablet PC Input functionality of the OS - not of the Wacom drivers or application.

How to disable these rings in Windows 7?
  • Disable the "Tablet PC Input Service".
    This solved the problem for me for a while... until the rings came back for no apparent reason. I suspect because of an automatic update or updated Wacom driver, that reactivated the tablet pc ring feature.
  • Uninstall Tablet PC Components.
    Control panel -> Programs and Features -> Turn Windows features on or off -> uncheck Tablet PC Components. Windows needs to be restarted after this.
  • Kill the wisptis.exe process, e.g. via taskmanager.
    Then 1) rename \windows\system32\wisptis.exe to wisptis.bak, 2) create a textfile called wisptis.exe with nothing in it. Alternatively: open properties for wisptis.exe, go to security tab and "deny full control" for the "everyone" account. If "everyone" is not visible, click edit, click add, type in "everyone" and click ok.
I am not using a tablet pc, so these actions might have a negative impact when using one.

UPDATE 20100305: The wisptis.exe process still comes up when running some .Net applications. Even when the above tip is applied. Most probably it is copied from the protected cache / version history Windows keeps. To prevent this, download the tool "process blocker":

http://www.processblocker.com/download.html

Configure it to block "wisptis.exe" and now the rings are REALLY gone forever.

UPDATE 20120718: There is a registry fix to disable the rings:

http://viziblr.com/news/2011/8/14/the-ultimate-guide-to-making-your-wacom-tablet-work-on-windo.html

Download the fix from step 5. Try this first and if it doesn't work, then use the process blocker application.

UPDATE 20130410: ... or disable it via a group policy:
  1. Open Local Group Policy Editor:Run... gpedit.msc
  2. Navigate to User Configuration - Administrative Templates - Windows Components - Tablet PC - Cursors
  3. Enable the Turn off pen feedback setting.

Limited connectivity using wireless internet in Vista Home SP1

I've had an issue with "limited connectivity" on my Vista Home SP1 installation. It is a laptop that connects wirelessly (WiFi) to a router. The router is connected to the internet via DSL. The router is working fine and the connection to the internet is ok. However, after using the internet for some time, sometimes half an hour, sometimes minutes, the wireless connection displays a yellow exclamation mark with the message "limited connectivity". It is not the router, since rebooting the laptop fixes the issue... for some time, and then it happens again. After that, trying to open network connections, or any other application related to the network, hangs that application.

I've collected the following possible remedies. I'm not sure which one solved it in the end. Obvious remedies like "put the laptop wifi switch to ON", "put in a correct WEP key" (or any other encryption key) or "check if the router is turned on" are left out:
  • (20090618) I put this upfront, because all the tips that follow didn't resolve the issue for me, so I finally bought a new Wifi USB dongle. After buying a new laptop to add to my wireless network I noticed that the WEP encryption I was using didn't work for the new laptop. So in the end I changed protocols from WEP to the stronger WPA. After that, all PC's and even the Wii worked flawlessly. Even after switching back to the original wifi NIC on the laptop that first had the "limited connectivity" message, that laptop hasn't had the problem. So in short: try changing encryption protocols. Start with none (unsecured) to see if the problem is gone and then move on to stronger ones.
  • Update drivers. There is a great free tool to update almost all drivers on your system, called DriverMax. Just register and it can be used legitimately for 30 days. I am not sure why it is "freeware" if it states that you can "use it freely for 30 days". It is based around a community of users uploading drivers. It the tool detects a new driver, you can then update it. This is a bit tedious, but always better then scouring the internet manually. The current version of the driver that is going to be updated is uploaded to DriverMax first. It is stored in their driver base for other users.
  • Reset TCPIP stack. Execute the following statement at the command prompt:
    NETSH INT IP RESET
  • Reset Winsock. Execute the following statement at the command prompt:
    NETSH WINSOCK RESET. Or remove both winsock registry keys, reboot, and reinstall TCP/IP protocol: http://support.microsoft.com/kb/811259
  • Disable auto-tuning. Execute the following statement at the command prompt:
    NETSH INTERFACE TCP SHOW GLOBAL. Check if "Receive Window Auto-Tuning Level" is disabled. If not, then disable it:
    NETSH INTERFACE TCP SET GLOBAL AUTOTUNINGLEVEL=DISABLED. Re-enable with "=NORMAL".
  • Disable IP6 protocol. Uncheck IPv6 protocol on the network adapters and disable in the registry. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip6\Parameters, add or change DWORD key "DisabledComponents", set to hex "FF".
  • Disable other protocols. Additionaly uncheck QoS Packet Scheduler, Link-Layer Topology Discovery Mapper I/O Driver, and Link-Layer Topology Discovery Responder.
  • Disable power management on NIC. Right click the wireless network adapter in Device Manager, select properties, tab "Power Management" and uncheck the box "Allow the computer to turn of this device to save power". In my case it was turned back on again at a later point in time, but that could be due to installing a new driver afterwards.
  • Change wireless channel. Try different channel numbers for the NIC. Each channel number (mostly between 1 and 13) corresponds to a different frequency. The property can be set on the advanced tab of the driver for the NIC in device manager. Set the adhoc channel accordingly in the wireless router configuration.
  • Disable and re-enable network adapter. Apparently, this sometimes fixes issues.
  • Reboot in Safe Mode. A solution like the previous one. Reboot the system, press F8, select "safe mode". After it has fully restarted, reboot again. Windows starts normally again.
  • Run the System File Checker utility. Execute the following statement at the command prompt:
    SFC /SCANNOW. This is not of much of use to the majority of laptop owners, since they often do not own a copy of their Windows OS installation disk. When SFC finds a component (e.g. DLL) on the system, that is different from the one that was installed during the original installation, then it needs to get that original one from the installation disk.
  • Check eventviewer. I have error messages concerning a NTIOMIN service that cannot start, TCPIP.SYS signing issues, and Kerberos related messages (can supposedly be ignored when the laptop is not part of a domain). Still haven't figured out the first two. I'm also getting "informational" events about the NETw5v32 wireless driver. This is related to an incorrect driver.
  • Run registry or system cleaners: nice tools to run are HijackThis and CCleaner. The first can be used to remove questionable or corrupt services, browser helper objects, protocols, etc. The latter cleans the registry, removing orphanaged keys.
  • Repair wireless. Right click the wireless network icon in the notification area and select "diagnose and repair". Alternative method. Execute the following statement at the command prompt:
    %windir%\system32\rundll32.exe ndfapi,NdfRunDllDiagnoseIncident. Do this before "limited connectivity" message pops up. In my case it displayed a message: "TCP/IP settings not optimized", and it optimized them. Running it again didn't display the message again, so I guess it did something the first time.
  • Run Intel PROSet/Wireless diagnostics. If using Intel on-board wireless (I am using 3945ABG), then you can run the diagnostics utility. Execute the following statement at the command prompt:
    %windir%\system32\iPROSet.cpl. Run "diagnose" to check hardware, driver, radio, etc. Run "statistics" to check beacons, transmit errors, and power and power levels.
  • Downgrade driver. Sometimes the most recent driver is not the best one for your device. Some drivers available are: NETw3v32, NETw4v32 (11.5.0.34), NETw5v32 (12.2.0.11). In my case, the NETw4v32 driver gave 100% signal quality, while a NETw3v32 driver gave only 60%. The NETw5v32 filled my eventlog with messages (see remark elsewhere).
  • Set router to G-Only: put the router in network mode "G-Only". This way, the older B-mode is not supported, but throughput is improved.
  • (Run Network Diagnostics tool. Execute the following statement at the command prompt: NETSH DIAG GUI. Check all scanning options and start. This should work in Vista when the command prompt is started with admin rights, but I couldn't get it to work)
  • (NIC settings. If supported, try putting the adapter in full-duplex mode, or if it is already, in half-duplex mode. Experiment what works best. I cannot do this myself and wonder if this is even possible for a wireless adapter?)
  • (Run netdiag tool. Unfortunately, this tool is only availabe in Windows XP as part of "Microsoft Windows XP Support Tools". So, only for those using Windows XP, execute the following statement at the command prompt:
    NETDIAG /TEST:WINSOCK /V. Use the outcome of the test for further analysis.)

Links: http://ask-leo.com/what_is_limited_connectivity_and_how_do_i_fix_it.html

Simple way to read Excel or Access data in SQL Server

-- list of available providers
exec master..xp_enum_oledb_providers

-- Three ways to read in Access data (file: c:\db1.mdb, tabel: TEST)

1a) Using openrowset with older MS Access version:
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM TEST')

1b) For 2010 or more recent (*.accdb)
SELECT * FROM OPENROWSET('MSDASQL', 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM MAANDEN')

2) Using opendatasource:
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\db1.mdb;')...[TEST]

3) Using a linked server called TEST for 2010 or more recent:

EXEC sp_addlinkedserver
@server= N'TEST',
@srvproduct = N'MSDASQL',
@provider= N'MSDASQL',
@provstr= N'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\db1.mdb;Uid=;pwd=;'

-- Read in Excel (file: c:\employee.xls, worksheet: TEST)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Excel Driver (*.xls)};Dbq=C:\employee.xls;Uid=;pwd=;','SELECT * FROM [TEST$]')

-- Insert into text file
insert openrowset( 'Microsoft.ACE.OLEDB.12.0', 'Text;HDR=yes;Database=c:\tmp\', 'select * from test.csv') (testname, testaddress) select testname, testaddress from vw_test;

Note:
  • MS Access needs to be installed on the same server where SQL Server is installed
  • Make sure that the use of ad hoc names is allowed:
    exec sp_configure 'show advanced options', 1;
    RECONFIGURE;
    exec sp_configure 'Ad Hoc Distributed Queries', 1;
    RECONFIGURE;
Possible error messages and solutions:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

Give read/write access to "everyone" on the server to the following folder:
C:\Users\<SQL Server Service Account>\AppData\Local\Temp

Make sure this is set on the server where the code is running, i.e. on the client PC where the code is executed within Management Studio.

Converting WAV DTS to MP3

A WAV DTS file contains a multi-channel encoded digital format called DTS and has .wav as extention. Playing such a file in Zoomplayer or WinAmp typically produces a static sound. This is because a filter and/or decoder are not installed.

For Zoomplayer a filter can be installed (Gabest AC3), but a separate DTS decoder is still needed. Ffdshow contains such a decoder, but setting up this mapping in advanced options -> customized media playback -> source filters and splitters didn't help. Zoomplayer keeps displaying the "An ERROR has occured!" messagebox when trying to play the WAV DTS file.

Foobar2000 used to have an additional component, foo_dts.dll, that could be downloaded from the site, but it is no longer available. I was unable to find out why it is no longer supplied. After searching the internet I found a version of the library, but foobar2000 0.9.5 refused to load it, reporting that it was an outdated version.

A player that can play them out-of-the-box is VLC player. This player has a built-in filter and decoder to play WAV DTS files. It can also transcode files, e.g. to MP3 format, but when I use it on a WAV DTS file, the program terminates unexpectedly.

So, in the end, I was unable to find a "one-click" solution. Here is how I finally managed to convert a WAV DTS file into a MP3 file:

For the example, I use a WAV DTS file named c:\wavdts.wav

  1. Download BeSplit (http://www.afterdawn.com/software/audio_software/audio_tools/besplit.cfm). I used version 0.9b6.2.
  2. Extract the zip file to c:\
  3. Start BeSliced.exe
  4. Drag and drop wavdts.wav onto the besliced canvas
  5. Select "DTS" when asked to select a format
    BeSplit is now ripping the DTS stream from the WAV DTS file.
    To use BeSplit without BeSliced, execute this command at the command prompt:
    BeSplit.exe -core( -input c:\wavdts.wav -fix -logfile C:\BeSliced.txt -type dtswav -output c:\wavdts_Fixed.dts ) -profile( BeSliced v0.3 )
    A file called c:\wavdts_Fixed.dts is created.
  6. Download DTSDec (http://www.rarewares.org/others.php). I used version 0.0.2.7.
  7. Extract the zip file to c:\
  8. Execute this command at the command prompt:
    dtsdec -o wav > regularwav.wav wavdts_Fixed.dts.
    DTSDec is now transcoding multi-channel DTS to a regular two-channel WAV file called regularwav.wav
  9. Use your favorite MP3 converting tools, e.g. AudioGrabber with LAME, to convert this WAV file to a MP3 file.

Money pit on Oak Island

Very bizarre story:
http://www.activemind.com/Mysterious/Topics/OakIsland/

I've put it here so I don't forget to check progress on the digging from time to time.

Update: an anonymous poster pointed me to the site http://www.oakislandtreasure.co.uk/. Check it out, as there are updates to this story!

Drupal Clean URL's with IIS7

Drupal can work with clean URL's (or URL write). The most talked about solution is using Helicon's ISAPI _Rewrite ISAPI add-in, which can be found here (http://www.isapirewrite.com/) if you are interested. But here I want to show you how you can accomplish clean url's using a free alternative by Ionics called IIRF.

Perform the following steps to get clean URL's in Drupal 5.3 in IIS7:

1) Download Ionics Isapi Rewrite Filter
At the moment of writing this is the only quite reliable freeware URL rewriter for IIS. You can get it here:
http://www.codeplex.com/IIRF/Release/ProjectReleases.aspx?ReleaseId=5018
Download the most recent "-bin.zip" version.

2) Install Ionics Isapi Rewrite Filter
- Extract IsapiRewrite4.dll to c:\inetpub\wwwroot (or your www root folder)
- Extract \examples\DrupalRules.ini to c:\inetpub\wwwroot (or your www root folder) as IsapiRewrite4.ini

If you haven't installed Drupal in the webroot, but, like me, in a subfolder, then you have to perform the following updates to DrupalRules.ini. I use http://localhost/drupal, so I had to add
add "/drupal" to almost every RewriteRule. In stead of "/drupal" you can use any folder or level of subfolders that is necessary in your case, e.g. for http://localhost/davy/dozy/tich you have to replace the bold "/drupal" string with "/davy/dozy/tich".

Here are some examples for each of the sections defined in DrupalRules.ini:

RewriteRule ^/misc/(.*)$ /misc/$1 [I,L]
RewriteRule ^/modules/tinymce/(.*)$ /modules/tinymce/$1 [I,L]
RewriteRule ^/cron\.php$ /cron.php [I,L]
RewriteRule /(.*)\?(.*)$ /index.php\?q=$1&$2 [I,L]
RewriteRule ^/(.*)$ /index.php?q=$1 [I,L]

become

RewriteRule ^/drupal/misc/(.*)$ /drupal/misc/$1 [I,L]
RewriteRule ^/drupal/modules/tinymce/(.*)$ /drupal/modules/tinymce/$1 [I,L]
RewriteRule ^/drupal/cron\.php$ /drupal/cron.php [I,L]
RewriteRule /drupal/(.*)\?(.*)$ /drupal/index.php\?q=$1&$2 [I,L]
RewriteRule ^/drupal/(.*)$ /drupal/index.php?q=$1 [I,L]

The following RewriteRule has to be added to the first RewriteRule-section If you also use Gallery2:

RewriteRule ^/gallery2/(.*)$ /gallery2/$1 [I,L]

or if you use http://localhost/drupal

RewriteRule ^/drupal/gallery2/(.*)$ /drupal/gallery2/$1 [I,L]

The RewriteRule function uses regular expressions to transform clean url's back to their non-clean equivalents.

3) Add ISAPI filter to IIS7
- Start IIS.msc
- Select Default Web Site (not your virtual directory or application!)
- Double-click the ISAPI filters icon
- Add...

Filtername: IsapiRewrite4
Executable: C:\inetpub\wwwroot\IsapiRewrite4.dll

4) Set Drupal to use Clean URL's
- Start Drupal in your web browser
- Administer -> Clean URLs
- Click "Test for Clean URL's" (it is somewhat hidden in the paragraph text)
- Select enable

In my case, the enable check box was still greyed out. To set Clean URL's manually:

- Remove write-protection from the c:\inetpub\wwwroot\drupal\sites\default\settings.php file
- Edit the file and add:

# Force Clean urls
$conf['clean_url']=1;

- Set write-protection back.

Hopefully this quick and rudimentary instruction helps you setting up URL rewriting for you Drupal installation.

Liger

I saw a documentary today on National Geographic about the cross-breeding product of a tiger and lion. The hybrid is called a liger. I didn't know that such a beast exists.

You can read more about it here:
http://en.wikipedia.org/wiki/Liger

Fix upload of images in Drupal

Solution to messages like

warning: move_uploaded_file() [function.move-uploaded-file]: Unable to move 'c:\progra~1\php\upload_tmp\php525F.tmp' to '' in C:\inetpub\wwwroot\drupal\includes\file.inc on line 244."

when trying to upload images to Drupal 5.3.

Perform or check these steps (I'm using Vista and IIS7):

1) Create temporary upload folder for php
php must have an upload folder to be able to upload files. Create one called upload_tmp under the install root of php. So if php is installed here:

c:\progra~1\php\

then the full path to the upload folder will be:

c:\progra~1\php\upload_tmp

2) Change php.ini
Change the settings to the following values:
file_uploads = On
upload_tmp_dir = "c:\progra~1\php\upload_tmp"
upload_max_filesize = 2M

3) Create temporary preview and upload folder for Drupal
Drupal must also have an upload and preview folder to be able to upload and preview files.

Create the following folders under the drupal root.
files
files\tmp

So if drupal is installed here:

C:\inetpub\wwwroot\drupal\

then the full path to the upload and preview folders will be:

C:\inetpub\wwwroot\drupal\files
C:\inetpub\wwwroot\drupal\files\tmp

In my case it worked without setting permissions on these folders, but if it doesn't, you can try giving the web service user access.

Give user IUSR permission to read/write on these folders:

- Right click folder- Properties -> security -> edit -> add -> advanced -> find now
- Select IUSR, ok
- Select full control, ok, ok

4a) Change drupal settings (upload module)
Activate the upload module:

In Administer -> Site building -> Modules, activate module Upload

4b) Change drupal settings (folders)
In Administer -> Site configuration -> File system, set the following values:

File system path: files
Temporary directory: files/tmp

The temporary directory must be a subfolder of the file system path folder!

Now the upload should work.

Install Windows Components from command-line

If you want to start the add/remove Windows Components dialog without using the add/remove programs interface, then use the following command:

Sysocmgr.exe /i:sysoc.ini

IE7 enable tabbed browsing

You can enable tabbed browsing in Internet Explorer 7 by using a menu command:

Tools -> Internet Options -> Tab: settings -> select Enable Tabbed Browsing.

However, sometimes this is not enough, for example because the domain administrator has "disabled" tabbed browsing using a registry key. You can enable it by putting the next registry entry in a .reg text file (e.g. c:\tabbrowse.reg) and executing it:
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\Explorer]"ClassicShell"=dword:00000000
... or you can set the DWord keyvalue to 0 by hand.

If the key value gets restored every time you log in to your system, you could add the execution of registry file to the startup folder:

- create a bat file, e.g. tabbrowse.bat
- add the following line to the bat file: regedit /s c:\tabbrowse.reg
- add the bat file to the startup folder

Dynamic transparent color

The default value of most color properties is "Transparent". If you want to dynamically set a color property, you can use the IIF-statement. This statement has the following signature:
=IIF([boolean],[return value if true],[return value if false])
A problem lies in the fact that both the true and the false part are obligatory. For setting the color the following IIF-statement can be used:
=IIF(ReportItems!txtBox.Value=5, "Palegreen", "Transparent")
But, the value Transparent does not exist! You cannot set it! Visual Studio throws the following warning:
The value of the background color property for the textbox ‘textbox’ is “Transparent”, which is not a valid background color.
It turns out that this property is not exported to the generated XML file, called rdl, if it is not explicitly set. The IIF-statement forces you to set it, but the value Transparent cannot be used. How to dynamically set the color property then?

You could use the value White instead. However, the background color for example gets rendered after the borders, so a white background covers part of a visible cell border. This is clearly visible on the report. Very annoying...

The only solution I could come up with is using a function that does not have an explicit return type. Then, if there is no return value, a non-existing object is returned.

Like this:
function Rank(val1)

if val1=5 then Rank = "Palegreen"

end function
Can you have the IIF-statement return a null (uninitialized object) value? The System.DBNull value that has replace the null value is just a type, not a value. So, this doesn't work.

Truncate staging tables

Before creating a backup of the database, you could clean out all staging tables to safe some space in the backup and to reduce the time it takes to make the backup. You could use the following simple SQL-loop statement:
EXEC sp_MSforeachtable "IF LEFT('?',11)='[dbo].[STG_' BEGIN PRINT '?' TRUNCATE TABLE ? END"
The statement expects a strict naming convention: all staging table names should start with STG_ and this prefix should not be used for any other table types. Also make sure you do not have any "permanent" data in your staging environment.

Note:
The PRINT-statement can be left out. It could help you debug the statement. Also the truncate statement does not work if there are foreign keys referencing the table.

Windows Update fix

Sometimes Windows Update gets corrupted. Some of its components or previously downloaded updates get mangled during the download or installation process. Here's a quick fix to clean out the folders that it uses (put the script in a batch file):
@echo off

NET STOP "Automatic Updates"

SET D=%windir%\SoftwareDistribution\DownloadDEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

SET D=%windir%\SoftwareDistribution\DataStoreDEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

SET D=%windir%\system32\CatRoot2DEL /q %D%
FOR /F %%f IN ('dir %D% /b /A:D') DO RD /s /q %D%%%f

NET START "Automatic Updates"
On execution, it wil re-download the main components and give you a "fresh" Windows Update installation.

Cannot decrypt the symmetric key...

Yesterday I got a Windows update of my .Net framework 2.0. After the update the system needed to be rebooted, so I did that. After that, my reportmanager in Internet Explorer returned with the following error:
The report server cannot decrypt the symmetric key used to access sensitive or encrypted data in a report server database. You must either restore a backup key or delete all encrypted content and then restart the service. Check the documentation for more information. (rsReportServerDisabled)
The Reporting Services service (RSS) uses an asymmetric key to decrypt a symmetric key. This symmetric key is used to communicate with its repository in SQL Server. The asymmetric key is tied to the account with which the RSS runs. Change the account and the key is invalidated. Apparently, the update did something to this account, although it was "LocalSystem" and still is "LocalSystem".

Fortunately, the problem is easily solved by issuing the following statement at the command prompt I found on Kate Gregory's blog posted by a guy named Derek:
rsactivate -r -c"C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config"
It re-creates the assymetric key for the service account and stores it again in the rsreportserver.config file.

Return the size of pre-selected tables

A stored procedure I use a lot to get a resultset containing the size of a selection of tables in the database:
CREATE procedure sp_TableSize as
begin
SET ANSI_WARNINGS OFF

CREATE TABLE #temp
(
TABLENAME varchar(100),
ROWS int,
RESERVED varchar(25),
DATA varchar(25),
INDEX_SIZE varchar(25),
UNUSED varchar(25),
)

DECLARE @table nvarchar(100)

-- Select the tables for which to retrieve sizes
DECLARE cDWHTables cursor read_only for
SELECT name FROM sysobjects
WHERE LEFT(name, 4) in ('STG_','ODS_','DIM_','SYS_','FCT_','RPT_') OR
LEFT(name,3) = 'MD_'

OPEN cDWHTables

-- Store each record retrieved from sp_spaceused in temp table
FETCH NEXT FROM cDWHTables INTO @table
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
insert #temp exec sp_spaceused @table
END
FETCH NEXT FROM cDWHTables INTO @table
END

UPDATE #temp SET
reserved = left(reserved, len(reserved) - 3),
data = left(data, len(data) - 3),
index_size = left(index_size, len(index_size) - 3),
unused = left(unused, len(unused) - 3)

INSERT #temp
SELECT 'TOTAL' as tablename, sum(rows) as rows,
sum(convert(int,reserved)) as reserved,
sum(convert(int,data)) as data,
sum(convert(int,index_size)) as index_size,
sum(convert(int,unused)) as unused
FROM #temp

--Return the results as data set
SELECT
TABLENAME = tablename,
ROWS = convert(int,rows),
CONVERT(int,reserved) as RESERVED,
convert(int,data) as DATA,
convert(int,index_size) as INDEX_SIZE,
convert(int,unused) as UNUSED,
AVG_DATA = convert(numeric(18,2),case when rows>0 then convert(float, data) / convert(float, [rows]) else 0 end),
TOTAL = convert(int,data) + convert(int,index_size),
TOTAL_MB = (convert(int,data) + convert(int,index_size))/1024
FROM #temp

-- Clean up
DROP TABLE #temp
CLOSE cDWHTables
DEALLOCATE cDWHTables

end
Replace the bold part to set the tables for which to return the size. After all records have been returned it adds a final total row to the set.

Note:
The insert of the total row can alternatively be written using the COMPUTE-clause on the main select statement.

Backup to network share

You can use the following TSQL statement to backup a database to a network share in SQL Server 2000:
BACKUP DATABASE TO DISK = '\\unc\folder\filename.bak'
WITH RETAINDAYS=0, INIT
This will create a full backup each time it is executed. The RETAINDAYS=0 option makes sure that the previous backup will always get overwritten. Not the safest thing, but can be needed if you are in a tight spot regarding disk space.

This backup file will also appear then in Enterprise Manager. Select Tools, Backup database and check the destination listbox.

To check if the backup is complete and readable:
RESTORE VERIFYONLY FROM DISK = '\\unc\folder\filename.bak'
This way you can have more control over your backup procedure than using maintenance plans, e.g. by putting them in a Execute SQL Task in a DTS package.

Alternatively, using Enterprise Manager, you can add a backup device via management\backup and have in point to \\unc\folder\filename.bak. Right-click it, select "backup a database" and click "Ok".

Note:
It is not recommended to backup directly to a network share, but sometimes there is no alternative.

Note 2:
Setting the RETAINDAYS property to e.g. 5 days means it will be impossible to overwrite the backup file during the next 5 days! Beware of that!

Concatenating text files

Jamie Thomson has a post on concatenating multiple text files (using the ForEach Loop) and subsequently loading this concatenated file in one go. You can find the post here:

http://blogs.conchango.com/jamiethomson/archive/2006/06/22/4116.aspx

I use to do this with a small batch file. The batch file is called concat.bat and takes in the following parameters: %1 for the extension of the files to concatenate, e.g. txt and %2 for the folder in which the files are stored.

The body of the batch file is as follows:
set I=
for /F %%f in ('dir "%2"*.%1 /B /O:N /A:-D') do set I=%%f
if not defined I goto EXIT
copy "%2"*.%1 "%2"\backup
copy "%2"*.%1 "%2"%I:~0,5%concat.tmp
if exist "%2"%I:~0,5%concat.tmp del "%2"*.%1 > NUL
ren "%2"*concat.tmp *.%1
:EXIT
Example of the usage of the batch file:
The batch files is called with %1 = .txt and %2 = c:\temp. Say there are three files in c:\temp called test1.txt, test2.txt, and test3.txt with create dates 2006/01/01, 2006/01/02, and 2006/01/03 respectively. The result is one textfile called text1.txt containing the content of all three files.

Explanation of each line of code:

Set I=
dimensions the variable I.

for /F %%f in ('dir "%2"*.%1 /B /O:N /A:-D') do set I=%%f
iterates over all files in folder c:\temp with extension ".txt", ordered descending by create date. Each iteration "do set I=%%f" assigns the filename to variable I. After iteration I contains the value of the last filename in the list. This is the file created first according to create date, since the list is sorted decendantly.

if not defined I goto EXIT
checks if I has a value, if not, the batch ends. I.e. there where no .txt files in the folder.

copy "%2"*.%1 "%2"\backup
copies the three sourcefiles to a backup folder. This is done for auditing purposes.

copy "%2"*.%1 "%2"%I:~0,5%concat.tmp
concatenates all .txt files to one file with the filename stored in I. So, a file called test1concat.tmp is created, containing the union/concatenation of the three files.

if exist "%2"%I:~0,5%concat.tmp del "%2"*.%1 > NUL
does a check to ensure the .tmp file has been created and deletes the three source files, so the inbox in emptied.

ren "%2"*concat.tmp *.%1
renames the .tmp file to the final filename, in this case text1.txt.

EXIT:
exits the batch file.

Currently it only works if all files have the same filename length, so 5 (="textX") in the case of the example. Of course this can be optimized, e.g. search for the first dot and take the left part of the file name.

I have no idea if this approach is better with regard to performance. I haven't tested it on thousands of files and have not done a comparison to the approach used in the post by Jamie. On the other hand, this approach will also work nicely in DTS, where there is no ForEach Loop.

Note:
The "for /F" functionality is not available in all Windows versions. It is available in Windows 2003 server and Windows XP.

Strange HAVING-clause behaviour

Say you want to return a resultset containing the last two days per week. First, construct the demo table:
CREATE TABLE TEST_TIME (WEEKCODE INT, ISODATE CHAR(8))
INSERT INTO TEST_TIME VALUES (1, '20060101')
INSERT INTO TEST_TIME VALUES (1, '20060102')
INSERT INTO TEST_TIME VALUES (1, '20060103')
INSERT INTO TEST_TIME VALUES (2, '20060108')
INSERT INTO TEST_TIME VALUES (2, '20060109')
INSERT INTO TEST_TIME VALUES (2, '20060110')
INSERT INTO TEST_TIME VALUES (2, '20060111')
The following SQL query will give the desired resultset:
SELECT t.WEEKCODE, t.ISODATE FROM TEST_TIME t
WHERE t.ISODATE >=
(
SELECT TOP 1 ISODATE FROM
(
SELECT TOP 2 ISODATE
FROM TEST_TIME t2
WHERE t.WEEKCODE = t2.WEEKCODE
ORDER BY ISODATE DESC
) t1
ORDER BY ISODATE ASC
)
ORDER BY t.WEEKCODE
It returns 4 records, as espected:
1 20060102
1 20060103
2 20060110
2 20060111
Just for fun, I tried the same thing using a GROUP BY and HAVING-clause like this:
SELECT t.WEEKCODE, t.ISODATE FROM TEST_TIME t
GROUP BY t.WEEKCODE, t.ISODATE
HAVING t.ISODATE >=
(
SELECT TOP 1 ISODATE FROM
(
SELECT TOP 2 ISODATE
FROM TEST_TIME t2
WHERE t.WEEKCODE = t2.WEEKCODE
ORDER BY ISODATE DESC
) t1
ORDER BY ISODATE ASC
)
ORDER BY t.WEEKCODE
Far less efficient, but one would expect it to return the same resultset. But it does not! In fact it returns 28 records:
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060102
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
1 20060103
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060110
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
2 20060111
That is even more than there are in the table! This is very strange behaviour. Stripping the HAVING-clause returns 7 records, as expected. But adding it results in a cartesian product of the 7 source records by the 4 top-2 records (2 for week 1 and 2 for week 2) resulting in 7x4=28 records.

From BOL:
"The HAVING clause is then applied to the rows in the result set that are produced by grouping. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function."

So, first the grouping, THEN an extra filtering on that set. How can filtering 7 records result in 28 records? Furthermore, the 28 records are not distinct records. Something you would expect when using a GROUP BY-clause...

Conclusion is that in case of a correlated having-clause query the having-clause is evaluated before the group by-clause.