Locks & Latches

Annoying search engine abroad question

2011-12-03T09:50:00.003+01:00

Google Chrome asks you if you would like to use another omnibox search engine when you move from one country to another. When you move across the border a lot, then this becomes quite an annoying message:

"Would you like to search with google.country1 instead of google.country2?"

If you always want to use e.g. google.nl, then make the following change:

Wrench -> Options -> Basics -> Manage search engines
Scroll to the bottom and fill in the columns:
In the first column: GoogleNEW
In the second: Google
In the third: http://google.nl/search?{google:RLZ}{google:acceptedSuggestion}{google:originalQueryForSuggestion}{google:searchFieldtrialParameter}{google:instantFieldTrialGroupParameter}sourceid=chrome&ie={inputEncoding}&q=%s
Hit Enter/Return, else the results will no be saved
Hover over the third column and click "make default"

Quipu connections

2011-10-05T10:32:00.007+02:00

Several ways to create a database connection in the open source data warehouse generator Quipu.

SQL Express

SQL Server configuration manager ->

Protocols for SQLExpress -> TCP/IP: enabled
TCP/IP -> IP Adresses -> IP All -> TCP Dynamic Ports: empty
TCP/IP -> IP Adresses -> IP All -> TCP Port: 666

SQL Server management studio -> SQL Express server properties -> Security -> Server Authentication: SQL Server and Windows Authentication mode
Create a SQL Server authentication login on the database server and assign it to the database that will be used by the connection, e.g. login: quipu, password: quipu
If the database runs on the same machine as Quipu, use "localhost" as the host name. Otherwise use the remote host name

Note:

Do not use the instance name, e.g. using "AdventureWorks;instance=sqlexpress" as database works, but will, depending on the SQL Server version, result in an "unable to get information" error.

Live calendar displays incorrectly in Chrome

2011-07-27T11:53:00.004+02:00

At last the Windows Live calendar displays appointments correctly again in the latest Google Chrome beta release, 14.0.835.2. They fixed the improper displaying of events.

Up until now you had to use a workaround to have the bars visible in the month view:

Download the extention Stylish
Download the style "Hotmail calendar events fix for Chrome browser"

Working with dates in OBIEE

2011-02-22T12:56:00.041+01:00

Here are several options to set date formats in OBIEE. Option #1 is the best way to work with dates and comparison of dates to strings containing dates in a particular format.

See also: http://gerardnico.com/wiki/dat/obiee/cast_as_date

The most important settings (with example values) are:

DATE_DISPLAY_FORMAT parameter in nqsconfig.ini: YYYY/MM/DD
DATESHORTFORMAT parameter in localedefinitions.xml (based on the locale set in nqsconfig.ini): M/D/YYYY
NLS_DATE_FORMAT parameter of the session (or database default): DD-MON-RR

Do not confuse these with parameters in dbfeatures.ini. There this format is the default for almost all database platforms in parameter DATE_FORMAT parameter.

Method 1 is used for implicit casting only if physical -> Database -> Features: CAST_SUPPORTED is available and enabled. Otherwise the settings described in method 4 (nqsconfig.ini) are used.

1) Tell Oracle how to interpret the casting of a string to date or vice versa (preferred method)

Explicitly set the Oracle NLS property for this to work unambiguously. This property can be set on a database level, affecting all connections made to the database. Alternatively, the property can also be set on the OBI connection in the physical layer of the Admin tool to only affect the current session:

Double-click "Connection pool" for the Oracle connection
Select "Connection Scripts" tab
Add "Execute on Connect" script and type:
ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD'

Then, use the following function in Answers formulae:

CAST('20110215' TO DATE)

Note: to check the value of the NLS_DATE_FORMAT parameter at the database level, e.g. from SQL Developer, execute the following statement:

SELECT * FROM (

SELECT 'instance' as domain, parameter, value FROM NLS_INSTANCE_PARAMETERS UNION

SELECT 'database' as domain, parameter, value FROM NLS_DATABASE_PARAMETERS UNION

SELECT 'session' as domain, parameter, value FROM NLS_SESSION_PARAMETERS

) x WHERE PARAMETER ='NLS_DATE_FORMAT'

2) Convert a date to string or vice versa using an Oracle database function

Use the OBIEE function EVALUATE to make use of an Oracle database function. In this case, TO_DATE:

Convert string to date:
EVALUATE('TO_DATE(%1,%2)' AS DATE,'20111231','YYYYMMDD')

Examples of what doesn't work:
EVALUATE('TO_DATE(%1)' AS DATE,'20100101')

--> does not work, unless the date happens to be in the nls parameter format

CAST('20110215' TO DATE)

--> does not work, unless the string happens to be in the nls parameter format

Convert date to string:
EVALUATE('TO_CHAR(%1,%2)' AS CHAR,DIM_DATE.TODAY,'YYYYMMDD')

EVALUATE('TO_CHAR(%1,''YYYYMMDD'')' AS CHAR,DIM_DATE.TODAY)

Examples of what doesn't work:
Conversions may fail with "date format not recognized" or "datetime value does not match the specified format".

EVALUATE('TO_CHAR(%1,%2)',DIM_DATE.TODAY,'YYYYMMDD')
--> does not work, because the result needs to be explicitly cast to a text datatype

EVALUATE('TO_CHAR(%1)',DIM_DATE.TODAY)
--> does not work, unless the date happens to be in the nls parameter format

3) Convert a date to string using different OBIEE functions

An alternative method is to convert the date to a string in stead of converting a testing string to a date. The difference is this:

DATE = ConvertToDate(STRING)

ConvertToString(DATE) = STRING

Convert date to number:

year(FACT_ORDERLINE.DATE) * 10000 + month(FACT_ORDERLINE.DATE) * 100 + dayofmonth(FACT_ORDERLINE.DATE) = 20110231

Note: here the lefthand and righthand side are numbers, not strings, but the idea is the same.

4) Convert a string to date using the OBIEE function "DATE"

In OBIEE, an equivalent of the Oracle function TO_DATE is the DATE function which has a fixed syntax:

YYYY-MM-DD.

For example:

DATE '2011-01-15'

Note:

This syntax cannot be configured and does not depend on locale or nls settings
The function cannot be found in the list of functions when adding a formula in Answers.
It does not use ( or ) signs, but only single-quotes

5*) Set uniform datatypes in the Physical Layer

Set the datatype property in the physical layer on the fields/attributes. Allowed date/time datatypes are:

DATE
TIMESTAMP

Try to use one, e.g. DATE, as the default to achieve a more universal way of working with dates. TIMESTAMP is required when there is an essential time part. If it is always "00:00:00", then use DATE.

6*) Locale definitions in configuration files

[bipath]\web\config\localedefinitions.xml
-- displays possible formats to choose from
-- locale "us-en" is based on locale: us
-- locale "us" contains all possible attributes to choose from, so is independend of the regional settings
-- of the Windows server
locale: us-en
[bipath]\server\config\nqsconfig.ini
-- for displaying messages
-- this maps to "us-en" in localedefinitions.xml
LOCALE = "English-usa";
-- for input and output of dates
DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss";
DATE_DISPLAY_FORMAT = "yyyy/mm/dd";
TIME_DISPLAY_FORMAT = "hh:mi:ss";
-- when on, obi does not guess what a string containing a date means
STRONG_DATETIME_TYPE_CHECKING = ON;

7*) Locale definitions in Answers webinterface

Set the locale, e.g. "English - United states" on the connection window or in your account settings: Settings -> Account

8*) Save display format

The default display format of a date is set in dbfeatures.ini (?)

It can be overriden for all date datatypes or specific attributes in Answers:

Criteria -> Column properties -> Save -> As the system-wide...

Test casting of date to string

Example: database or session NLS parameter = 'YYYYMMDD'

CAST(DIM_DATE.TODAY AS CHAR)
CAST('20110211' AS DATE)
CAST('01-FEB-11' AS DATE) --> will fail if nls parameter is set to YYYYMMDD

Notes:

Column properties -> date format (i.e.date or text) is set automatically after the result of a conversion. It does not steer, in any way, the input of functions.

No login prompt for Exchange

2011-02-15T11:46:00.017+01:00

It is possible to suppress the login prompt that appears in Outlook, when trying to connect to an Exchange server via Outlook Web Access.

This prompt can be skipped:

Close Outlook
Get the Exchange server name: Control Panel -> Mail (32 bit) -> Email accounts... -> View or change existing e-mail accounts -> Double click the "Exchange" account

The microsoft exchange server name, e.g. exmbx01.acmexchange.com, becomes the exchange server name: *.acmexchange.com

Get the webserver name: -> More settings -> Connection -> Exchange proxy settings

The connection settings URL, e.g. webmail.acmeweb.com, becomes the webserver name: *.acmeweb.com
Set proxy authentication settings: NTLM authentication

Start the Credential Manager: Control Panel -> Credential Manager
Add a windows credential, e.g. for jim@acme.com
a. internet or network address: *.acmeweb.com
b. username: jim@acme.com
c. password: pwd
Add another windows credential
a. internet or network address: *.acmexchange.com
b. username: jim@acme.com
c. password: pwd

Start Outlook and send/receive. The login popup should be gone.

Keywords: microsoft exchange, outlook 2003, outlook web access, repeating login prompt

SkyDrive photos in Flipboard

2011-01-28T14:34:00.046+01:00

