IBM Informix 64 bit ODBC connection

Creating a 64-bit ODBC connection for IBM Informix is a bit tricky. By default, only the 32-bit drivers are installed, so a x64 DSN cannot be created
  1. Download the IBM Informix Client SDK from IBM Software Downloads
    At the time of writing, this is the version to get for Windows x64:
    Informix Client SDK Developer Edition for Windows x86_64, 64-bit, clientsdk.4.10.FC5DE.WIN.zip
    Note that you need to create a free IBM ID first.
  2. Extract the zip to a folder
  3. Right-click installclientsdk.exe and select Troubleshoot compatibility
  4. Select Try recommended settings and select Start the program. The installation might progress slower then usual!
  5. Step through the installer by clicking Next
  6. At the production selection screen, deselect everything except:
    1. IBM Informix ODBC Driver
    2. Global Language Support (GLS)
  7. Step through the installer by clicking Next until the end
Now a linked server can be created in SQL Server x64 that uses a DSN created with this driver.

Main tip from here: StackOverflow

Raspberry Pi 2 Kodi storage on USB using UUID

A default OpenELEC or OSMC MMC/SD-card contains two partitions:
  1. A FAT32 partition with the kernel and system file and a cmdline.txt file that specifies the two partitions to use
  2. An EXT4 partition with the data, like artist/video thumbnails, the video and texture databases, and user configurations
The first issue is that a MMC/SD-card is:
1) less robust and gets corrupted easily and
2) slower then a 2.0 USB drive (note that the most recent Raspberry Pi 2B only supports up to USB 2).

The second issue it references these partitions by a "logical" name that can change from boot to boot. Especially for USB (thumb) drives that get removed and reconnected. For example, a drive might be /dev/sda5 during one boot, but /dev/sdb5 during the next. These logical names are used by default in the cmdline.txt file in the FAT32 partition when OpenELEC or OSMC starts. But it is better to use the unchangeable UUID that each drive has.

How to put the 2nd partition on a USB thumb drive using Windows and UUID's (OpenELEC, but OSMC is similar:
  • Download the 5.95.5 image from OpenELEC. There might be a more recent version, but this guide is tested with that one
  • Download and install Win32DiskImager on Windows
  • Insert the MMC/SD-card into a card reader that is connected to the Windows machine. If you don't have one, you need to buy it
  • Start Win32DiskImager and select the downloaded image. The file has extension .img. Select the device letter the MMC/SD-card has been assigned and press Write
  • Insert the USB thumb drive into the Raspberry Pi
  • Insert the MMC/SD-card into the Raspberry Pi and connect the power. The Raspberry Pi should already have a connected Ethernet or Wifi dongle before the power is connected!
  • Wait while the EXT4 partition is configured with i.e. the video database and Kodi folder structure. This EXT4 partition will be copied to the USB thumb drive later on.
    A short while after that, Kodi is started presenting an installation wizard
  • Follow the installation wizard and make sure the network connection is up and running and activate SSH. Keep OpenELEC running.
  • Download and install PuTTY on Windows
  • Start PuTTY and make a connection to the OpenELEC Raspberry Pi using its IP address on port 22 and selecting SSH
  • Click Yes to accept the new server host key
  • OpenELEC always has user: root and password: openelec
    OSMC always has user: osmc and password: osmc
  • Type in: blkid and hit enter and copy the UUID's from the FAT32 partition (most probably denoted by /dev/mmcblk0p1) and the USB thumbdrive (can be anything like /dev/sda1, /dev/sda5, /dev/sdb1, dev/sdb5, etc)
    Example result from blkid:
    /dev/mmcblk0p1: SEC_TYPE="msdos" UUID="6495-ECA4" TYPE="vfat" PARTUUID="84e76e9c-01"
    /dev/sda5: UUID="df2ea9f9-bdc6-4e79-b436-d2aa272ddac0"
  • Create a text file called cmdline.txt with this single line of text and UNIX line endings.
    For OpenELEC, specify the boot and disk locations using the UUID:
    boot=UUID=6495-ECA4 disk=UUID=df2ea9f9-bdc6-4e79-b436-d2aa272ddac0 quiet
    For OSMC, use the PARTUUID for the root (OSMC does not support UUID):
    root=PARTUUID=04030201-05 rootfstype=ext4 quiet osmcdev=rbp2 boot_delay=50 rootdelay=10 rootwait
    Note that the "boot" disk does not need to be specified for OSMC.
  • Shutdown OpenELEC properly from the Kodi interface and when done remove the power from the Raspberry Pi
  • Remove the USB thumb drive and the MMC/SD-card from the Raspberry Pi and insert both into the Windows machine
  • Download and install MiniTool Partition Wizard Free on Windows
  • Start MiniTool and click "launch application"
  • If the USB thumb drive has partitions, then select them and one by one select Delete Partition from the Operations pane
  • Press Apply and wait for the partitions to be converted into one big unallocated partition
  • Select Copy Partition Wizard from Operations and 
    • Select the EXT4 partition from the SD/MMC-card as source
    • Select the unallocated partition from the USB thumb drive as the destination
    • Press Apply and wait for the copy to finish
  • Close MiniTool and copy the previously created cmdline.txt to the root of the FAT32 partition of the MMC/SD-card. Overwrite the existing file there
  • Remove the USB thumb drive and MMC/SD-card from the Windows machine and put them in the Raspberry Pi. Connect the power
