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

Install Epson AcuLaser AL-C900 on Windows 8

The Epson C900 AcuLaser can be easily installed on Windows 7 x64 and before using the Vista x64 driver available from the Epson website. In fact, most legacy printers can be installed using this method.

There might be an official in-box driver available for your (older) Windows operating system, i.e. it comes shipped with the C900 driver and recognizes the printer when it is connected via USB. No downloaded driver is then required.

Officially, there is no support and no driver for Windows 8 x64 for this printer. But luckily, the in-box driver is still present; it is just not recognized automatically on connection.

Manually install the Epson AL-C900 driver

Perform the following steps to get any legacy printer working on Windows 8:

1. Open Devices and Printers
2. Add a printer
3. Click "The printer that I want isn't listed"
4. Select "Add a local printer", next
5. Select "Use an existing port" and select "USB000", next
   This "Virtual printer port for USB" only exists when the printer is connected and turned on!
6. Select the following driver:
   Manufacturer: Epson
   Printers: Epson AL-C900
   Note: if the AL-C900 is not in the list, press "Windows Update" to get it
   next
7. Done!

The printer will now function, but only on the physical USB port that is linked to the virtual USB000 port. You need to figure out by trying port for port which one this is. Also note that in some cases it can take up to five minutes for a print job to start on this printer.

Delete an existing port mapping

Delete an existing - visible or invisible - port mapping as follows:

1. Open Device and Printers
2. Click Print server properties
3. Select Ports tab
4. Select USB port with the mapped printer driver
5. Click "Delete port"

Cannot delete port

Clear the printer spool folder beforehand by running these statements from a command prompt and reboot:

net stop spooler
del %systemroot%\system32\spool\printers\*.shd
del %systemroot%\system32\spool\printers\*.spl
net start spooler

USB printer is not recognized

If the USB printer is not recognized by Windows 8.1 (you don't even get the "USB device not recognized" message) then try the following:

• Control Panel -> Troubleshooting -> Hardware and Sound: Use a printer-> Next
• Device Manager -> Universal Serial Bus controllers -> Delete all Generic USB Hub entries and reboot

After reboot, open the Device Manager and plug in the printer. It should automatically appear. When the unplugged, it should immediately disappear.

PS3 controller on Windows 8.1 via bluetooth

A PS3 Sixaxis controller can be used as an input device on Windows 8.1.

For this to work the free Motion In Joy (MiJ) application needs to be installed: http://www.motioninjoy.com/download. At the time of writing, for Windows 8.1 x64, select v0.7.1000 (testing).

Using the controller via a wired USB connection almost always works, but using a wireless Bluetooth connection can be a problem.

Possible error: "Wait for bluetooth adabter to be connected" (Yes, there is a typo in adabter)

The causes for this are:

1. MiJ installs its own non-signed bluetooth driver, which is not allowed on Windows 8 unless the system is started with "driver signature enforcement" disabled
2. The bluetooth device's hardware id is not in the INF file of the MiJ bluetooth driver

The following instructions are from WingedT on:
http://forums.motioninjoy.com/viewtopic.php?f=15&t=3764&start=10

I revised them and put them on my blog in case that post is deleted from the Motion In Joy forum.

Instructions for bluetooth support:

1. Before starting, make sure bluetooth works by clicking the Bluetooth icon in the tray in right-bottom corner and selecting "Show bluetooth devices". If there are none, try "Add a bluetooth device" and try to pair e.g. a mobile phone. If this works, continue
2. Install and start MiJ's DS3 Tool
3. Select "Driver Manager" and click "install all"
   It might be that MiJ drivers for both devices (USB and bluetooth) are installed correctly. In that case they both have a green checkmark at the end in the MotionInJoy column. If one has a red cross, then most probably that is the bluetooth driver and that has not been installed
4. Take the Hardware ID of that device and strip the revision part, i.e. for a Lenovo Y510P this might be that USB\VID_8087&PID_07DA&REV_7869 becomes USB\VID_8087&PID_07DA
5. Open C:\Program Files\MotioninJoy\ds3\drivers\MijXinput.inf in a text editor. 
1. Find and replace:
   ;BLUETOOTH DONGLE
   with
   ;BLUETOOTH DONGLE
   %MIJ.DeviceName%=CC_Install, USB\VID_8087&PID_07DA
2. Delete the following line:
   catalog=MijXinput.cat
3. Save and exit
6. Restart Windows 8 with Advanced Options so Driver Signature Enforcement can be disabled. Open a command prompt (Win + X, command prompt) and type:
   shutdown /r o
7. In the Advanced Options screen, select Advanced Options -> Windows Startup Settings -> Restart
8. In the next screen with the Advanced Option to enable, choose: 7. Disable Driver Signature Enforcement. A reboot will follow
9. Select "Driver Manager" and click "install all". Now both devices should have a green check mark:
   
   
10. Exit and restart DS3 tool and once again click "Load driver". Now the bluetooth driver should be selectable in the BluetoothPair tab:
    
    

Reporting Services dynamic multi-column parameter box

The Reporting Services parameter section at the top of a report is generated as a HTML table with two fixed columns. To display SSRS parameters in three columns in the parameter field is not possible using a configuration option or even a CSS style sheet, so element conversion is needed.

The instructions below fixes this for the /reports and /reportserver web entry points and are suited for Reporting Services 2012, but might work for earlier versions as well.

The code:

• Adds the jquery library to every report
• Uses this library to:
• Create a DIV element for each set of TD parameter elements (=label and textbox/dropdown) and add it to the first TD element of the first TR row
• Add all sets of TD parameters to these DIVs
• Remove all other TR rows from the parameter TABLE

The result is that as many parameters as possible are fitted on one horizontal row:

The code has to be appended to the end of the ReportingServices.js file. This file is added automatically to every report generated by Reporting Services.

The file is most probably located in the public javascript folder: c:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\js

Perform the following steps to get a multiple columns in the parameter box:

1) Download and add the publicly available jquery-1.4.4.min.js to the public javascript folder.

