Primary, secondary, tertiary, etc

01: primary
02: secondary
03: tertiary
04: quaternary
05: quinary
06: senary
07: septenary
08: octonary
09: nonary
10: denary
11: ?
12: duodenary

TSQL - Full text search useful queries

-- Languanges

select

      *

from

      sys.fulltext_languages

order by

      name

;

-- Catalogs

select

      *

from

      sys.fulltext_catalogs

;

-- Indexes

select

      *

from

      sys.fulltext_indexes

;

-- Index browsing

select

      *

from

      sys.dm_fts_index_keywords(

            DB_ID('DATABASE_NAME_HERE')

            , OBJECT_ID('TABLE_NAME_HERE')

      ) as V1

where

      display_term <> 'END OF FILE'

;

TSQL - How to create a full text catalog, index and search

use AdventureWorks
go

-- Create the catalog

CREATE FULLTEXT CATALOG AdventureWorksFullTextCatalog;

GO

-- Create the full text index on the desired column(s)

CREATE FULLTEXT INDEX ON Production.ProductDescription

(

      Description Language 1033

)

KEY INDEX PK_ProductDescription_ProductDescriptionID

ON AdventureWorksFullTextCatalog;

GO

-- Search sample

DECLARE @SearchWord nvarchar(30);

      SET @SearchWord = N'high-performance';

SELECT

      Description

FROM

      AdventureWorks.Production.ProductDescription

WHERE

      FREETEXT(Description, @SearchWord)

;

ORACLE - How to get the SQL code of a view

SELECT view_name, text
FROM all_views
WHERE view_name like '%VIEW_NAME%';

Virtual Box - How to clone a virtual machine

First, make a copy of the hard drive(s) of the original virtual machine (using the command line).

vboxmanage clonehd ORIGINAL_HD_FILENAME.vdi NEW_HD_FILENAME.vdi

Then, simply create a new virtual machine with the same specs as your original one, specifying the new hard drive(s) copy(ies).

Linux - Change file ownership

sudo chgrp YOUR_GROUP_NAME "FILE NAME.EXTENSION"

Punch Out! - Pass keys

After wining the first title bout: 005 737 5423
More to come, I haven't played this in ages.

HTTP - Apache Benchmark Tool

This is a great way to test the capacity to respond quickly and concurrently of a website.  You need the Apache Utilities 2 package in order to try this in Linux.

(1) Get the utilities through this command:  apt-get install apache2-utils

(2) Run the "ab" command (Apache Benchmark) like this:  ab -n 100 -c 10 http://test.com/pagex.htm

100 represents the number of requests to be sent.
10 represents how many concurrent requests are to be made.
Make sure to point not to the root of a site, but to a specific document.

Linux - CURL to get the text of an HTTP call

Use this command to get the HTTP response from calling any website. CURL is much more powerful than this, but this is a nice little way of using it.

curl http://test.com

Linux - How to change your MAC address

sudo ifconfig eth0 down
sudo ifconfig eth0 hw ether FF:FF:FF:FF:FF:F
sudo ifconfig eth0 up

Linux - How to get your MAC address

ifconfig | grep HWaddr

Linux - How to open a file with root privileges using a launcher in GUI mode

Simply create a new launcher with this command: gksudo "gnome-open %u"

Then just drop any file into the launcher and it will open the file, in the default application, with root privileges.

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:

1. Get rid of the file "C:\ProgramData\Microsoft\PlayReady\mspr.hds"
2. Re-start your browser.
3. 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:

1. sudo modprobe vboxnetflt
2. 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

;

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.

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

;

TSQL - Dynamic Top N

Declare @rows int;

set @rows = 5;

select top (@rows)

      *

from

      TABLE_NAME_HERE

;

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

;

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

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     

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.

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

AS

BEGIN

      -- Refresh Record Edit tracking fields

      IF UPDATE(Field1)

            UPDATE

                  T

            set

                  T.RecordEditTimestamp = GETDATE()

                  , T.RecordEditUserID = suser_sname()

            from

                  Table1 as T

                  inner join

                  inserted as S

                  on

                  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"?>

<DataMacros xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application">

       <DataMacro Event="BeforeChange">

              <Statements>

             

             

                     <ConditionalBlock>

                           <If>

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

                                  <Statements>

                                         <Action Name="SetField">

                                                <Argument Name="Field">RecordInsertUserIDArgument>

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

                                         Action>

                                  Statements>

                           If>

                           <Else>

                                  <Statements>

                                         <Action Name="SetField">

                                                <Argument Name="Field">RecordInsertUserIDArgument>

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

                                         Action>

                                  Statements>

                           Else>

                     ConditionalBlock>

                    

                    

                     <ConditionalBlock>

                           <If>

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

                                  <Statements>

                                         <Action Name="SetField">

                                                <Argument Name="Field">RecordInsertTimestampArgument>

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

                                         Action>

                                  Statements>

                           If>

                     ConditionalBlock>

             

             

                     <Action Name="SetField">

                           <Argument Name="Field">RecordEditTimestampArgument>

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

                     Action>

                     <Action Name="SetField">

                           <Argument Name="Field">RecordEditUserIDArgument>

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

                     Action>

                    

                    

              Statements>

       DataMacro>

DataMacros>