Flipboard is a great app for the iPad. It displays a collections of RSS feeds as a magazine. The latest version (1.1.1) offers the ability to add a Google Reader account, which can contain a multitude of feeds. These feeds can even be categorized. Flipboard can easily display all feeds, the feeds in a category or individual feeds.

It has the ability to add a Flickr account to show the photos in that account. Unfortunately, Flickr is rather limited in its features: no more than 300MB per month upload.

There are alternatives for Flickr, like Google Picasa, Mediafire or Microsoft Live SkyDrive. Picasa has a storage limit of 1GB, which is even worse than Flickr. SkyDrive has a storage limit of 25GB, but no monthly upload quotum. The best thing about SkyDrive is that it has a RSS feed capability, so you can add it to your Google Reader account and have a magazine of photos. Mediafire has no upload or storage limitations, but no RSS feed capability, which is too bad, otherwise it would be the perfect photo-in-flipboard solution.

Because there is no dedicated Microsoft Live SkyDrive iPad app, this is a nice alternative.

Unfortunately, the RSS XML that SkyDrive returns is not interpreted by flipboard, like it is interpreted by, say, Google Reader, where the photos are displayed directly in the feed. Flipboard displays an empty article, with a link, that, when clicked, redirects to the photo. This is not useful. It turns out that the <link> element is the culprit.

Workaround

Optionally, create a SkyDrive account. Create a Windows Live / SkyDrive account and create a new folder. Set it as a "photo" folder. Upload some photos to this account. Make a note of the "cid-" code in the URL (or on the SkyDrive webpage). It is the unique identifier for that account. Also make a note of the folder name.
Create a conversion script that corrects the XML supplied by SkyDrive. Flipboard only displays the photos in the feed items, when the combination of the <link> tags of the <channel> and <item> tags form a valid URL. SkyDrive supplies the complete URL in the <link> tag of each item, so the <link> tag of the <channel> should be empty or not present at all.
Host the script online. I have created a PHP script called default.php and am hosting it on freehostingnoads.net. An account can be freely created and their PHP configuration support the functionality needed to get HTML code from another website, in this case, SkyDrive. The complete URL to the website is:
http://www.livestreem.freehostingnoads.net/default.php
Optionally, make the script dynamic. I added the posibility to supply a SkyDrive account id (CID) and folder to the script. Example for CID "cid-dba80f3be68c2301" and folder "genericweb" :
http://www.livestreem.freehostingnoads.net/default.php?cid=dba80f3be68c2301&folder=genericweb
Add script URL to the Google Reader: add the URL of the conversion script to the Google Reader account. Use the complete URL, including the PHP script name.
So:
http://www.livestreem.freehostingnoads.net/default.php?cid=dba80f3be68c2301&folder=genericweb
and not:
http://www.livestreem.freehostingnoads.net/?cid=dba80f3be68c2301&folder=genericweb
Optionally, test the feed. Google Reader updates the results from feeds at set intervals, that cannot be controlled. Pressing the "refresh" button is only useful after this interval has passed and Google has updated the feed internally. I use Chrome and, to test the feed realtime, I use the extention/add-on "Slick RSS"
Add a Google Reader section to Flipboard: login with your username and password. This only needs to be done once. The domain name part of the link tag is used by Flipboard to prefix the text of an item with a bold text containing this text and a small icon. If there is no text, just a photo, it does not do this.

Some downsides:

History explosion. Google stores the history of a feed. This can be annoying when testing a script with a lot of photos, because they get added again and again. Even worse, after deleting the subscription and adding it later, Google restores the full history! Google remembers the URL's and content of feeds that were added in the past.
Low resolution. There is no easy way to get the full resolution photo from SkyDrive. It supplies a encoded path to a preview version - most of the time 600x400 pixels - and, when clicked from within the SkyDrive website, redirects to another encoded path for the full resolution.
Photos are public. The photos need to be in a publicly available SkyDrive folder, unlike Flickr. With the Flickr photostream of Flipboard it is possible to login in with a username and password to a private folder.

SkyDrive ASPX pages

A list of .Net scripts to access SkyDrive:

self.aspx: returns SkyDrive page for the specified document/photo
embedphoto.aspx: returns a downsized version of the photo
embedalbum.aspx
feed.aspx: returns a XML document describing the RSS feed
browse.aspx

Update 20110727:

Skydrive has been redesigned. The current feed works correctly with Flipboard, so the workaround above is no longer needed!

Combine two DVD's into one

2011-01-12T21:12:00.013+01:00

When you want to combine two DVD's onto one disc, but keep the chapters, then try the following steps. I assume the video_ts folders of both DVD's are already available on your harddrive.

I most cases there is no .vob file per chapter, but each vob file is filled to its maximum (1GB) and then a new one is created. Sometimes, however, there is a vob file per chapter. In this case, you only need to follow the steps following DVDStyler:

Download and install DVDShrink (freeware)
Start DVDShrink and click "Reauthor"
In the right pane under "dvd browser", navigate to the video_ts folder of the first DVD
Drag the desired chapters to the left
In the right pane under "dvd browser", navigate to the video_ts folder of the second DVD
Drag the desired chapters to the left
Under File, click "Backup" and save to folder c:\shrink
Download and install DVDStyler (freeware)
Start DVDStyler and add all .vob files from c:\shrink to the current project. The DVD main- and chapter menu's are created automatically
Optionally: add the names of the chapters to the chapter menus
Burn the project to an iso file or directly to disc

Ofcourse, you can only do this with homemade DVD's or to DVD's you own legally and when local law permits it.

Use 32-bits Excel Source with 64-bits SSIS

2010-10-20T12:21:00.018+02:00

The following is still work in progress and more of a braindump than anything else, but I've put it here, because someone might already find some use for it

Straight out of the box, you cannot use the Excel Datasource in SSIS on a 64-bits Windows platform , because the Excel Datasource uses a 32-bits OLEDB library. Microsoft has not released a 64-bits version of this library.

0) Use MADE 2010
There is 64 bits version for Office 2010, so for Excel 2010. It is called "Microsoft Access Database Engine 2010 Redistributable" of which there is a 32- and 64-bits version. We're interested in the 64-bits version, but this package can only be installed after removing all 32 bits Office applications!

1) Trick Windows to use 32-bits ODBC data sources
Try adding the SysWow64 folder to the beginning of your path:

Path=%systemroot%\SysWow64;...old values here...

When done via the command prompt via the SET-command, it will be undone when that window is closed.

2) Run SSIS in 32-bits mode
Use the 32-bit DTEXEC.EXE to execute the package containing the Excel Datasource. It is located in "Program Files (x86)\Microsoft Sql Server\90\Dts\Binn" (or for SSIS 2008: "Program Files (x86)\Microsoft Sql Server\10\Dts\Binn")

You can schedule this using "task scheduler" or SQL Agent.

3) Compile package as 32-bits
Package configuration properties -> Debugging -> Debugging Options -> Run64BtRuntime -> set to False.

4) Even dirtier trick: convert Excel spreadsheet to CSV beforehand
This can be done in several ways, with minimal coding required:

If Excel is installed, then create a VBS script that uses CreateObject("Excel.Application") to create an Excel object. Then open the XLS file and save it as a CSV using this object.
... or a perl script using the "Spreadsheet::ParseExcel" module
... or a powershell script using "comobject Excel.Application"
Install OpenOffice and program a small macro in it's OpenOffice basic language. Then create a VBS or DOS script that opens OpenOffice and calls the macro with parameters, one being the source XLS, and one being the target CSV.
Without installing anything on the server running SSIS: write a small C# application that utilizes the Excel OLEDB data provider built into Windows to read data from the Excel sheet. Then write the results to a CSV file.
... or write a small C# application that utilizes the opensource Excel Data Reader library to read data from the Excel sheet. Then write the results to a CSV file. This library appears to be buggy however.
Freeware commandline tool: XLS2CSV by Zoom Technology. Parses only the first sheet. The column separator and text quotes are configurable throught an ini file
Freeware commandline tool: XLS2CSV by Tom Crow. Parses all sheets to separate CSV files

ODBC/DSN configuration can be found here:

32 bits ODBC connections: %windir%\syswow64\odbcad32.exe
64 bits ODBC connections: %windir%\system32\odbcad32.exe

Notes:

ADO also uses the OLEDB library.
The OLEDB library is a piece of code that gets loaded and compiled into the calling application at runtime.
If you use the MADE 2010 Redistributable package, then be aware of a small bug when connecting to Office 2010. You have to use "MICROSOFT.ACE.OLEDB.12.0" as the driver name in stead of the specified "MICROSOFT.ACE.OLEDB.14.0".
Although previous Office versions need to be de-installed before installing MADE 2010, they can be reinstalled afterwards.

Increase maximum volume of the iPad

2010-10-02T00:07:00.015+02:00

If you find that the maximum volume on the iPad is not loud enough, then you can fix that with the following adjustment:

Start Cydia

If you haven't installed OpenSSH, then search for it and install the app, restart the iPad
Install WinSCP on your Windows environment and make a connection to the iPad. The iPad IP adress can be found on the iPad in Settings -> Wi-Fi. Click on the blue arrow on your current wifi connection. By default the username is "root" and the password "alpine".

Download and install iPodRobot for Windows
Open a connection to the iPad via WinSCP
Copy the following file to your Windows environment: System/Library/PrivateFrameworks/Celestial.framework/RegionalVolumeLimits.plist