2) Code to append:

// helper function to add scripts to head of html
function addHeadElement(headtype, file) {
var head = document.getElementsByTagName('head')[0];
  if (headtype == 'script')
  {
    var script = document.createElement('script');
    script.setAttribute('type', 'text/javascript');
    script.setAttribute('src', file);
    head.appendChild(script); 
  }
}
addHeadElement('script','/Reports/js/jquery-1.4.4.min.js');

// function that converts the parameter table to divs 
function param() {

  // create as many div->table->tr elements in the first column of the first row as there are parameters
  $(".ParamLabelCell").each( function (i, v) {
    var html = "<div style='float: left; padding-right: 10px;'> \
      <table> \
        <tr id='blackninja" + i + "'></tr> \
      </table> \
    </div>";
    $("td.InterParamPadding").filter(":first").append(html);
  });
  
  // move each label to its proper div->table->tr element
  $("td.ParamLabelCell").each( function (i, v) {
    $(this).appendTo("#blackninja" + i);
  });
  
  // move each prompt to its proper div->table->tr element
  $("td.ParamEntryCell").each( function (i, v) {
    $(this).appendTo("#blackninja" + i);
  });
  
  // remove all other parameter rows
  $("tr[isparameterrow='true']").not(':first').remove();

}
// add the convert function to the onload event twice
// this guarantees a cross-browser safe implementation
function pageLoad() {
  param();
}
window[ addEventListener ? 'addEventListener' : 'attachEvent' ]( addEventListener ? 'load' : 'onload', param );

3) Change style sheet

Add the following code to the styles\ReportingServices.css style sheet to make the dropdown boxes and textboxes smaller in width. More parameters will fit on a row:

.ParametersFrame.ParamsGrid.MenuBarBkGnd { padding: 0px; }
.DisabledTextBox { width: 125px; }
td.ParamEntryCell [id$="Value"] { width: 125px; }
td.ParamEntryCell { padding: 0px; }
td.SubmitButtonCell { padding-top: 2px; padding-bottom: 0px; }
td.ParamLabelCell { padding-right: 5px; }
td.InterParamPadding { padding-left: 0px; }
/* IE11 Edge/default profile fix */
tr.MenuBarBkGnd { height: 0px; } 
#ParametersRowctl31 { height: 0px; }

4) Enable in the reportserver

Add the following two elements to the head-element in \ReportServer\Pages\ReportViewer.aspx:

<meta http-equiv="X-UA-Compatible" content="IE=9">
<link href="/Reports/styles/ReportingServices.css" type="text/css" rel="stylesheet">
<script type="text/javascript" src="/Reports/js/ReportingServices.js"></script>
<script type="text/javascript" src="/Reports/js/jquery-1.4.4.min.js"></script>

