Ubuntu - List all USB devices

On the terminal, simply type this command:

lsusb


For more of its options, read
http://manpages.ubuntu.com/manpages/hardy/man8/lsusb.8.html

SQL Server 2012 - Code editor custom color

This guy has a good solution... simple and quick.
http://www.jimmcleod.net/blog/index.php/2012/06/26/beautify-your-management-studio-2012/

Ubuntu - Timed shutdown from the command line

sudo shutdown -h 23:59 "Custom log message about why you are shutting down."

Ubuntu - How to pair two devices unto a single Logitech Unifying receiver

(1) Download the files autopair.sh and unify.c from this guy's github page... it is amazing that people take the time to actually do this, and share it... hats off to them.
https://github.com/treeder/logitech_unifier

(2) Make the autopair.sh file executable.

(3) Execute it (double click or from terminal) and follow the on-screen instructions. You basically have to turn off the second device you use to sync (preferably keep using the keyboard's unifying receiver), and when prompted, turn it back on.

Voila!

Ubuntu - How to execute an .sh file

(1) Make sure it is allowed to execute. Right click on it and set, on the "Permissions" tab, the checkbox that reads "Execute... allow executing file as a program". Alternatively, use the following command line:

chmod +x filename.sh


(2) Execute it by either double clicking on it, or using the command line. Below is the command.

sudo ./filename.sh

Ubuntu - Desktop missing, low resolution, displays don't work

This is how it worked for me, on Ubuntu 12.10... I accidentally installed bumblebee on my desktop computer. I had the nvidia drivers. I ended-up using whatever the default for Ubuntu is, and it works like a charm now (dual display).

• apt-get purge nvidia-current
• rm /etc/X11/xorg.conf
• sudo apt-get update
• sudo apg-get install ubuntu-desktop
• sudo service lightdm restart
• sudo reboot

Voila!

TSQL - Quickly copy a linked server table

select

       *

into

       DATABASE.dbo.TABLE1

from

       openquery(

              LINKED_SERVER_NAME

              , '

                     select

                           *

                     from

                           TABLE2

                     where

                           PeriodID=201211

              '

       )

;

TSQL - Creating a view that uses a CTE

create view vTest1 as

with v1( a )

as

(

       select 1 as a

)

select

       *

from

       v1

;

EXCEL - Save all open workbooks macro

This macro will save all open non-read-only workbooks.


Sub x()
    Dim w As Workbook
    For Each w In Workbooks
        If Not w.ReadOnly Then
            w.Save
        End If
    Next
End Sub

Ubuntu 12.04 - Installing and registering a DVD decryption library

I couldn't open any DVD... this fixed it. Run the following two commands on terminal. Don't worry if the first step tells you that you already have the library, as the second step registers it properly.

sudo apt-get install libdvdread4

sudo /usr/share/doc/libdvdread4/install-css.sh

Reference, source:
http://askubuntu.com/questions/109103/how-do-i-install-a-dvd-decryption-library

TSQL - Excel numeric date to SQL date

select

       40756 as ExcelDate

       , cast(dateadd(d, 40756, '1899-12-30') as date ) as TSQLDate

;

Excel dates are represented by numbers.
Decimals represent time... which is out of the scope of this post.
The trick is knowing that day 1 is for January 1st, 1900 (1900-01-01).

TSQL - Position of the first strange character

select PATINDEX('%[^A-Za-z0-9 ]%' , 'as-df.qwer' );

TSQL - Scientific notation arithmetic warning

select 2E

union all select 3E

union all SELECT 2E-3E --> Doing 2E-3, ingnoring the extra "E".

union all select (2E)-(3E)

;

/*

2

3

0.002

-1

*/

Teradata - How to rename a field or column

ALTER TABLE

DATABASE_NAME_HERE.TABLE_NAME_HERE

RENAME

CURRENT_FIELD_NAME_HERE

TO

NEW_FIELD_NAME_HERE

;

TSQL - Dense rank vs rank vs row number

select

       T1.Field1

       , DENSE_RANK() over( order by Field1 asc ) as DenseRank

       , RANK() over( order by Field1 asc ) as DenseRank

       , ROW_NUMBER() over( order by Field1 asc ) as RowNumber

from

       (

              select 'a' as Field1

              union all select 'a'

              union all select 'a'

              union all select 'b'

              union all select 'b'

              union all select 'c'

              union all select 'd'

       ) as T1

;

SSRS - Data alert grayed out, disabled

SSRS 2012
Sharepoint 2010 Foundation


"If the New Data Alert option is grayed, the report data source is configured to use integrated security credentials or prompt for credentials. To make the New Data Alert option available, you must update the data source to use stored credentials or no credentials."
- http://stackoverflow.com/questions/9680419/data-alerts-disabled-in-sharepoint-2010-installed-in-integrated-mode-with-ssrs

TSQL - Comma formatted number from varchar to float

select distinct

       FIELD_NAME_HERE

       , cast(

              case

                     -- If the entire field is a dash, then turn it to null

                     -- If it is null, then keep it as null

                     when

                           FIELD_NAME_HERE='-'

                           or FIELD_NAME_HERE is null

                     then

                           null

                     -- If the cell has an actual value

                     else

                           -- Comma to nothing

                           replace(

                                  -- Ending parenthesis to nothing

                                  replace(

                                         -- Starting parenthesis with a minus sign

                                         replace(FIELD_NAME_HERE,'(','-')

                                         , ')'

                                         , ''

                                  )

                                  ,','

                                  , ''

                           )

              end

              as float

       ) as FIELD_NAME_HERE_NEW

from

       DATABASE_NAME_HERE.SCHEMA_NAME_HERE.TABLE_NAME_HERE

;

Teradata - String functions sampler

select distinct

      -- Concatenation

      'string 1' || ' string 2' as StringConcatenation

      , '[' || '   asdf   ' || ']' as ConcatenationWithSpaces

      -- Position and length

      , position( '_' in 'hamburgers_hot dogs' ) as PositionFound

      , position( 'a' in 'qwer' ) as PositionNotFound

      , position( 'a' in null ) as PositionInNull

      , lower( 'ABCdef' ) as LowerCase

      , upper( 'ABCdef' ) as AnUpperCase

      , character_length( 'asdf' ) as CharacterLength

      -- Trimming

      , '[' || trim( '   asdf    ') || ']' as DefaultTrim

      , '[' || trim( leading  ' ' from '  asdf') || ']' as TrimLeading

      , '[' || trim( trailing ' ' from 'asdf  ') || ']' as TrimTrailing

      , '[' || trim( both ' ' from '   asdf  ') || ']' as TrimBoth

      , '[' || trim( leading '0' from '0001230') || ']' as TrimLeadingZeroes

      , trim( leading '0' from '001234560' ) as TrimLeadingZeroes2

      , '[' || trim( both '0' from '0001230') || ']' as TrimZeroes

      -- Casting and formatting

      , CAST(123456789.00 AS FORMAT 'G999999999D99') as Casting

      , 123456 ( FORMAT 'G-(10)D9(2)' ) as AFormat

      -- Substring

      , substr( 'asdf', 3, 3 ) as Substring3

      , substr( 'asdf', 1, 3 ) as Left3

      , substring( 'asdfxyz' from CHARACTER_LENGTH( 'asdfxyz' ) -2 FOR 3) as Right3

      , substring( '0123456789ABCDEF' from CHARACTER_LENGTH( '0123456789ABCDEF' ) -11 FOR 12) as Right12

;

Teradata - Fixing the "name requires more than 30 bytes" error

This happened to me while loading data using Multi-Load, but also through other Teradata OleLoad methods (version 13.10).

Make sure you:

1. Are not loading into a table with a name longer than 30 characters.
2. Each of your fields are at or below 30 characters in length.
3. Your fields do not contain spaces or any strange character... only use letters and numbers.

TERADATA - Adding and dropping multiple fields

alter table DATABASE_NAME.TABLE_NAME

add FIELD1_NAME varchar(200) null

, add FIELD2_NAME varchar(200) null

;

  

alter table DATABASE_NAME.TABLE_NAME

drop FIELD1_NAME

,drop FIELD2_NAME

;

TERADATA - Adding and dropping a field

alter table DATABASE_NAME.TABLE_NAME

add FIELD_NAME varchar(200) null

;

alter table DATABASE_NAME.TABLE_NAME

drop FIELD_NAME

;

TSQL - Moving the master database

Moving the master Database (SQL Server 2012)

To move the master database, follow these steps.

1. From the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then click SQL Server Configuration Manager.
2. In the SQL Server Services node, right-click the instance of SQL Server (for example, SQL Server (MSSQLSERVER)) and choose Properties.
3. In the SQL Server (instance_name) Properties dialog box, click the Startup Parameters tab.
4. In the Existing parameters box, select the –d parameter to move the master data file. Click Update to save the change.
   
   In the Specify a startup parameter box, change the parameter to the new path of the master database.
5. In the Existing parameters box, select the –l parameter to move the master log file. Click Update to save the change.
   
   In the Specify a startup parameter box, change the parameter to the new path of the master database.
   The parameter value for the data file must follow the -d parameter and the value for the log file must follow the -l parameter. The following example shows the parameter values for the default location of the master data file.
   -dC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\master.mdf
   -lC:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
   If the planned relocation for the master data file is E:\SQLData, the parameter values would be changed as follows:
   -dE:\SQLData\master.mdf
   -lE:\SQLData\mastlog.ldf
6. Stop the instance of SQL Server by right-clicking the instance name and choosing Stop.
7. Move the master.mdf and mastlog.ldf files to the new location.
8. Restart the instance of SQL Server.
9. Verify the file change for the master database by running the following query.
   
   SELECT name, physical_name AS CurrentLocation, state_desc
   FROM sys.master_files
   WHERE database_id = DB_ID('master');
   GO

Source:
http://msdn.microsoft.com/en-us/library/ms345408.aspx

SharePoint 2010 - Installing on SQL Server 2012

This is not a detailed 101 on how to install it, but on how to troubleshoot a problem during the installation. The error messages I received cited the following two phrases: "System.Security.Cryptography.CryptographicException" and "Keyset does not exist".

The issue got solved by making the folder "C:\Users\All Users\Microsoft\Crypto\RSA\MachineKeys" not read-only, and granting full access to the NETWORK SERVICE account to it.

Reference:
http://www.lazyasscoder.com/Article.aspx?id=73&title=Error%3a+Error%3a+System.Security.Cryptography.CryptographicException%3a+Keyset+does+not+exist+or+Access+is+denied

TSQL - Greatest of two dates using arithmetic

declare @val1 as date;

declare @val2 as date;

set @val1='2011-12-31';

set @val2='2012-06-27';

select

       DATEADD

       (

              d

              ,

              cast(

                     0.5

                     * (

                           (

                                  datediff(DAY,'1990-01-01',@val1)

                                  + datediff(DAY,'1990-01-01',@val2)

                           )

                           + ABS(

                                  datediff(DAY,'1990-01-01',@val1)

                                  - datediff(DAY,'1990-01-01',@val2)

                           )

                     )

                     as int

              )

              , cast('1990-01-01' as date)

       )

;

TSQL - Greatest of two numbers using arithmetic

declare @val1 as float;

declare @val2 as float;

set @val1=3.1;

set @val2=3;

select 0.5 * ((@val1 + @val2) + abs(@val1 - @val2));

TSQL - Lowest of two dates using arithmetic

You could create a UDF for this, and then nest calls to it in order to get the maximum of a collection of 3, 4, 5 or more fields.  Change the minus sign (big bold red below), to a plus sign, in order to retrieve the maximum of the two dates.

declare @val1 as date;

declare @val2 as date;

set @val1='2011-12-31';

set @val2='2012-06-27';

select

       DATEADD

       (

              d

              ,

              cast(

                     0.5

                     * (

                           (

                                  datediff(DAY,'1990-01-01',@val1)

                                  + datediff(DAY,'1990-01-01',@val2)

                           )

                           - ABS(

                                  datediff(DAY,'1990-01-01',@val1)

                                  - datediff(DAY,'1990-01-01',@val2)

                           )

                     )

                     as int

              )

              , cast('1990-01-01' as date)

       )

;

TSQL - How to get the lowest of two values using arithmetic

declare @val1 as float;

declare @val2 as float;

set @val1=3.1;

set @val2=3;

select 0.5 * ((@val1 + @val2) - ABS(@val1 - @val2));