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