Painting Blue Code: 09/01/2011

2011-09-30

Netflix - Digital Rights Management Error DRM N8156-6013

So I got this annoying error while trying to watch Netflix on Ubuntu, and also on Windows 7. You just need to:

Get rid of the file "C:\ProgramData\Microsoft\PlayReady\mspr.hds"
Re-start your browser.
Re-try watching something good on Netflix... voila!

Virtual Box - Failed to open/create the internal network

I got this error message after upgrading Ubuntu 11.04 with only-God-knows which sort of update. You solve it by running these 2 commands on Terminal:

sudo modprobe vboxnetflt
sudo modprobe vboxdrv

Failed to open a session for the virtual machine XXX.

Failed to open/create the internal network 'HostInterfaceNetworking-eth1' (you might need to modprobe vboxnetflt to make it accesible) (VERR_SUPDRV_COMPONENT_NOT_FOUND).

Please install the virtualbox-ose-dkms package and execute 'modprobe vboxdrv' as root (VERR_SUPDRV_COMPONENT_NOT_FOUND).

TSQL - Server last reboot datetime

SELECT

[Name]

, CrDate as ServerLastRebootDateTime

FROM

sys.sysdatabases

WHERE

dbid = 2

;

2011-09-29

Virtual Box - Right CTRL not working

If the right CTRL (control) key is not working, use ALT+CTRL.
This worked for me on Virtual Box for Linux.

2011-09-28

Virtual Box - Fixing the "please install the virtualbox-ose-dkms package" error

This is how I fixed it in Ubuntu 11.04.
This did it for me and I hope it helps you.

(1) Open the Synaptic Package Manager.
(2) Look for the Virtual Box KMS through a keyword search under "kms", then select it to be re-installed. Apply the changes.
(3) After the installation is done, open Terminal and execute this command: sudo modprobe vboxdrv

TSQL - Number of times a string repeats within a varchar field value

The trick is to compare the length of the original field, with the length of the field with all instances of the string you are looking for removed.

select

Original

, LEN( Original ) as OriginalLen

, REPLACE( Original, '|', '' ) as OriginalWithoutPipes

, len( REPLACE( Original, '|', '' ) ) as OriginalWithoutPipesLen

, LEN( Original ) - len( REPLACE( Original, '|', '' ) ) as NumberOfPipes

from

(

select 'asdf|asdf|asdf' as Original

) as V1

;

2011-09-22

TSQL - Dynamic Top N

Declare @rows int;

set @rows = 5;

select top (@rows)

from

TABLE_NAME_HERE

;

2011-09-21

TSQL - Comma number formatting

select

SUBSTRING

(

CONVERT( varchar, CAST( 1234567 as money ), 1 )

, 1

, charindex

(

'.'

, CONVERT(varchar(20), CAST( 1234567 as money ), 1 )

, 2

) - 1

)

;

Blogspot search gadget

<form

id="searchthis"

action="/search"

style="display: inline"

method="get"

<input

id="search-box"

name="q" size="30"

<input

id="search-btn"

value="Search"

type="submit"

form>

TSQL - Removing leading zeroes

select

substring(ColumnName, patindex('%[^0]%',ColumnName), 8000)

from

(

select '00010A0' as ColumnName

union all select 'sdfa'

union all select '00001'

) as V1

;

2011-09-16

Oracle - SQLPlus command-line login

sqlplus [USER_NAME_HERE]/[USER_PASSWORD_HERE]@//[SERVER_ADDRESS_HERE]:[PORT_NUMER_HERE_DEFAULT_IS_1521]/[SID_HERE]

Oracle - ODBC Setup

After struggling to configure Windows 7 64 Bit ODBC to Oracle 11.2 using Oracle's native ODBC drivers, I found this is the way to setup the ODBC connection:

Parametric example
Data Source Name: TNSNAME_ENTRY_NAME_HERE
TNS Service Name: DOMAIN_HERE.SERVER_ADDRESS_HERE.COM:PORT_HERE/SID_HERE
User ID: USER_NAME_HERE

Example with values
Data Source Name: NORTHWIND
TNS Service Name: EXAMPLE.DOMAIN.COM:1521/NORTHWIND
User ID: JPICARD

Top and Bottom Guitar Strings

Top: the highest note strings (1st, 2nd, 3rd).

Bottom: the lower note strings (6th, 5th, 4th).

A light top might range from 0.008 to 0.010.
A medium top is usually considered 0.011.
A heavy top usually starts at 0.012.

A heavy bottom usually has a lower E string gage of 0.052.
An extra heavy bottom would be of around 0.056.
A heavier bottom is better for lower tunings, such as "Drop D".

2011-09-15

Number 142857

This happens with other numbers too.
See how the digits switch their place.