It might be that the Raspberry boots too quick and the USB disk is not yet available. This results in a black screen and freezed boot. For OSMC, a delay for system start in general (rootdelay) and each kernel call (boot_delay) can be used. Add this to the cmdline.txt (already done in the example above):
boot_delay=50 rootdelay=10

The MMC/SD-card can become corrupted easily when e.g. the Raspberry Pi 2 hangs or Kodi is writing too long to the video database and hangs, or there is a power glitch. In that case, re-writing the disk image is the safest and quickest option. The alternative is to download the KERNEL and SYSTEM file archive from OpenELEC and copy the necessary files manually to the FAT32 partition on the MMC/SD-card. This method however might still result in the Raspberry Pi not booting if the corruption is severe.

The BLKID tool can also be downloaded together with the necessary Cygwin libraries. Here is one by user bircoe: blkid for Windows. The downside is that this implementation - as well as the blkid on the OpenELEC/OSMC command line when it does not boot correctly, and you are stuck at the command prompt - does not show the PARTUUID (partition ID). It only displays the UUID, which is the filesystem ID. The SSH version does return the PARTUUID also.

It might also be that the combination of SDA and UUID does not work. To be sure do not use this combination:
boot=/dev/mmcblk0p1 disk=UUID=df2ea9f9-bdc6-4e79-b436-d2aa272ddac0 quiet
but always use UUID for both
boot=UUID=6495-ECA4 disk=UUID=df2ea9f9-bdc6-4e79-b436-d2aa272ddac0 quiet

One attempt with UUID's in the cmdline.txt file was not successful; OpenELEC would not boot and freeze at the gradient over-powered picture. Re-writing a clean image to the MMC/SD-card and putting the cmdline.txt file on it again solved the problem.

Videos starting slow from SMB share

What is written below does not work, because the function that scans for local subtitle files in the movie folder is hard-coded to always run. The function CUtil::ScanForExternalSubtitles can be found in the Kodi source code file util.cpp on github: https://github.com/xbmc/xbmc

The subtitle extensions Kodi scans for are hard-coded in source code file AdvancedSettings.cpp in global variable m_subtitlesExtensions and cannot be set using e.g. advancedsettings.xml.

The only workaround is to divide homevideos over subfolders, e.g. per year, with a maximum of about 500 videos per folder, to keep the load on an acceptable level.

Old post:

(Home)videos can be stored on an external SMB device and added as a "source" to OpenElec/Kodi on a Raspberry Pi 2.

If those videos load extremely slow, while the CPU and the Wifi or cabled network connection have capacity enough, then this might be caused by:
  1. The spinning up/down of the external disk the source is pointing to
  2. The search for subtitle files on the external storage
The disable the search for subtitle files (written using Openelec 14.2):

1) In Kodi using the "Eminence" skin, enable logging under settings -> system -> debugging ->
  • enable debug logging: true
  • enable component specific logging: true
    • specify component-specific logging: "verbose logging for the SMB library"
2) Start a video from the SMB source
3) On a Windows client, use a SFTP-capable client like Filezilla to connect to the Raspberry Pi with these settings:
  • host: IP address of the Raspberry Pi, e.g. 192.168.0.95
  • protocol: sftp
  • user: root
  • password: openelec
4) Navigate to path: /storage/.kodi/temp and download the file kodi.log
5) Open kodi.log using a text editor and search for "ScanForExternalSubtitles: END". If the "total time" is more then say 10000 ms, then Kodi is trying to search for a subtitle file, e.g. movie.srt. There probably are none for a home movie, so this is not necessary
6)  In Kodi under settings -> video -> subtitles ->
  • subtitle storage location: custom location
  • customer subtitle folder: set to a local, empty folder, e.g. /home/screenshots
  • pause when searching for subtitles: disable

There might also be a difference between drives, used file system, and the number of files in the source folder.

My Seagate Expansion Portable (STBX2000401) 2TB USB disk on NTFS reports ~200ms for subtitle search, while my WD MyPassport 2TB on FAT32 reports ~13000ms subtitle scan time.

Create RSS feed from Microsoft Live Calendar and Google Calendar

In Microsoft Calendar:
  • Click "Share" on the top menu bar and select the calendar to share
  • Under Share, click Get link. If this has already been done, the message "You've already created all possible link types" appears. If both cases, continue with next step
  • Under Permissions, click Links to event details and select and copy the URL from the ICS textbox.

The URL from XML only contains a random subset of calendar events and can therefore not be used directly as a complete RSS feed.

That is why Google Calendar is needed:

In Google Calendar:
  • On the main page, click on the down arrow next to Other Calendar and click "Add by URL"
  • Paste the ICS link from the Microsoft Calendar. Replace the "webcal://" prefix with "http://". Enable "Make calendar publicly available?"
  • Wait for the calendar to be added and click on the down arrow next to it
  • In the section "Calendar Address", click on Change sharing settings and select "Make this calendar public" and press Save
  • Again, click on the down arrow next to the calendar that was just added
  • In the section "Calendar Address", click on the XML icon and copy the link
  • Test the link in a browser by pasting it. Add "?max-results=99999" at the end of the URL to get all events.
By default only the 25 most recently changed events are shown. To get all events in the RSS feed:
www.google.com/calendar/feeds//private-/basic?max-results=99999

Create script for deploying database roles and rights

The script below returns the create statements for all roles in a database. It also creates the statements that add the role members to the roles and assigns the role rights to the objects.

It can be filtered for one or some specific roles, e.g. the role "db_servicecockpit".