Just in case anything goes wrong: make a copy of this file on your Windows environment

Start iPodRobot and load RegionalVolumeLimits.plist
Find and replace 0.xx000000000000004 with 1, where x=79 or 84, or some other number under 100
Save the file and copy it back to the iPad using WinSCP
Reboot the iPad.

The volume will be much louder, so tune it down before starting any music or video on the iPad.

Note:

The iPad needs to be jailbroken, i.e. needs to have Cydia installed
The Cydia/Winterboard apps Volume Boost X3 and Volume Boost 4.0 have no effect on the iPad.

Alternatively, using iTunes, you can boost the volume of each individual video/song:

Start iTunes
Select all songs under iPad -> Music
Right click and select "Get info"
Tab "options", drag the slider to +100%

The song volumes will be adjusted song by song. This can take a long time!

Keywords: setting the maximum volume limit on iPod

Internet on iPad via adhoc wifi hotspot

2010-09-30T11:59:00.013+02:00

Scroll down for the solution, read on for some trial-and-error background info.

When no direct wifi internet connection is available, then it is possible to use the internet connection of wired Windows PC. The only requirement is that the PC needs to also have a wireless (wifi) network card.

It is possible to use internet connection sharing (ICS) on Windows to setup an adhoc wireless network and connect to this network from the iPad:

Control Panel -> Network and sharing center -> Setup a new network or connection -> Set up wireless adhoc (computer to computer) network -> Next
Give the network a name
Security type: WPA2-Personal
Give the network a password (security key) of at least 8 characters
Click "Turn on internet connection sharing"
Select the LAN internet connection when asked "Select the internet connection that you want to share"
iPad -> Settings -> Wi-Fi -> Select the network, type in the password

I couldn't get this to work properly. I tried all of the following:

"Turn on internet connection sharing" does not appear. Try a solution posted by sinnerman on windows7forums.com:
- Open Network and Sharing Center
- Go to Change Adapter Settings
- Find the adapter for the default internet connection (it was a PPPOE in my case)
- Right click on that -> Cancel as Default Connection
- Right click -> Properties -> Sharing tab
- Uncheck "Allow other network users to connect ..." if it is not
- Setup the ad-hoc as explained above, the Turn on Internet connection sharing should appear.
Disable Windows Firewall. If you have another one, for example Bitdefender, then there is/might be a direct option to allow ICS.
Allow other users on internet connection:

Control Panel -> Network and sharing center -> change adapter settings -> select internet connection -> Properties -> Sharing -> Check "Allow other network users to connect ..."

Set a policy that allows you to use ICS:

Start gpedit.msc.
Local Computer Policy -> Computer Configuration -> Administrative Templates -> Network -> Network Connection
Set "Prohibit use of Internet Connection Sharing on your DNS domain network." to false

Solution

Download Connectify. It is freeware
Install and allow the software to install drivers
Start Connectify and start easy setup wizard
Give the network a name, next
Give the network a password, next
Select the connection (wired/LAN) through which there is an internet connection
iPad -> Settings -> Wi-Fi -> Select the network, type in the password.

The connection from the iPad is lost when unused for a couple of minutes. When using, for example, Safari on the iPad, the connection is quickly restored via a popup that lets you select from wifi networks. Sometimes this is not even necessary and safari restores the connection automatically.

Bluetooth device not found on Dell laptop

2010-09-21T14:23:00.001+02:00

If you are sure you have a built-in bluetooth device on your Dell laptop, but you cannot find it in control panel or device manager, then try these steps:

Determine bluetooth device type using the Dell device locator
or
Go to http://support.dell.com and type in the search bar: "Find Out Which Bluetooth Adapter Is in Your Dell Computer"
or
Go to the webtool "system configuration tool" directly: system configuration tool
In all cases, use the service tag that can be found on the bottom of the laptop
Search for "bluetooth" on the page that the SCT returns, it should have code beside it, i.e. "370"
Go to http://support.dell.com and type in the search bar: "bluetooth 370"
The results page contains drivers for bluetooth for various Windows OS'es:
- Look for "Dell Wireless 370 Bluetooth Minicard, v.XXX" and search for the most recent version XXX.
- Make sure "Win 7" is in the description, if the driver is needed for Windows 7.
Download and install the driver.

CTAS disregards number precision and scale

2010-08-17T11:02:00.013+02:00

When using a "create table as select" (CTAS) to create a table based on a query, when that query also contains a window function/analytical function in the SELECT-clause, results in the precision and scale being omitted.

The precision/scale are neglected, when running the following example in Oracle 11g:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(1 AS NUMERIC(10,2)) AS FLD1,
ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
FROM DUAL;

DROP TABLE X;

CREATE TABLE X AS SELECT * FROM VSA_X;

Table X will have no precision/scale for FLD1. It is bypassed and will simply have NUMBER for its datatype. This is a bug in Oracle.

Note:

Using NUMBER in stead of NUMERIC makes no difference, the bug is still there.
Explicitly casting the ROW_NUMBER() function also makes no difference.

Solution:

Put the window function inside a derived table:

CREATE OR REPLACE FORCE VIEW VSA_X AS
SELECT
CAST(FLD1 AS NUMERIC(10,2)) AS FLD1,
RN
FROM
(
  SELECT
  1 AS FLD1,
  ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY 1) AS RN
  FROM DUAL
) x;

Keywords: CTAS omits precision, CTAS omits number precision, CTAS bypasses precision, CTAS bypasses number precision.

Subtitle Workshop blank screen with Matroska/MKV containing H.264

2010-08-11T11:55:00.151+02:00

Scroll down to solution for the workaround to get video in stead of a black screen. Read on for a little bit more background information.

There are five components to video in Windows: container (like avi, mkv), video format (divx, h.264), splitter, decoder, and video player.

Nice post to get started:
http://www.hack7mc.com/2009/02/mkvs-for-minimalists-on-windows-7.html

But the scripts could be more robust. E.g. change the fixed system root "c:\windows" to %systemroot%

SPLITTERS

Haali Media Splitter: http://haali.su/mkv
Note: since december 2009, this contains also the x64 version.

Gabest: hard to get, but is part in some packs.
x32: http://www.hack7mc.com/wp-content/plugins/download-monitor/download.php?id=12
x64: http://www.hack7mc.com/wp-content/plugins/download-monitor/download.php?id=11

Advice: Haali splitter.

DECODERS

ffdshow (the original, but outdated): http://sourceforge.net/projects/ffdshow/files
ffdshow tryouts: http://sourceforge.net/projects/ffdshow-tryout/files

Get a 64 bits or 32 bit ("generic") in the folder "official releases" or get a more recent nightly build from the folder "SVN builds by clsid".

Advice: a recent 32 bit nightly build.

SPLITTER/DECODER packs

K-Lite Codec Pack: http://www.digital-digest.com/software/K-Lite_Codec_Pack_64-Bit_history.html