Reinstall OEM-installed Windows 8.1

One of the first steps of the re-installation process of Windows 8.1 is that it asks for a Windows key. There is a "bug" that prevents a valid Windows key from working when the general Windows 8 installation disk is used on a laptop with a pre-installed OEM version.

Fortunately, if there are no installation disks supplied, then Windows 8.1 can still be easily re-installed on such a laptop, for example on another partition or disk:

1. Get a copy of the Windows 8.1 ISO, e.g. "Windows_8.1_EN-US_x64.ISO" and extract the contents to a USB stick

2. Bypass the Windows key screen by creating an ei.cfg text file in the sources folder on the USB stick. It should contain the following (replace "Core" with "Professional", if you have a valid key for that edition):

[EditionID]

Core

[Channel]

Retail

[VL]

0

3. Restart the laptop with the USB stick in it. The initial Windows key screen is skipped. During the latter part of the installation another Windows key screen pops up. Here the genuine valid key can be entered

Notes

• It has to be a USB stick, an external disk will not work: Win 8 can only be installed from an USB stick or a DVD
• Unfortunately, I cannot supply a link to the above mentioned ISO file
• Make sure the BIOS settings are such that the USB stick can be booted from and is the first in the sequence of bootable devices. Check how to enter the BIOS and set the bootable USB device on the site of the laptop manufacturer. Each manufacturer has a different method. For Lenovo IdeaPad: press and hold the small round button next to the power adapter entrance during a reboot

SQL Server contained database feature

SQL Server 2012 has a "contained database" feature.

In the normal case the logins of the (SQL Server) server are coupled to the users in each of its databases. The logins are stored in its MASTER database. If a database gets restored to another server, then the mapping is lost and you end up with "orphaned users". Those accounts can no longer access the database.

For a contained database on a SQL Server:

• The server logins are not used/required. Users can be created and used without a matching login on the server. These users are called "portable users"
• The database handles the autorization instead of the server. This is called "contained database authentication". Activate this delegation of functionality on the server:
  

  EXEC sp_configure 'contained database authentication',1
  GO
  RECONFIGURE

  In SSMS, a new option appears in the ->Security->Users->New User... screen: "SQL User with password"
• Works for SQL and Active Directory Accounts, e.g. statement for both without logins:
  

  CREATE USER [testdomain\testuser]
  CREATE USER [biuser]

Notes:

• Partial containment: in 2012 only the "partial containment" functionality works. In the future a "full containment" option if foreseen
• Connect from SSMS: you need to specify the database, otherwise the connection attempt fails, because the database is now doing the authentication
• Security: once a portable user gains access to a contained database through contained database authentication, that user also ends up gaining guest access to all other databases on the host system
• Conversion: conventional existing users can be converted into portable users through the use of a special stored procedure: sp_migrate_user_to_contained. It provides an argument that you can use to specify whether or not to disable the server-level login -- something you'll typically want to do as a best practice to avoid ugly login problems that can occur when duplicate logins and users overlap each other
• Downsides: no cross-database joins, linked server use, database mail, possible tempdb colation issues. Use "select * from  sys.dm_db_uncontained_entities" to check containment readiness

PowerPivot cannot load from procedure with info messages

Data can be loaded into PowerPivot from a SQL Server stored procedure, but the procedure cannot contain:

• PRINT statements, like print 'lets start'
• informational RAISERROR events, like raiserror ('lets start', 0, 1)

Both return messages to the client. Tools like Management Studio can handle these messages and display them in a separate window. PowerPivot handles them like the returned data and fails reporting an error. The error is not very helpful:

OLE DB or ODBC error: lets start; 01000.
An error occurred while processing table 'Query'.
The current operation was cancelled because another operation in the transaction failed.

The only solution is to remove these instructions from the procedure and any procedures it might call. There is no way to suppress this on the OLEDB connection during the call to the procedure.

It is also advisable to have the following two instructions at the start of the procedure:

• set fmtonly off
• set nocount on

LISTAGG / implode solution for SQL Server

Group multiple records into one and concatenate the values of the string/text values of a field:

ALTER view [dbo].[vcomments] as
with x as
(
select
cmt_domain,
cmt_indx,
cmt_seq,
concat(cmt_cmmt1,cmt_cmmt2,cmt_cmmt3,cmt_cmmt4,cmt_cmmt5,cmt_cmmt6,cmt_cmmt7,cmt_cmmt8,cmt_cmmt9,cmt_cmmt10,cmt_cmmt11,cmt_cmmt12,cmt_cmmt13,cmt_cmmt14,cmt_cmmt15) as cmt_cmmt,
cmt_lang
from
v_cmt_det_act
)
select
cmt_domain,
cmt_indx,
--FOR XML PATH returns a one record XML datatype in which all records are "unioned".
-- However, special characters are encoded as excape sequences, i.e. "<" becomes "<"
-- To reverse the encoding, get the entire root document/element as a value using the VALUE function.
-- The [1] is required because VALUE expects a singleton, i.e. exactly ONE element
rtrim(substring(
(
select char(10) + '[PAGE ' + cast(x2.cmt_seq as nvarchar(2)) + ']' + char(10) + x2.cmt_cmmt -- a field with no name gets no XML element to enclosed it. In short, the XML tag is removed
from x x2
where x1.cmt_domain = x2.cmt_domain and x1.cmt_indx = x2.cmt_indx
order by x2.cmt_seq
for XML PATH (''), type -- the brackets indicate the XML root element. Specifying an empty string as root element name removes it
).value('(/)[1]','nvarchar(max)')
,2,100000)) as cmt_cmmt
from
(
select distinct cmt_domain, cmt_indx
from x
) x1

Query Active Directory from SQL Server

Get active directory server and LDAP servers in a domain
nltest /dclist:sub.dom.com

results amongst others in:
\\dcserver1.sub.dom.com -> dcserver1 is the AD server

Analyse active directory structure
Use Sysinternals ADExplorer to analyse the structure of active directory. Connect to server: dcserver1

Users in a group from command prompt
net group adtestgroup /domain

Active Directory structure in FROM clause
E.g. FROM "LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com"

LDAP = case-sensitive protocol name, always this value
Before the slash: sub.dom.com, is the domain for which to find the AD structure
After the slash: the part of the tree to search. Specified from right to left. So in the example, from parent to child:
com -> dom -> sub -> Global -> Groups -> ADTestGroup, searches the nodes beneath the lowest level, which is ADTestGroup

The names ("dom", "Groups", etc), type ("OU", "CN", "DC") and depth of the levels are dynamic. So check with ADExplorer how AD is configured in the specific instance.

Users in a group from SQL Server
First, add a linked server to ADSI (fill with correct password for DOMAIN\ACCOUNT):

exec sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', @provider = 'ADSDSOObject', @datasrc = 'adsdatasource'
exec sp_addlinkedsrvlogin @rmtsrvname = 'ADSI', @useself = 'False', @locallogin = null, @rmtuser = 'DOMAIN\ACCOUNT', @rmtpassword = '********'

Select users in group "adtestgroup":