select sql from
(
-- 1) create role
select 1 as ord,
r.name as rolename,
concat('CREATE ROLE [',r.name,']') as sql
from sys.database_principals r
union all
-- 2) add members
select 2 as ord,
r.name as rolename,
concat('EXEC sp_AddRoleMember ''',r.name,''', ''',u.name,'''') as sql
from sys.database_principals u
join sys.database_role_members l on l.member_principal_id = u.principal_id
join sys.database_principals r on r.principal_id = l.role_principal_id
union all
-- 3) apply role rights to objects
select 3 as ord,
r.name as rolename,
'GRANT ' + p.permission_name + ' ON ' +
CASE p.class_desc
WHEN 'SCHEMA' THEN concat('[',schema_name(major_id),']')
WHEN 'OBJECT_OR_COLUMN' THEN
CASE
WHEN minor_id = 0 THEN concat('[',OBJECT_SCHEMA_NAME(major_id),'].[',object_name(major_id),']') COLLATE Latin1_General_CI_AS_KS_WS
ELSE
(
SELECT concat(object_name(object_id),' (',c.name,')')
FROM sys.columns c
WHERE o.object_id = p.major_id
AND c.column_id = p.minor_id
)
END
ELSE 'other'
END + ' TO [' + r.name + ']'  as sql
from sys.database_permissions p
join sys.database_principals r on p.grantee_principal_id = r.principal_id
left join sys.objects o on o.object_id = p.major_id
where p.major_id > 0
) x
where rolename = 'db_servicecockpit'
order by ord

Reporting Services in SQL Server 2016

Installing SQL Server 2016 CTP2 (Community Technology Preview) to test Reporting Services (SSRS) is a bit of a deception: the installer does not contain the developer tools! They (SSDT-BI or Report Builder) are also not yet available as a separate download as of June 10th 2015.

This fact is not communicated anywhere by Microsoft, although there is a mention by the SQL Server Team on June 1st 2015  in reaction to user Dave's remark concerning this:

Dave, agree completely, the tools upgrade is coming."

Source:
http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/27/sql-server-2016-first-public-preview-now-available.aspx

Or here by another user:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fa8133bd-5cfa-4add-aa20-4c5442760d65/download-ssdtbi-samples-for-sql-server-2016?forum=ssdt

This prevents users from testing e.g. the new configurable parameter box.

For those still interested, the CTP can be found here:
https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2016

Analyzing string or binary data would be truncated error

Based on the ideas in this post, I've made an improved and dynamic version to help in determining the violating records when inserting into a table:
http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx

create procedure cst_checktruncatederror  as
begin
set nocount on
declare @src nvarchar(max) = 'sourcetable'
declare @tgt nvarchar(max) = 'targettable'
declare @cols nvarchar(max)
declare @sql nvarchar(max)

-- create temporary empty version of destination table (target proxy)
-- note: use a global temp table, because a normal one goes out-of-scope when the dynamic sql returns
if (object_id('tempdb..##target') is not null) drop table ##target
set @sql = concat('SELECT * INTO ##target FROM ',@tgt,' where 1=0')
print @sql
exec sp_executesql @sql

-- get a comma-separated string with block-quoted columnnames that are comparable
set ansi_padding on
set @sql = concat('
select @colsOUT =
substring
(
(
select concat('',['',column_name,'']'')
from information_schema.columns
where table_name = ''',@tgt,'''
and data_type <> ''geography''
for xml path(''''), type
).value(''.'',''nvarchar(max)'')
,2,100000
)
')
print @sql
exec sp_executesql @sql, N'@colsOUT nvarchar(max) OUTPUT', @colsOUT=@cols OUTPUT
print @cols

-- load source to target proxy
set @sql = concat('
INSERT INTO ##target (',@cols,')
SELECT ',@cols,'
FROM ',@src,'
')
print @sql

SET ANSI_WARNINGS OFF -- truncates fields instead of throwing "string or binary data would be truncated error" error!
-- 1. do not use * to insert, because the column order might be off
-- 2. wrap fieldnames in blockquotes [] because reserved words might have been used, e.g. STATE or UNIQUE
-- 3. exclude data types: geography
-- 4. TO DO: destination columns might be NULLable, while the source is not. That results in a different error
exec sp_executesql @sql
SET ANSI_WARNINGS ON

-- return violating records
set @sql = concat('
SELECT ',@cols,'
FROM ',@src,'
except
SELECT ',@cols,'
FROM ##target
')
print @sql
exec sp_executesql @sql

-- clean up
if (object_id('tempdb..##target') is not null) drop table ##target
end