Shark007: Install Win7Codecs first (http://www.majorgeeks.com/Win7codecs_d5959.html), then x64Components (http://www.majorgeeks.com/download.php?det=5535)

Shark's Win7Codecs comes with a tool to select and configure the various splitters and decoders for the various media types (divx/xvid, mpeg2, h264/mpeg4) and containers (avi, mkv).

Install and use the "settings32.exe" application to configure, since most video players are currently still 32 bit (winamp, zoomplayer)

Advice: Shark007 packs

VIDEO PLAYERS

Zoomplayer 5.02 Standard (latest free version), Windows Media Player (build-in, WMP Classic/Guliverkli2 or WMP Classic Home Cinema) and Winamp all work with the system splitters and decoders. VLC Player comes with its own splitters/decoders and does not use the ones installed on the system.

Subtitle Workshop v2.51 does only work with "real" avi containers. The "rename trick", where the mkv extension is changed to avi, will work with most of the video players, but unfortunately not with Subtitle Workshop.

The more recent betas of v4 do work with mkv/h.264 files, but those are not very stable. The splitter has to be Haali, Gabest's does not work - "cannot render media!". It does not matter if the "rename trick" is used. This also applies to Windows Media Player. Gabest also doesn't work with Subtitle Workshop v2.51.

Winamp doesn't really like mkv files; it almost always halts on startup.

Zoomplayer does not work well with video format "Xvid dev 25-Mar-03". You have to use Haali splitter for avi, because Gabest and Microsoft's cause a stack overflow.

Zoomplayer doesn't work with mkv/H.264 when the ffdshow codec is used.

CHECK TOOLS

Tools to check video containers, e.g. get video format or the the processing workflow (splitting, decoding):

GSpot: http://www.headbands.com/gspot

MediaInfo: http://sourceforge.net/projects/mediainfo/files

GraphStudio: http://blog.monogram.sk/janos/2008/12/13/monogram-graphstudio-0310
GraphEdit: http://www.digital-digest.com/software/download.php?sid=82&ssid=0&did=1

SOLUTION

To get the video preview to work in Subtitle Workshop 2.51 with mkv files, that contain the h.264 video format:

Install both Shark007 packs mentioned above. Check the box to install ffdshow.
To get the latest ffdshow version working with Shark007 (this step is not necessary):

In \program files (x86)\win7codecs\filters: regsvr32 /u "ffdshow.ax". Uninstall this older version
Download most recent 32 bit nightly build mentioned above and install with default settings
Copy "ffdshow.ax" from the nightly build install folder to the \win7codecs\filters folder

Start \program files (x86)\win7codecs\tools\settings32.exe
Click "reset all" to get the recommended configuration.

To be sure, check the following settings:
H264: use ffdshow's codec/ffdshow with multithreading
MKV: use haali splitter

Set H264 codec in directshow players to "use ffdshow's codec"
Finally, install Vobsub 2.23

Notes:

The final step is very important. You have to install Vobsub (or "vsfilter") as the final step, even if it is already installed. Just install it again. (VobSub is a directshow filter that overlays subtitles)
Test mkv and avi video formats in Winamp and Zoomplayer. If they fail to start, then uninstall Vobsub 2.23 again. Use the subtitle option from ffdshow to have subtitles in those players. WMPC and VLC have their own subtitle overlay filters.
The version has to be 2.23, more recent versions will not work. E.g. 2.39 does not work.
Do not rename the container from .mkv to .avi. Subtitle Workshop 2.51 will load the .mkv extension without problems.

TNS service names ODBC dropdown box garbled, messed up, gibberish

2010-08-10T14:07:00.011+02:00

Most likely cause is that there are two or more different Oracle clients installed, with two or more home folders and tnsnames.ora files.

For example, an Oracle 10 client is installed or Oracle XE is installed. Both contain a home folder with tnsnames.ora. Later, the Oracle 11g instant client + ODBC compontents is added. Now there are two home folders.

In this case, the TNS_ADMIN environment variable needs to exist:

Create TNS_ADMIN variable via control panel -> system -> advanced -> environment variables -> system variables
Set is to one of the admin folders, eg.:
c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN
Note: make sure is contains the tnsnames.ora, sqlnet.ora and listener.ora files.
Create a ODBC connection using the Oracle driver and check if the "tns service name" dropdown box contains readable entries
Just to be sure, replace the body of all other tnsnames.ora files with:
IFILE=c:\oracle\ic_x32_11.2.0.1.0_basiclite\NETWORK\ADMIN

CDBurnerXP: drive not recognized

2010-08-05T20:36:00.011+02:00

CDBurnerXP is a freeware tool for burning CD's and DVD's, including blueray. However, it sometimes does not recognize the drive to burn with.

First, check the FAQ on the CDBurnerXP website:
http://cdburnerxp.se/help/appendices/troubleshooting

In my specific case, I then did the following:

Start CDBurnerXP in "audio mode"; the drive was recognized. Only in "data mode" it is not.
Check the chipset. Download the freeware tool "system info": http://www.gtopala.com/siw-download.html.
Extract and start it - it does not have to be installed; it is a standalone executable. Check hardware -> motherboard -> southbridge. Note the chipset.
Download "intel matrix storage manager" for your chipset: IMSM.
Install IMSM and restart.

Now, CDBurnerXP should work in "data mode".

Note: make sure the drive is on the "compatible drives" list: http://cdburnerxp.se/en/testeddrives

The nearby-future of storage

2010-08-04T12:45:00.019+02:00

The ioDrive Duo by FusionIO out-performs even multiple SSD's placed in parallel. The 320GB version of the ioDrive has a read/write speed of ~1.5GB/s:

http://www.fusionio.com/products/iodriveduo

Difference with traditional SSD:

The flash memory is integrated in the PCI card.
The flash memory has been optimized using a proprietary technology.

Downside:

Cannot be used as a boot drive.
Requires a 64-bit operating system.
The price is about 15,000 euro for 320GB version (ssdeurope.com). Ofcourse, this has to be compared against the cost of buying and operating a SAN.

SQL Server statistics

2010-08-03T21:15:00.016+02:00

Statistics are information about the distribution of data in a column, maintained internally by the database engine.

Rules and behaviour with regard to statistics, when automatic creation/updating is turned on:

Statistics are automatically created when creating an index, but only for the first column of the (composite) index.
Statistics can be created manually on any column or combination of columns.
Statistics are automatically created/updated when the query optimizer finds need for them.
Statistics are not automatically created/updated when the server is too busy, unless the "update statistics asynchronously" option is turned on, because then the are created in parallel.
Asynchronously created/updated statistics have effect the next time the (causing) query is run.

Links:
http://www.sql-server-performance.com/tips/update_statistics_p1.aspx
http://blogs.technet.com/b/rob/archive/2008/05/16/sql-server-statistics.aspx
http://technet.microsoft.com/nl-nl/library/cc966419(en-us).aspx

Set cscript as default script engine

2010-08-03T12:12:00.003+02:00

To set cscript as the default scripting engine on the system:

cscript //h:cscript //s

This way, the "wscript.echo" method always spools to the command line in stead of to message boxes. And without the need to start each script with the "cscript.exe" application.

Derived table vs subquery

2010-07-30T09:10:00.007+02:00

There are several ways to combine datasets:

Subquery: SELECT clause, correlation
Join (cross/left/right/inner/full): FROM clause, no correlation
Cross apply: FROM clause, correlation

Datasets retrieved in the FROM clause are called "derived tables".

The "cross apply" combines the best features of the subquery and join, but is performance-wise not always the best choice.

SQL Developer thick/OCI driver

2010-07-28T12:59:00.029+02:00

The following error messages may appear when using SQL Developer to select data from a SQL Server table via a (heterogeneous or DG4ODBC) database link on an Oracle 11g DB:

Bigger type length than Maximum
OALL8 is in an inconsistent state

Solution: switch to the thick driver to work around this.

In SQL Developer: tools -> preferences -> database -> advanced -> use oci/thick driver = checked.

If this does not work or when getting the error messages:

no ocijdbc11 in java.library.path
vendor code 0

then:

- Download Oracle Instant Client 11.1.0.7.0 basic lite (easily found with Google, register with Oracle to download)

Note: version 11.2 does not work with SQL Developer!

Note: take the 64-bits version on a 64-bits OS, like Windows7 x64.

- Extract files to c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Add to environment variable PATH: c:\ora\instantclient_x64_11.1.0.7.0_basiclite
- Create a batch file called sd.bat in the rootfolder of the SQL Developer installation, with the following body:

set ORACLE_HOME="c:\ora\instantclient_x64_11.1.0.7.0_basiclite"
start sqldeveloper.exe

- Double-click sd.bat to start SQL Developer
- Add a connection with Connection type = basic

To make a connection with Connection type = TNS:

- Create folder c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Put these files in the admin folder:

sqlnet.ora
tnsnames.ora

- In SQL Developer: tools -> preferences -> database -> advanced -> tnsnames folder: c:\ora\instantclient_x64_11.1.0.7.0_basiclite\network\admin
- Add a connection with Connection type = TNS

Note the following:

The solution above even (or especially) works when another Oracle client is already installed. For example, if the OracleXE DB (which, at the time of writing, is 10g) is installed locally, then the Oracle Client 10 is also installed. But this client might not work when trying to connect to a remote Oracle 11g DB
There is no need to set the environment variables ORACLE_HOME or TNS_ADMIN variable on the system. It does not matter what their values are
The order of folders in the variable PATH does not matter
Most likely Help -> About -> Properties -> oracle.home does not point to the correct Oracle home folder. So not to c:\ora\instantclient_x64_11.1.0.7.0_basiclite. It does not matter what the value is
Unfortunately, the ORACLE_HOME trick does not work with the PATH variable; it is no use adding that to the batch file
The error "Bigger type length than Maximum" is related to a bug in the implementation of the thin part of the JDBC driver. Not to heterogeneous services, Oracle DB, SQL Server, SQL Developer or the thick part of the JDBC driver. (Note: the same driver, e.g. ojdbc6.jar, contains the thin and the thick classes)

Oracle JDBC connections

2010-07-28T10:59:00.025+02:00

The following JDBC drivers exist for Oracle:

classes111.jar: Java 1.1
classes12.jar: 1.2 and 1.3
ojdbc14.jar: 1.4 and 1.5 (no JDBC3 and 4 features)
ojdbc5.jar: 1.5
ojdbc6.jar: 1.6

Java 1.5 = Java JRE/SE 5 and Java 1.6 = Java JRE/SE 6. Additional jar files are required for some features.

All the classes to support basic functionality for the Thin and OCI drivers are inside the same jar. In short, for simplification, the same file contains both the thin and the thick (=OCI) classes.

Joe Weinstein on forums.oracle.com:

The jar is all of the thin driver and the java portion of the OCI driver. In order to
run in OCI mode, you would use this same jar, giving it the OCI URL instead of the thin
URL, and you would have to have the whole Oracle client installed on your machine,
and your OS environment set up to point to the OCI DLLs as well as the standard C
libraries. Note that the version of the OCI libraries that works with one driver jar
may not work with another driver jar, so if you go the OCI route, you may have to
install a whole new OCI client just to get a particular driver fix.

Thick URL: jdbc:oracle:oci8:@<database_name>
The database_name is the Net Service name of a mapping in tnsnames.ora.

Thin URL: jdbc:oracle:thin:@<server>:1521:<database_name>
The database_name is the database name part of the database service on the (remote) server. E.g. Windows service "OracleServiceORCL", then the database_name is "ORCL".

Oracle released different versions for each driver, eg. 11.1.0.6.0 or 11.1.0.7.0.

To get the different versions, download the Oracle Instant Client of choice from the Oracle website and extract the ojdbc.jar files to one folder. Rename them using the version. This makes testing or switching easy:

ojdbc6_11gR1_11.1.0.6.0.jar
ojdbc6_11gR1_11.1.0.7.0.jar
ojdbc6_11gR1_11.2.0.1.0.jar
etc

Databases

2010-07-27T22:13:00.013+02:00

Row-oriented:
Oracle DB
SQL Server
SQL Server CE
MySql: InnoDB
Sybase Advantage Database Server (ADS)
MS Access

Column-oriented:
Extreme-DB
InfiniDB Enterprise Edition
Infobright
MonetDB
ParAccel Analytic Database
Sybase IQ
Sybase Adaptive Server Enterprise (RDBMS & column-oriented)
Vectorwise
Vertica

Appliance:
Aster Data (MPP, MapReduce)
Greenplum (MPP, MapReduce)
Netezza (MPP)
Teradata (MPP)

In-process:
SQLite

No login prompt for website

2010-07-23T11:00:00.011+02:00

Suppose you want to login with Internet Explorer to an intranet website, that uses Windows Authentication as the authentication mechanism. When you do this, a prompt is displayed, that requires you to enter the account's username and password.

This prompt can be skipped:

Note the webserver name in the title bar of the prompt, e.g. wbsvr01.acme.lan
Start the Credential Manager: Control Panel -> Credential Manager
Add a windows credential:
a. internet or network address: wbsvr01.acme.lan
b. username: domain\usr
c. password: pwd

Note: if the website is on the internet, the webserver name has to be added to the trusted sites of the intranet zone:

Internet Explorer -> Tools -> Internet Options -> Security -> Select "Local Intranet" -> Sites -> Advanced
Add this website to the zone: http://wbsvr01.acme.lan

Run SSMS using different Windows account

2010-07-23T09:20:00.017+02:00

You can log in to a SQL Server database using SQL Server Management Studio (SSMS) with a Windows Authentication login. However, the domain account to do this with cannot be entered. It is greyed out and always the account of the user that is currently logged on.

There are several ways to bypass this and make a connection:

1) Run SSMS under different privileges:

- Create a shortcut to SSMS with the following command:

runas /netonly /user:domain\usr "g:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe"

- Double-click the shortcut and enter password for the runas account

Note: SSMS can be located somewhere else, depending on the choice made for the location when installing or the operating system. When using Windows 7 x64, then the tool is by default located as specified above.

Note: The greyed-out login screen in SSMS still displays the logged on user, not the runas account. This might be confusing.

1a) Run SSMS under different privileges, without needing to enter a password:

- Download "runasspc" (can be easily found with a search engine)
- Extract "runasspc.exe" from the zip file and copy to the windows folder
- Create a shortcut to SSMS with the following command:

runassprc "g:\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SqlWb.exe" /domain:domain /user:usr /password:pwd

2) Create a network connection to the remote server

- Open a command windows (Win+R, cmd.exe)
- Delete all mappings: net use * /delete /y
- Create mapping to SQL Server server: net use \\servername /user:domain\usr
- Start SQL Server Configuration Manager via Programs (or locate SQLServerManager.msc directly)
- Open SQL Native Client Configuration
- Enable Named Pipes protocol and move to top in the order
- Add a new alias. Use:
a. "Named Pipes" as network library
b. Server name: fill in remote server name. The pipe field will display: \\[servername]\pipe\sql\query"
- Start SSMS
a. Server name: fill in remote server name
b. Authentication: Windows Authentication
c. Connection Properties -> Network Protocol, set to Named Pipes

Note: SSMS automatically uses the account name from the connection to the remote server.

3) Set credentials for a remote server:

- Control Panel -> Credential Manager
- Add the domain\account for the remote server

4) Get terminal services access

Get the "allow logon to terminal services" right for the remote server for the domain account. Then make a terminal services connection to the server with that account and start SSMS within that session.

Links:
http://www.olegsych.com/2009/05/crossing-domain-boundaries-windows-authentication/

Change column position

2010-07-21T10:59:00.003+02:00

In Oracle, it is not straightforward to change the position of a column. Let alone one that contains data.

Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Create a new table called "newtable" with the desired column order, based on "oldcolumn"

Note: generate DDL script for "oldtable" and only change the position of the columns. Do not apply constraints, defaults, primary keys, foreign keys, etc. They are named and the names cannot exists twice in a schema

- Insert all data from "oldtable" into "newtable" using a DML INSERT-statement
- Drop "oldtable"
- Rename "newtable" as "oldtable"
- Apply constraints, defaults, etc to "oldtable"

Note: if columns are deleted or newly added to "newtable", then this is not straightforward and each constraint, default, etc should be inspected before applying

Or you can use the "dbms_redefinition" package (bhaski on www.geekinterview.com):

But you should have these previleges -->
# execute access on the dbms_redefinition package
# create any table # alter any table
# drop any table
# lock any table
# select any table

create table tab1(col1 char,col3 number);

alter table tab1 add(col2 char);

create table tab2 as select col1,col2,col3 from tab1; exec

dbms_redefinition.can_redef_table('schema_name','tab1');

begin
dbms_redefinition.start_redef_table('schema_name','tab1','tab2','col1,col2,col3');
dbms_redefinition.finish_redef_table( 'schema_name', 'tab1', 'tab2' );
end;

select * from tab1;
col1 col2 col3

Change column data type

2010-07-21T10:10:00.001+02:00

In Oracle, it is not straightforward to change the data type of a column that contains data. Without resolving to manually changing metadata tables, but using SQL, the following trick can be applied:

- Add a new column to the table called "new column" with the new data type
- Copy the values of "old column" to "new column" using a DML UPDATE statement
- Remove the "old column" from the table using a DDL ALTER statement

Most recognizable samples

2010-07-13T23:14:00.027+02:00