1 × 142,857 = 142,857
2 × 142,857 = 285,714
3 × 142,857 = 428,571
4 × 142,857 = 571,428
5 × 142,857 = 714,285
6 × 142,857 = 857,142
7 × 142,857 = 999,999

Three ones to arithmetically get all digits

With three ones, calculate all digits (natural numbers from 0 to 9), using simple arithmetic operations.
Here is a solution:

0 = (1-1)/1
1 = 1+1-1
2 = 1+(1/1)
3 = 1+1+1
4 = (1+1)^/1
5 = 1 + (1+1)^2
6 = (1+1+1)!
7 = ( (1+1)^ )^ - 1
8 = (1+1+1)^
9 = ((1+1)^)^ + 1

Teradata - Date to YYYYMM integer

cast(

( extract( year from DATE_FIELD_HERE ) * 100 )

+ extract( month from DATE_FIELD_HERE )

as integer

) as PeriodID

2011-09-14

SSIS - Running packages on 64-bit computers

If you need to run a SQL Server 2008 R2 package in a 64-bit computer, and you are having trouble because the connection to an Excel 2010/2007 file is not working, read this article.

In short...

In the Project Properties of an Integration Services package, you can select 32-bit or 64-bit execution by setting the value of the Run64BitRuntime property on the Debugging page. By default, the value of this property is True. When the 64-bit version of the Integration Services runtime is not installed, this setting is ignored.

2011-09-13

VBA - Remove string leading zeroes using a regular expression

Public Function fStringLeadingZeros(str As String) As String

Dim re As Object

Set re = CreateObject("VBScript.RegExp")

re.pattern = "^0+"

fStringLeadingZeros = re.Replace(str, "")

Set re = Nothing

End Function

TSQL - Logged in user ID retrieval

-- 1. System defined function.

select

suser_sname()

;

-- 2. System variable.

select

SYSTEM_USER

;

TSQL - Unique identifier function

select

Field1

, NEWID() as NewUniqueIdentifier

from

Table1

;

TSQL - Record edit tracking trigger

CREATE

TRIGGER dbo.tTable1RecordEdit

ON dbo.Table1

FOR UPDATE

BEGIN

-- Refresh Record Edit tracking fields

IF UPDATE(Field1)

UPDATE

set

T.RecordEditTimestamp = GETDATE()

, T.RecordEditUserID = suser_sname()

from

Table1 as T

inner join

inserted as S

T.RecordID = S.RecordID

;

END

TSQL - Raise error

IF(

SELECT

COUNT(*)

FROM

TABLE1

) = 0

BEGIN

RAISERROR

(

'error message goes here'

,16 -- Severity.

,1 -- State

,1 -- myKey that was changed

)

END

;

TSQL - List or all tables and columns

SELECT

table_name

,column_name

,data_type

,character_maximum_length

FROM

information_schema.columns

;

Microsoft Access 2010 - Select directly from and ODBC source table

select

from

[ODBC;DSN=ODBC_DNS_NAME_HERE;].[TABLE1]

;

Microsoft Access 2010 - Rank function equivalent turnaround

SELECT DISTINCT

T1.PeriodDate

, (

SELECT

COUNT(*)

FROM

(

select distinct

PeriodDate

from

TABLE1

) as N2

WHERE

N2.PeriodDate >= T1.PeriodDate

) AS Rank

FROM

TABLE1 AS T1

ORDER BY

T1.PeriodDate DESC

;

Microsoft Access 2010 - Hiding the ribbon programmatically

SendKeys "^{F1}", True

Microsoft Access 2010 - Record level edit tracking fields

This code works great in order to add some tracking fields to updates made to a Microsoft Access 2010 table. It calls a function through a DLOOKUP into a view (vgetUserID), that returns the windows network ID of the logged in user.

xml version="1.0" encoding="utf-16" standalone="no"?>

<If>

<Condition>IsNull([Old].[RecordInsertUserID])Condition>

<Argument Name="Field">RecordInsertUserIDArgument>

<Argument Name="Value">DLookUp("UserID","vgetUserID")Argument>

Action>

Statements>

If>

<Else>

<Argument Name="Field">RecordInsertUserIDArgument>

<Argument Name="Value">[Old].[RecordInsertUserID]Argument>

Action>

Statements>

Else>

ConditionalBlock>

<If>

<Condition>[RecordInsertTimestamp]<>[Old].[RecordInsertTimestamp]Condition>

<Argument Name="Field">RecordInsertTimestampArgument>

<Argument Name="Value">[Old].[RecordInsertTimestamp]Argument>

Action>

Statements>

If>

ConditionalBlock>

<Argument Name="Field">RecordEditTimestampArgument>

<Argument Name="Value">Now()Argument>

Action>

<Argument Name="Field">RecordEditUserIDArgument>

<Argument Name="Value">DLookUp("UserID","vgetUserID")Argument>

Action>

Statements>

DataMacro>

DataMacros>