select * from openquery(ADSI,'
    SELECT objectCategory, cn, sn, mail, name, department, company
    FROM ''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''
    WHERE MemberOf=''CN=adtestgroup,OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
    ORDER BY cn
')

Unfortunately, dynamic sql is not possible inside a udf, so a procedure is needed:

alter procedure cst_usersingroup(@grp nvarchar(100))
as
begin
    declare @sql as nvarchar(max) = '
    select samaccountname as accountname, name, mail, department, title, company
    from openquery(ADSI,''
        SELECT objectCategory, cn, sn, mail, name, department, company, title, samaccountname
        FROM ''''LDAP://sub.dom.com/DC=sub,DC=dom,DC=com''''
        WHERE MemberOf=''''CN=' + @grp + ',OU=Groups,OU=Global,DC=sub,DC=dom,DC=com'''''')
    x
    order by samaccountname'

    exec(@sql)
end

Select all BI_* and EDW_* groups from active directory:

select lower(cn) as grp, whencreated, whenchanged, distinguishedname
from openquery(ADSI,'
    SELECT cn, distinguishedname, whencreated, whenchanged
    FROM ''LDAP://sub.dom.com/OU=Groups,OU=Global,DC=sub,DC=dom,DC=com''
    where CN=''bi_*'' or  CN=''edw_*''
') x
order by cn

Notes:

• In most cases, all domain users can query AD using the basic search method specified before. More advanced AD search methods might be disabled and require special rights

Create schema with separate autorization

Perform the following steps in SQL Server to create a schema called "bi" and give create view rights to user "domain\testuser":

CREATE SCHEMA [bi]
CREATE ROLE [db_bischema]
ALTER AUTHORIZATION ON SCHEMA::bi TO [db_bischema]
GRANT CREATE VIEW TO [db_bischema] -- "create view" includes the power to drop views
ALTER ROLE [db_bischema] ADD MEMBER [domain\testuser]

Advantages:

• If views are removed from or added to the schema, autorization does not need to be added to each individual view
• Give certain users the rights to (re)create the views inside the schema, without the risk of modification of the rest of the database

Pass table-valued parameter from VB.Net

1) Create table type itemtabletype for a table with one, unique clustered column called "item":

create type itemtabletype
as table (
item nvarchar(30) not null,
primary key clustered (item)
)

2) Create a procedure cst_testtabletype that has itemtable type as a table-valued input parameter:

create procedure cst_testtabletype

( @tv itemtabletype readonly )

as

begin

declare @itemcount int = 0

select @itemcount = count(*) from @tv

select concat('succesfully parsed item ',item) from @tv

end

The procedure returns all items provided in the table-valued parameter prefixed with the words "successfully parsed item ".

3) Give execution rights to user(s) and/or groups that are allowed to execute the procedure and table type:

grant exec on type::itemtabletype to [User]
grant exec on cst_testtabletype to [User]

4) Finally, create the following test routine  in VB.Net:

    Public Sub Start()

        'create data table
        Dim Table1 As DataTable

        'create a table named tmptbl
        Table1 = New DataTable("tmptbl")

        Dim row As DataRow

        Try

            'declare a column named item
            Dim item As DataColumn = New DataColumn("item")

            'setting the datatype for the column
            item.DataType = System.Type.GetType("System.String")

            'adding the column to table
            Table1.Columns.Add(item)

            'declaring a new row
            Dim i As Integer
            For i = 1 To 1000
                row = Table1.NewRow()
                row.Item("item") = i
                Table1.Rows.Add(row)
            Next

        Catch

        End Try

        'establishing connection. you need to provide password for SQL server
        Dim myConnection = New SqlConnection()

        myConnection.ConnectionString = "Server=mySQLServer;Database=myDB;Trusted_Connection=True;"

        Try

            myConnection.Open()

            Dim myCommand As New SqlCommand("cst_testtabletype", myConnection)

            ' the table-valued parameter is called @tv for cst_testtabletype
            Dim myparam As SqlParameter = myCommand.Parameters.Add("@tv", SqlDbType.Structured)
            'create parameter
            myCommand.CommandType = CommandType.StoredProcedure
            myparam.Value = Table1

            Dim dr As SqlDataReader = myCommand.ExecuteReader()
            While dr.Read()
                Debug.Print(dr.Item(0))
            End While

        Catch ex As SqlException

            MsgBox(ex.Message)

        End Try

    End Sub

IsNumeric() stinks

Read the thoroughly insightful and funny post by Mike Teevee as a reaction to the technet documentation with regards to the useless ISNUMERIC() function introduced in SQL Server 2012:
http://technet.microsoft.com/en-us/library/ms186272.aspx

Select "SQL Server 2012" under "Other Versions", otherwise the post is hidden.

Silverlight not working because of font issue

If Silverlight is installed correctly, but still not working, check for Windows Fonts with an ampersand in the name and remove them:

http://connect.microsoft.com/VisualStudio/feedback/details/719317/silverlight-5-error-1001-font-filename-issue

Uneasy way to determine proxy server

Open a command prompt and perform the following steps:

1. Inspect the output of netstat -an | find "EST" (short from 'ESTABLISHED')
2. Go to a fresh site (one that you have not recently visited)
3. Run the netstat command again, looking for the new connection. It might look like:
   TCP 192.168.1.1:1989 192.168.1.88:8080 ESTABLISHED

(credits go to Royce Williams)

Alternatively, install TCPView and look at the remote address and remote port columns for the browser connections.

Configure Reporting Services proxy for Bing maps

Add the following XML element to the web.config of both folders:
...\MSSQL\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\
...\MSSQL\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\

Add at the end between the runtime close tag and configuration close tag:

</runtime>
<system.net>
     <settings>
          <ipv6 enabled="true" />
      </settings>
      <defaultProxy enabled="true" useDefaultCredentials="true">
           <proxy bypassonlocal="True" proxyaddress="http://:"/>
       </defaultProxy>
</system.net>
</configuration>

It might be needed to restart the Microsoft Reporting Services service.