Breakbeats
Al Green - I’m Glad You’re Mine (0:00)
[Eric B. & Rakim - Mahogany, The Notorious B.I.G. - I Got a Story to Tell, RZA - My Lovin' Is Digi]

Bad Bascomb – Black Grass (1:41)
[Doug E. Fresh - Everybody Got 2 Get Some, Monie Love - Monie In The Middle]

Billy Squier – Big Beat (0:00)
[Ice Cube – Jackin’ For Beats, Big Daddy Kane – Get Down, A Tribe Called Quest – We Can Get Down, EPMD – Get Wit This]

Booker T. & The M.G.'s - Born Under A Bad Sign
Booker T. Jones & William Bell - Born Under A Bad Sign

Bobby Byrd - Hot Pants (0:00)
[Sir Mix-a-Lot - One Time's Got No Case, Das EFX - Straight Out the Sewer, Ini Kamoze - Here Comes the Hotstepper, Run-DMC - What's It All About]

Chuck Brown and The Soul Searchers - Ashley’s Roach Clip (3:31)
[Eric B. & Rakim – Paid In Full, PM Dawn – Set Adrift On Memory Bliss, Nice & Smooth - Down the Line]

Dexter Wansel – Theme From The Planets (0:00)
[Eric B. & Rakim - I Ain't No Joke, Last Night by Kid 'N Play, Gutfest '89 by Digital Underground]

Donny Hathaway – Magnificent Sanctuary Band (0:00)
[Beastie Boys - Johnny Ryall]

Edwin Birdsong - Rapper Dapper Snapper (0:00)
[De La Soul - Me Myself and I, Gang Starr - Skills]

Fred Wesley and The J.B.'s - More Peas (7:22)
[Organized Konfusion - Fudge Pudge, Cypress Hill - The Phunky Feel One]

Funkadelic – Good Old Music (0:00)
[Young Black Male by 2Pac]

Kool & The Gang – Jungle Jazz (0:13)

Kool & The Gang - N.T.
[Jump by Kris Kross, Fakin' the Funk by Main Source]

Funk Inc. – Kool Is Back (1:48)
[Jeru the Damaja - Come Clean]

Isley Brothers – Footsteps In The Dark

James Brown - Blues and Pants (0:00)
[Ice-T - New Jack Hustler, Cypress Hill - The Phunky Feel One]

James Brown - Funky Drummer (5:35)

James Brown – Funky President (0:00)
Eric B. Is President by Eric B. & Rakim, Hey Ladies by Beastie Boys]

Jeff Beck – Come Dancing (0:00)
[Chino XL - It's All Bad, Gang Starr - New York Strait Talk]

Jimi Hendrix - Flashing (0:00)

Joe Tex – Papa Was Too (0:00)
[EPMD - Headbanger, Wu-Tang Clan - Wu-Tang Clan Ain't Nuthing Ta F*** Wit]

Lafayette Afro Rock Band - Hihache (0:00)

Led Zeppelin - When The Levee Breaks (0:00)
[Ice-T - Midnight, Eminem - Kim]

Little Feat - Fool Yourself (0:00)
[A Tribe Called Quest - Bonita Applebum, Organized Konfusion - Soundman]

Little Richard – The Rill Thing (0:00)

Lowell Fulsom - Tramp (0:00)
[House Of Pain - Jump Around, Cypress Hill - How I Could Just Kill A Man]

Lyn Collins - Think (About It) (1:23)

Manzel - Midnight Theme (0:00)
[Wreckx-N-Effect - Rump Shaker, Cypress Hill - How I Could Just Kill A Man]

Mountain - Long Red (Live) (0:00)

Melvin Bliss - Synthetic Substitution (0:00)
[Naughty By Nature - OPP, Ice-T - Original Gangster, The Pharcyde - Ya Mama]

Orange Crush – Action (0:25)
[Do You Want It... Do You Like It... by Puff Daddy, We Major by Kanye West]

Power of Zeus – Sorcerer of Isis (0:10)
[The Wiseguys - Face the Flames, Eminem - Amityville]

Q65 – Get Out Of My Life Woman
[Jeru the Damaja - Revenge of the Prophet (Part 5), The Wiseguys - We Be the Crew]

Roy Ayers – The Boogie Back (0:00)
[N.W.A - F*** Tha Police]

Silhouettes – Fonky First (0:00)

Skull Snaps – It’s A New Day (0:00)
[The Pharcyde - Passin' Me By, Digable Planets - For Corners]

Sly & the Family Stone - Sing a Simple Song (2:12)

The Headhunters – God Make Me Funky (0:09)
[Eric B. & Rakim - Beats for the Listeners]

The Honey Drippers - Impeach the President (0:00)

The Incredible Bongo Band - Apache (0:00)
[Sugarhill Gang - Apache, LL Cool J - You Can't Dance, Grandmaster Flash - Freelance]

The Meters – Here Comes The Meterman
[Big Daddy Kane - Long Live The Kane, Run-DMC - How'd Ya Do It Dee?]

The Meters - Look-Ka Py Py (0:50)
[Cypress Hill - The Phunky Feel One]

The Winstons - Amen, Brother (1:27)
[N.W.A. - Straight Out Of Compton, 3rd Bass - Portrait Of The Artist As A Hood, Brand Nubian - The Gods, Snow - Informer, Eric B. & Rakim - Casualties of War]

Young Holt Unlimited – Wah Wah Man

Zapp and Roger – More Bounce To The Ounce (0:00)
[EPMD – You Gots To Chill, Ice Cube – Jackin’ For Beats, Public Enemy – One Million Bottlebags, Ice Cube – Look Who’s Burnin’]

The Turtles – I’m Chief Kamanawanalea (0:00)
[Serious by Steady B feat. KRS-One, Say No Go by De La Soul, AmeriKKKa's Most Wanted by Ice Cube]

Grady Tate - Be Black Baby (0:00)
[Set It Off by Big Daddy Kane, Fakin' the Funk by Main Source, Stand Together by Beastie Boys]

Sounds
The J.B.'s - The Grunt (0:00)
Herbie Hancock - Wiggle Waggle (0:00)

Identifying keys

2010-07-12T21:31:00.013+02:00

A key that is made up of one field is a simple key. All other keys are combined keys. There are two types of combined keys, compound and composite:

Simple key: key that consists of one field

Combined key: key that consists of more than one field

Compound key: combined key in which each field is itself a simple key (of another entity)

Composite key: combined key, that is not a compound key

A new term I have made up myself:

Concatenated key: a simple key, that is the concatenation of the fields in a combined key

Truncate tables that have a foreign key

2010-06-09T16:04:00.004+02:00

A script to truncate tables that have foreign keys referencing them. It also keeps one record in the table, the one with ID=0. In this case, each table has this field as a primary key.

Finally, some tables are not to be truncated. The readible names of these tables are in a metadata table called TDWM_TABLES. The readible name is in the field VIEWNAME.

The process is:

- disable constraints on the selected tables, i.e. with names like 'TDWH_%'
- create a new intermediate table via CTAS that contains the records to keep, i.e. with ID=0
- truncate the table
- copy records back from the intermediate table
- drop intermediate table
- enable constraints on the selected tables, i.e. with names like 'TDWH_%'. Note: enable PK constraints before enabling the FK constraints!

Here is the script:

/* SCRIPT */

SET SERVEROUTPUT ON SIZE UNLIMITED

BEGIN

-- Disable constraints
DBMS_OUTPUT.PUT_LINE ('-> Disabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' DISABLE CONSTRAINT ' || constraint_name || ' CASCADE' as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%')
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;

-- Truncate tables
DBMS_OUTPUT.PUT_LINE ('-> Truncating tables');
FOR reg IN (
SELECT
'CREATE TABLE Z_' || TABLE_NAME || ' AS SELECT * FROM ' || TABLE_NAME || ' WHERE ID=0' AS ST1,
'TRUNCATE TABLE ' || TABLE_NAME AS ST2,
'INSERT INTO ' || TABLE_NAME || ' SELECT * FROM Z_' || TABLE_NAME AS ST3,
'DROP TABLE Z_' || TABLE_NAME AS ST4
FROM USER_TABLES t
INNER JOIN TDWM_TABLES b ON UPPER(t.TABLE_NAME) = UPPER(b.TABLENAME)
WHERE t.TABLE_NAME LIKE 'TDWH_%' AND
UPPER(b.VIEWNAME) NOT IN ('TDAYOFWEEK','TMONTHOFYEAR','TQUARTEROFYEAR')
)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
DBMS_OUTPUT.PUT_LINE (reg.ST2); EXECUTE IMMEDIATE reg.ST2;
DBMS_OUTPUT.PUT_LINE (reg.ST3); EXECUTE IMMEDIATE reg.ST3;
DBMS_OUTPUT.PUT_LINE (reg.ST4); EXECUTE IMMEDIATE reg.ST4;
END LOOP;

-- Enable constraints
DBMS_OUTPUT.PUT_LINE ('-> Enabling constraints');
FOR reg IN (SELECT 'ALTER TABLE ' || table_name || ' ENABLE CONSTRAINT ' || constraint_name as ST1 FROM user_constraints WHERE table_name LIKE 'TDWH_%' ORDER BY CONSTRAINT_NAME DESC)
LOOP
DBMS_OUTPUT.PUT_LINE (reg.ST1); EXECUTE IMMEDIATE reg.ST1;
END LOOP;
END;

Unlimited login attempts on Oracle 11g

2010-06-02T12:43:00.002+02:00

In Oracle 10g, by default, each user has unlimited login attempts before being blocked. In Oracle 11g this behaviour is changed to 10 attempts.

When a user/schema is created, it gets assigned to the default profile. The easiest way for unlimited login attempts for all users is to give the default profile the rights:

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Reconfigure OracleXE built-in webserver's port

2010-06-01T16:27:00.005+02:00

OracleXE comes with a built-in webserver, running on port 8080. Since this is a wellknown port, chances are that it is already in use on your machine, and therefore conflicting with an already running webserver.

Check this blog to reconfigure it:

http://daust.blogspot.com/2006/01/xe-changing-default-http-port.html

Cannot load package & required privilege is not held by the client

2010-01-29T10:36:00.056+01:00

How to start a filesystem dtsx package without config file from sql agent:

- ssis: make note of the package owner, should be windows (domain) account
- ssis: package: encrypt with userkey
- os: sql agent service should run under LocalSystem account
- os: add package owner to local groups: SQLServer2005DTSUser$SERVER, SQLServer2005SQLAgent$SERVER
- ssms: create credential for package owner
- ssms: create proxy using created credential, add all principals, add all subsystems
- ssms: use the created proxy as "run as" user for the job step that starts the package

There is no easy option to specify the password for a package from sql agent, hence the "encrypt with userkey".

Background information
Principals have access to a proxy. A proxy is a mapping to a credential. A credential is a mapping to a Windows account.

1. SSMS / Security / Credential, create a credential. Set identity to an existing account that has the appropriate rights to run the job.
2. SQL Agent / Proxies / SSIS Package Execution, create a new proxy
2a. Proxy / General / Credential name: select the credential from step 1
2b. Proxy / General / Subsystems: select all
2c. Proxy / Principals / MSDB Role, add principals: sqlagentuserrole, sqlagentreaderrole, sqlagentoperatorrole. This are the principals that can access the proxy.
2d. The account must have "login on as a batch job" privileges (check with secpol.msc)
4. Set SQL Server Agent service account to LocalSystem
5. If needed, run a job step under another account. Typically: a SQL Server account based on a Windows account: SQL Server Agent / Jobs / job / Steps / Edit / Advanced / Run as user,
- select proxy
- type SSIS package
- package source File system

If the "A required privilege is not held by the client" error occurs (from Microsoft website):

1. Set the SQL Server Agent service account in SQL Server Configuration Manager to the LocalSystem account.
2. Stop and then start the SQL Server Agent service.
3. Reset the SQL Server Agent service account in SQL Server Configuration Manager back to the original account.
4.Stop and then start the SQL Server Agent service.

From Marc at Stackoverflow:

So it boils down to really just two accounts:

one account is needed to run the SQL Server Agent Windows service - this is a Windows account on your machine / server which needs to have enough permissions to run the service, start and stop it - either use LocalSystem, Network Service, or whatever other Windows account you have to run services with

The other account would be the account to run your SQL Server Agent steps under - that's typically a SQL Server account (which could be based on a Windows account), and it needs enough privileges inside SQL Server to do its job, e.g. it needs access to the database objects and all. I would strive to have just one account for each app that runs the SQL Server jobs - makes life a whole lot easier!

From Influent at SQLTeam:

1. set ProtectionLevel to DontSaveSensitive in package properties in Visual Studio (using SSPI anyway so not sure it mattered)
2. created configuration file (had to alter path to config file in dtsx file since it's different on servers than in dev environment)
3. instead of importing the package into MSDB, set the job step Package Source to "File system" and pointed to dtsx file on local drive of server
4. set the Configurations tab of the job step to point to the dtsConfig file created in step 2
5. made sure the AD user kicking off the job was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER (I eventually removed Administrator permissions because I only needed it to log to Windows Event Log)
6. made the associated SQL user to the user in step 5 part of the SQLAgentUserRole in the msdb database
7. made sure the local user assigned to the SQL Agent service was part of local server groups: Administrators, SQLServer2005DTSUser$SERVER, and SQLServer2005SQLAgent$SERVER

From xprog.blogspot.com:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314). The step failed.
The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:
1. The proxy account have the "Act as part of the operating system" privilege.
2. The SQL Server Account is in the group
SQLServer2005MSSQLUser$$
3. The Proxy account is in the group
SQLServer2005DTSLUser$$

SQL Server Agent Account must have 'Adjust memory quotas for a process' (or 'Increase quotas' in pre-Windows 2003) privilege on SQL Server.

From FriggleFragle at MSDN:

an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.

External tables

2010-01-21T13:28:00.071+01:00

Data can be imported into and exported from Oracle 10g in different ways. E.g. by using the SQL*Loader tool for text files (sqlldr) or the Data Pump (impdp/expdp) tools for the propriety Oracle RAW format. For previous Oracle versions, the tools imp and are also available. Those import/export to external locations on the client machine, not the server machine.

Another way to get data in and out of Oracle is to define an external table. To do this you have to create a table in Oracle in the regular way using a CREATE TABLE statement, but with adding an additional ORGANIZATION EXTERNAL clause.

Within this clause the properties of the external (server side!) location can be specified.

EXAMPLE

CREATE TABLE TEST
(
  "CODE" VARCHAR2(17 BYTE)
)
ORGANIZATION EXTERNAL
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY DATA_PUMP_DIR
  ACCESS PARAMETERS (
   RECORDS DELIMITED BY NEWLINE
   CHARACTERSET WE8MSWIN1252
   FIELDS TERMINATED BY 0X'09'
   MISSING FIELD VALUES ARE NULL
   REJECT ROWS WITH ALL NULL FIELDS
  )
  LOCATION ( DATA_PUMP_DIR: 'EXTFILE.TXT' )
)

FIELD LIST

The list of fields following the create table keywords cannot be inferred dynamically, but has to be specified explicitly. So it is not possible to "hook up" a text file and retrieving the list of fields automatically.

Trick: import the data first into a table using SQL Developer, TOAD, etc, and create the DDL script from it. Finally, copy and paste the field list from this script.

It is also possible to specify the "external" list of fields inside the organization external clause. This is, for example, similar to how it is done using Oracle Warehouse Builder (OWB).

TYPE

One of these properties is "TYPE", which specifies the access driver that Oracle uses. There are two values for this property:

ORACLE_LOADER: only read access, from all kinds of text files (but not, for example, Excel .xls)
ORACLE_DATAPUMP: read and write access, a propriety RAW format

The access driver is a wrapper Oracle uses to call the previously mentioned SQL*Loader or Data Pump functionality.

An error occurs when an insert statement is performed on a table is created with access driver ORACLE_LOADER:

SQL Error: ORA-30657: operation not supported on external organized table30657.0000 - "operation not supported on external organized table"
*Cause: User attempted on operation on an external table which is
not supported.
*Action: Don't do that!

The ORACLE_DATAPUMP type has to be used to export data from Oracle using an external table. Unfortunately, this data cannot be humanly read or imported into any other tool then an Oracle database.

DEFAULT DIRECTORY / LOCATION

A directory object has to be available in Oracle. If it is not already present, it has to be created. For example:

CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS 'C:\'

Now the tag DATA_PUMP_DIR can be used in the organization external clause. The DEFAULT DICTIONARY property is mandatory and has to be used when setting the LOCATION.

A path specification is not allowed between the single-quotes.

This is not valid:

LOCATION ( 'C:\EXTFILE.TXT' )

ACCESS PARAMETERS

The options are rather straightforward, except perhaps "FIELDS TERMINATED BY". To set the field delimiter a character can be specified between single-quotes. But it is also possible to specify special characters like tab or linefeed.

To specify a tab:

FIELDS TERMINATED BY 0X'09'

The 0X instructs Oracle that the value between the single-quotes should be interpreted as an ASCII value. In the example, ASCII character 9 is a tab.

OPTIONALLY ENCLOSED BY '"'

It is also possible to specify that some values have double-quotes around them. This however does not work in the following case:

Column 1 value - tab - tab - Column 3 value

Here a record has a value for columns 1 and 3, but not for column 2. It should be read as NULL, but is actually skipped. This results in the value of column 3 being wrongly inserted in column 2.

CHARACTER SET

Explicitly setting the character set of the external location prevents problems with interpreting the data and can improve performance.

From Odi's blog:

If you do not specify the CHARACTERSET parameter, the database character set is used to interpret the input file. As the input file is in WE8MSWIN1252, the ORACLE_LOADER driver gets confused trying to interpret single-byte WE8MSWIN1252 codes as multibyte AL32UTF8 codes.

The character set of the input file depends on the way it was created. Even on US Windows, you can create text files in different encodings. Notepad allows you to save the file in ANSI code page (=WE8MSWIN1252 on US Windows), Unicode (=AL16UTF16LE), Unicode big endian (=AL16UTF16), and UTF-8 (=AL32UTF8). The Command Prompt edit.exe editor saves the files in the OEM code page (=US8PC437 on US Windows).

-- Sergiusz

LOGGING

All errors are logged to a bad file - containing the violating records - and a log file - containing messages about the error that occured.

To disable logging, (some of) the following keywords can be added to the clause:

NODISCARDFILE
NOBADFILE
NOLOGFILE

Oracle specification of the ORACLE_LOADER access driver:

http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10825/et_params.htm

SQLite enforces foreign key constraints

2009-10-15T10:54:00.006+02:00

As of version 3.6.19, SQLite finally supports foreign key constraints that can be enforced.

http://sqlite.org/releaselog/3_6_19.html

A pragma statement has to be execute on the database to enable the enforcement:

PRAGMA foreign_keys = true;

Measuring disk I/O speed

2009-09-30T09:09:00.015+02:00

Disk thruput tester is a handy tool to test the sequential read and write performance of storage media, like harddisks and SSD. It supports configurating custom block sizes:

http://www.objectso.nl/

Using this tool, testing the read/write performance of 8KB, 16KB, 32KB, etc, sized blocks is a breeze. The size and location of the test file is also configurable.

Note: on the website the tool is called "disk throughput tester".

Winload.exe missing or corrupt (0xc000000e)

2009-09-22T11:56:00.019+02:00

Today we had a problem with Acronis Disk Doctor. We used it to try to resize a existing partition. The tool doesn't directly support dynamic disks - only after downloading additional drivers. These were downloaded, because we had those in our system, and the system had to be rebooted. No changes had been made to this point. But after the reboot, the system stopped almost immediately with a "black screen" reporting:

0xC000000E \WINDOWS\SYSTEM32\WINLOAD.EXE INFO: THE SELECTED ENTRY COULD NOT BE LOADED BECAUSE THE APPLICATION IS MISSING OR CORRUPT

What made this case different from all others that I could find on the internet is that the system partition was no longer accessible. It was not assigned the C drive letter.

So, the suggestions I could find did not work. They include, but are not limited to:

Repair using the Vista installation disc
From the Vista installation disc's command prompt option:
bootrec /fixmbr -> completes succesfully, but didn't fix the issue
From the Vista installation disc's command prompt option:
bootrec /fixboot -> no system partition can be found
From the Vista installation disc's command prompt option:
bootrec /scanos -> no system partition can be found
Perform the following three commands from the Vista installation disc's command prompt option:
x:\windows\system32\bcdedit /set {default} device partition=c:
x:\windows\system32\bcdedit /set {default} osdevice partition=c:
x:\windows\system32\bcdedit /set {bootmgr} device partition=c:
-> no system partition can be found

Bootrec is the "fdisk /mbr" for Windows Vista and Windows 7.

If you got here and you still have a C drive letter assigned and that drive is accessible, then the suggestions above might work. I suggest you try them first.

If you don't have a system drive letter, then the resolution is fairly simple:

Start the repair option from a Windows 7 installation disk.

It detected that there were issues with the current Vista installation and fixed them. We rebooted the system and all was well again. No data loss and still running Vista.

We used a Windows 7 Ultimate x64 disk to fix a Windows Vista Ultimate x32 OS.

Cursor variants in Oracle 10g

2009-09-17T09:52:00.038+02:00

Examples of different types of cursors. Each has its own pros and cons. Example 1 is the most straightforward and easiest to understand. The variants in example 3 perform better. I've put them here for reference.

-- 1) implicit cursor

DECLARE l_old_tbs VARCHAR(100) := 'USERS';
BEGIN
FOR item IN (
SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs )
LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;

-- 2) explicit cursor

DECLARE l_old_tbs VARCHAR(100);
CURSOR c1 IS (SELECT OWNER, TABLE_NAME FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs);
BEGIN
l_old_tbs := 'USERS'; -- set variable here, otherwise it is NULL!
FOR item IN c1 LOOP
dbms_output.put_line(item.TABLE_NAME);
END LOOP;
END;

-- 3a) local collection variable with multiple fields using BULK COLLECT and FORALL

CREATE TABLE TEST1 (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30));
DECLARE l_old_tbs VARCHAR(100);
TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
TYPE t_tmp IS TABLE OF r_tmp;
tmp t_tmp;
BEGIN l_old_tbs:= 'USERS';
-- fill local collection "tmp" with one bulk operation
SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch. Check dbms_output, this is only one line.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 VALUES tmp(i);
dbms_output.put_line('first: 'tmp.FIRST', last: 'tmp.LAST);
COMMIT;
END;

-- 3b) local collection variable with single field using BULK COLLECT and FORALL

DECLARE l_old_tbs VARCHAR(100);
TYPE t_tmp IS TABLE OF ALL_TABLES.TABLE_NAME%TYPE;
tmp t_tmp;
BEGIN
l_old_tbs:= 'USERS';
-- fill "tmp" with one bulk operation
SELECT TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
-- update/insert/delete with one bulk operation; select not permitted.
-- no "loop" keyword, because following statement is performed as one batch.
-- always use "commit" statement
FORALL i IN tmp.FIRST..tmp.LAST
INSERT INTO TEST1 (TABLE_NAME) SELECT tmp(i) FROM DUAL;
COMMIT;
END;

-- 3c) local collection variable with multiple fields using BULK COLLECT and FOR

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  l_old_tbs VARCHAR2(100);
  TYPE r_tmp IS RECORD (OWNER ALL_TABLES.OWNER%TYPE, TABLE_NAME ALL_TABLES.TABLE_NAME%TYPE);
  TYPE t_tmp IS TABLE OF r_tmp;
  tmp t_tmp;
BEGIN
  l_old_tbs:= 'USERS';
  -- fill local collection "tmp" with one bulk operation
  SELECT OWNER, TABLE_NAME BULK COLLECT INTO tmp FROM ALL_TABLES WHERE TABLESPACE_NAME = l_old_tbs;
  -- update/insert/delete with one bulk operation; select not permitted.
  -- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
  -- always use "commit" statement
  FOR i IN 1..tmp.COUNT
  LOOP
    --INSERT INTO TEST1 VALUES tmp(i);
    dbms_output.put_line('tablename: '||tmp(i).TABLE_NAME||', last: '||tmp.LAST);
    COMMIT;
  END LOOP;
END;

-- 3d) local collection variable with single field using BULK COLLECT and FOR

SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
  i INT := 0;
  l_tabname VARCHAR2(100);
  TYPE r_tmp IS RECORD (TABLE_NAME USER_TABLES.TABLE_NAME%TYPE);
  TYPE t_tmp IS TABLE OF r_tmp;
  tmp t_tmp;
BEGIN
  -- fill local collection "tmp" with one bulk operation
  SELECT TABLE_NAME BULK COLLECT INTO tmp FROM USER_TABLES WHERE UPPER(TABLE_NAME) LIKE 'TTMP_%';
  -- update/insert/delete with one bulk operation; select not permitted.
  -- with "loop" statement. Check dbms_output, there are as much lines as tmp.LAST.
  -- always use "commit" statement
  FOR i IN 1..tmp.COUNT
    LOOP
      dbms_output.put_line(i);
      l_tabname := 'DROP TABLE ' || tmp(i).TABLE_NAME; 
      dbms_output.put_line(l_tabname);
      EXECUTE IMMEDIATE l_tabname;
      COMMIT;  
    END LOOP;
END;

-- 4) use a temp table

CREATE GLOBAL TEMPORARY TABLE tmp (OWNER VARCHAR(30), TABLE_NAME VARCHAR(30);

Create handwritten truetype font for free using fontcapture

2009-09-15T13:02:00.032+02:00

Fontcapture (http://www.fontcapture.com/) is the only website I could find that offers a service to convert handwritten characters into a True Type (.ttf) font for free. It supplies you with a template to fill in each of the glyphs (characters) of the font. You can print this template, fill it by pen, and scan and upload it to the fontcapture website. The bitmaps are turned into vector paths in the background and the font is created almost instantly.

Another advantage is that if you own a tablet pc or a (Wacom) pentablet, you can use it to fill in the template digitally. No need to print and scan the template.

The mechanism can also be used to trace bitmaps, since the tool does not distinguish between characters or other designs. The result is a font with vectorized images.

Alternatives:

Fontifier (http://www.fontifier.com/): almost identical to Fontcapture, but charges $9 per font
Yourfonts (http://www.yourfonts.com/): almost identical to Fontcapture, but charges $9 per font
Fontgrinder: (http://www.fontgrinder.com/): idem, but charges even more: $14.95
Fontstruct (http://fontstruct.fontshop.com/): free, but doesn't support handwritten fonts. The website has an editor for constructing your own font from predefined building blocks. A sort of "lego-for-font" method.

UPDATE 20100106: Unfortunately, the site has been taken offline. The domain is now referring to yourfonts.com. No free alternative is available anymore.

UPDATE 20110727: There is a new website offering the same free funtionality: http://www.myscriptfont.com

Netsend

2009-09-05T20:30:00.003+02:00

Windows Vista and Windos 7 are no longer shipped with the command line tool netsend. Fortunately, a freeware alternative is available at:

http://www.lantalk.net/netsend/

List of freeware tools

2009-09-04T14:35:00.062+02:00

Explorer & OS

Total Commander (shareware): http://www.ghisler.com/
7-Zip: http://www.7-zip.org/
VirtualCloneDrive: http://www.slysoft.com/nl/virtual-clonedrive.html
NTFS Undelete: http://www.ntfsundelete.com/
CDBurnerXP: http://cdburnerxp.se/en/download
Atto Disk Benchmark: http://www.attotech.com/products/product.php?sku=Disk_Benchmark

Editor

Notepad2: http://www.flos-freeware.ch/notepad2.html
ExifTool & ExifToolGUI: http://www.sno.phy.queensu.ca/~phil/exiftool

Graphics

FastStone Image Viewer: http://www.faststone.org/
Paint .Net: http://www.getpaint.net/
IcoFX: http://www.icofx.ro/
Screenhunter Free: http://wisdom-soft.com/products/screenhunter.htm
Inkscape: http://www.inkscape.org/
FontForge: http://fontforge.sourceforge.net/

Conversion

Super: http://www.erightsoft.com/
MediaCoder: http://mediacoder.sourceforge.net/download.htm
PDFsam (split and merge): http://www.pdfsam.org/
Bullzip PDF Printer: http://www.bullzip.com/products/pdf/info.php
A-PDF extractor (PDF to text): http://www.a-pdf.com/text/index.htm

Media & audio/dsp

Renoise: http://www.renoise.com/
Zoom Player 5.02 (6+ is no longer free): http://download.cnet.com/Zoom-Player-Standard/3000-2139_4-10068896.html?tag=mncol
Media Tagger (Ladislav Dufek): http://www.softpedia.com/get/Multimedia/Audio/Tag-Editors/Media-Tagger.shtml
Subtitle Workshop: http://www.urusoft.net/home.php?lang=1
Divx 7: http://www.divx.com/en/win
Youwashock: http://youwashockvst.googlepages.com/
Stomper: http://www.lysator.liu.se/~zap/stomper/index2.html
MP3Val: http://mp3val.sourceforge.net
T@B ZS4: http://www.zs4.net

Databases

PostgreSQL (as a service): http:/www.postgresql.org
SQL Server Compact 3.5 (in-process): http://www.microsoft.com/Sqlserver/2005/en/us/compact.aspx
SQLite (in-process): http://www.sqlite.org/

Great directors and actors

2009-08-27T22:49:00.008+02:00

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

2009-08-13T11:47:00.017+02:00

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

2009-08-12T13:44:00.017+02:00

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.

Limited connectivity using wireless internet in Vista Home SP1

2009-04-01T16:49:00.028+02:00

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

2008-02-15T11:28:00.013+01:00

-- list of available providers
exec master..xp_enum_oledb_providers

-- Two ways to read in Access data (file: c:\db1.mdb, tabel: TEST)
SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\db1.mdb;Uid=;pwd=;','SELECT * FROM TEST')

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

-- 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$]')

Converting WAV DTS to MP3

2008-01-05T17:39:00.000+01:00

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

Download BeSplit (http://www.afterdawn.com/software/audio_software/audio_tools/besplit.cfm). I used version 0.9b6.2.
Extract the zip file to c:\
Start BeSliced.exe
Drag and drop wavdts.wav onto the besliced canvas
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.
Download DTSDec (http://www.rarewares.org/others.php). I used version 0.0.2.7.
Extract the zip file to c:\
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
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

2007-11-02T15:17:00.000+01:00

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

2007-10-29T20:34:00.000+01:00

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

2007-10-27T14:53:00.000+02:00

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

2007-10-23T21:49:00.000+02:00

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

2007-03-13T08:51:00.000+01:00

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

2007-02-26T09:16:00.001+01:00

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

2006-07-28T09:32:00.000+02:00

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

2006-07-21T08:52:00.001+02:00

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

2006-07-20T16:58:00.000+02:00

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...

2006-07-20T10:17:00.000+02:00

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

2006-07-13T09:24:00.001+02:00

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

2006-07-11T16:11:00.001+02:00

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

2006-07-11T13:35:00.000+02:00

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

2006-07-11T12:00:00.000+02:00

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.