Wednesday, 22 December 2010

Bash: Command line for Linux

An A-Z Index of the Bash command line for Linux.

adduser  Add a user to the system
  addgroup Add a group to the system
  alias    Create an alias •
  apropos  Search Help manual pages (man -k)
  apt-get  Search for and install software packages (Debian/Ubuntu)
  aptitude Search for and install software packages (Debian/Ubuntu)
  aspell   Spell Checker
  awk      Find and Replace text, database sort/validate/index
b
  basename Strip directory and suffix from filenames
  bash     GNU Bourne-Again SHell 
  bc       Arbitrary precision calculator language 
  bg       Send to background
  break    Exit from a loop •
  builtin  Run a shell builtin
  bzip2    Compress or decompress named file(s)
c
  cal      Display a calendar
  case     Conditionally perform a command
  cat      Display the contents of a file
  cd       Change Directory
  cfdisk   Partition table manipulator for Linux
  chgrp    Change group ownership
  chmod    Change access permissions
  chown    Change file owner and group
  chroot   Run a command with a different root directory
  chkconfig System services (runlevel)
  cksum    Print CRC checksum and byte counts
  clear    Clear terminal screen
  cmp      Compare two files
  comm     Compare two sorted files line by line
  command  Run a command - ignoring shell functions •
  continue Resume the next iteration of a loop •
  cp       Copy one or more files to another location
  cron     Daemon to execute scheduled commands
  crontab  Schedule a command to run at a later time
  csplit   Split a file into context-determined pieces
  cut      Divide a file into several parts
d
  date     Display or change the date & time
  dc       Desk Calculator
  dd       Convert and copy a file, write disk headers, boot records
  ddrescue Data recovery tool
  declare  Declare variables and give them attributes •
  df       Display free disk space
  diff     Display the differences between two files
  diff3    Show differences among three files
  dig      DNS lookup
  dir      Briefly list directory contents
  dircolors Colour setup for `ls'
  dirname  Convert a full pathname to just a path
  dirs     Display list of remembered directories
  dmesg    Print kernel & driver messages 
  du       Estimate file space usage
e
  echo     Display message on screen •
  egrep    Search file(s) for lines that match an extended expression
  eject    Eject removable media
  enable   Enable and disable builtin shell commands •
  env      Environment variables
  ethtool  Ethernet card settings
  eval     Evaluate several commands/arguments
  exec     Execute a command
  exit     Exit the shell
  expect   Automate arbitrary applications accessed over a terminal
  expand   Convert tabs to spaces
  export   Set an environment variable
  expr     Evaluate expressions
f
  false    Do nothing, unsuccessfully
  fdformat Low-level format a floppy disk
  fdisk    Partition table manipulator for Linux
  fg       Send job to foreground 
  fgrep    Search file(s) for lines that match a fixed string
  file     Determine file type
  find     Search for files that meet a desired criteria
  fmt      Reformat paragraph text
  fold     Wrap text to fit a specified width.
  for      Expand words, and execute commands
  format   Format disks or tapes
  free     Display memory usage
  fsck     File system consistency check and repair
  ftp      File Transfer Protocol
  function Define Function Macros
  fuser    Identify/kill the process that is accessing a file
g
  gawk     Find and Replace text within file(s)
  getopts  Parse positional parameters
  grep     Search file(s) for lines that match a given pattern
  groups   Print group names a user is in
  gzip     Compress or decompress named file(s)
h
  hash     Remember the full pathname of a name argument
  head     Output the first part of file(s)
  help     Display help for a built-in command •
  history  Command History
  hostname Print or set system name
i
  id       Print user and group id's
  if       Conditionally perform a command
  ifconfig Configure a network interface
  ifdown   Stop a network interface 
  ifup     Start a network interface up
  import   Capture an X server screen and save the image to file
  install  Copy files and set attributes
j
  jobs     List active jobs •
  join     Join lines on a common field
k
  kill     Stop a process from running
  killall  Kill processes by name
l
  less     Display output one screen at a time
  let      Perform arithmetic on shell variables •
  ln       Make links between files
  local    Create variables •
  locate   Find files
  logname  Print current login name
  logout   Exit a login shell •
  look     Display lines beginning with a given string
  lpc      Line printer control program
  lpr      Off line print
  lprint   Print a file
  lprintd  Abort a print job
  lprintq  List the print queue
  lprm     Remove jobs from the print queue
  ls       List information about file(s)
  lsof     List open files
m
  make     Recompile a group of programs
  man      Help manual
  mkdir    Create new folder(s)
  mkfifo   Make FIFOs (named pipes)
  mkisofs  Create an hybrid ISO9660/JOLIET/HFS filesystem
  mknod    Make block or character special files
  more     Display output one screen at a time
  mount    Mount a file system
  mtools   Manipulate MS-DOS files
  mtr      Network diagnostics (traceroute/ping)
  mv       Move or rename files or directories
  mmv      Mass Move and rename (files)
n
  netstat  Networking information
  nice     Set the priority of a command or job
  nl       Number lines and write files
  nohup    Run a command immune to hangups
  notify-send  Send desktop notifications
  nslookup Query Internet name servers interactively
o
  open     Open a file in its default application
  op       Operator access 
p
  passwd   Modify a user password
  paste    Merge lines of files
  pathchk  Check file name portability
  ping     Test a network connection
  pkill    Stop processes from running
  popd     Restore the previous value of the current directory
  pr       Prepare files for printing
  printcap Printer capability database
  printenv Print environment variables
  printf   Format and print data •
  ps       Process status
  pushd    Save and then change the current directory
  pwd      Print Working Directory
q
  quota    Display disk usage and limits
  quotacheck Scan a file system for disk usage
  quotactl Set disk quotas
r
  ram      ram disk device
  rcp      Copy files between two machines
  read     Read a line from standard input •
  readarray Read from stdin into an array variable •
  readonly Mark variables/functions as readonly
  reboot   Reboot the system
  rename   Rename files
  renice   Alter priority of running processes 
  remsync  Synchronize remote files via email
  return   Exit a shell function
  rev      Reverse lines of a file
  rm       Remove files
  rmdir    Remove folder(s)
  rsync    Remote file copy (Synchronize file trees)
s
  screen   Multiplex terminal, run remote shells via ssh
  scp      Secure copy (remote file copy)
  sdiff    Merge two files interactively
  sed      Stream Editor
  select   Accept keyboard input
  seq      Print numeric sequences
  set      Manipulate shell variables and functions
  sftp     Secure File Transfer Program
  shift    Shift positional parameters
  shopt    Shell Options
  shutdown Shutdown or restart linux
  sleep    Delay for a specified time
  slocate  Find files
  sort     Sort text files
  source   Run commands from a file `.'
  split    Split a file into fixed-size pieces
  ssh      Secure Shell client (remote login program)
  strace   Trace system calls and signals
  su       Substitute user identity
  sudo     Execute a command as another user
  sum      Print a checksum for a file
  suspend  Suspend execution of this shell •
  symlink  Make a new name for a file
  sync     Synchronize data on disk with memory
t
  tail     Output the last part of files
  tar      Tape ARchiver
  tee      Redirect output to multiple files
  test     Evaluate a conditional expression
  time     Measure Program running time
  times    User and system times
  touch    Change file timestamps
  top      List processes running on the system
  traceroute Trace Route to Host
  trap     Run a command when a signal is set(bourne)
  tr       Translate, squeeze, and/or delete characters
  true     Do nothing, successfully
  tsort    Topological sort
  tty      Print filename of terminal on stdin
  type     Describe a command •
u
  ulimit   Limit user resources •
  umask    Users file creation mask
  umount   Unmount a device
  unalias  Remove an alias •
  uname    Print system information
  unexpand Convert spaces to tabs
  uniq     Uniquify files
  units    Convert units from one scale to another
  unset    Remove variable or function names
  unshar   Unpack shell archive scripts
  until    Execute commands (until error)
  useradd  Create new user account
  usermod  Modify user account
  users    List users currently logged in
  uuencode Encode a binary file 
  uudecode Decode a file created by uuencode
v
  v        Verbosely list directory contents (`ls -l -b')
  vdir     Verbosely list directory contents (`ls -l -b')
  vi       Text Editor
  vmstat   Report virtual memory statistics
w
  watch    Execute/display a program periodically
  wc       Print byte, word, and line counts
  whereis  Search the user's $path, man pages and source files for a program
  which    Search the user's $path for a program file
  while    Execute commands
  who      Print all usernames currently logged in
  whoami   Print the current user id and name (`id -un')
  Wget     Retrieve web pages or files via HTTP, HTTPS or FTP
  write    Send a message to another user 
x
  xargs    Execute utility, passing constructed argument list(s)
  xdg-open Open a file or URL in the user's preferred application.
  yes      Print a string until interrupted
  .        Run a command script in the current shell
  ###      Comment / Remark
reference: http://ss64.com/bash/

Wednesday, 8 December 2010

LINQ: Examples of LINQ to SQL, SQLMetal

Detailed explanation and example for LINQ and SQLMetal.
SQLMetal is a command line tool that allows you to produce your DAL.
http://www.simple-talk.com/dotnet/.net-tools/exploring-linq,-sqlmetal-and-sqltac/

LINQ to SQL and using it for your DAL
http://www.kirupa.com/net/further_exploration_linq_pg1.htm

Tuesday, 7 December 2010

LINQ: LINQPad, Paste XML as XElements

Handy tools for programming in LINQ.
Has cool function to query SQL databases using LINQ!
http://www.linqpad.net/

Scott Hanselman talks about an extension for VS2008 that allows you to paste XML as XElements directly to your source code.
http://www.hanselman.com/blog/PasteXMLAsXLinqXElementVisualStudioAddIn.aspx

LINQ: CSV to XML using LINQ

Code sample from MS to convert CSV to XML using LINQ.


// Create the text file.
string csvString = @"GREAL,Great Lakes Food Market,Howard Snyder,Marketing Manager,(503) 555-7555,2732 Baker Blvd.,Eugene,OR,97403,USA
HUNGC,Hungry Coyote Import Store,Yoshi Latimer,Sales Representative,(503) 555-6874,City Center Plaza 516 Main St.,Elgin,OR,97827,USA
LAZYK,Lazy K Kountry Store,John Steel,Marketing Manager,(509) 555-7969,12 Orchestra Terrace,Walla Walla,WA,99362,USA
LETSS,Let's Stop N Shop,Jaime Yorres,Owner,(415) 555-5938,87 Polk St. Suite 5,San Francisco,CA,94117,USA";
File.WriteAllText("cust.csv", csvString);

// Read into an array of strings.
string[] source = File.ReadAllLines("cust.csv");
XElement cust = new XElement("Root",
    from str in source
    let fields = str.Split(',')
    select new XElement("Customer",
        new XAttribute("CustomerID", fields[0]),
        new XElement("CompanyName", fields[1]),
        new XElement("ContactName", fields[2]),
        new XElement("ContactTitle", fields[3]),
        new XElement("Phone", fields[4]),
        new XElement("FullAddress",
            new XElement("Address", fields[5]),
            new XElement("City", fields[6]),
            new XElement("Region", fields[7]),
            new XElement("PostalCode", fields[8]),
            new XElement("Country", fields[9])
        )
    )
);
Console.WriteLine(cust);


http://msdn.microsoft.com/en-us/library/bb387090(v=VS.90).aspx

C# : Sockets Development


Really simple easy to understand sockets development code from Microsoft for C#.
using System;
using System.Text;
using System.IO;
using System.Net;
using System.Net.Sockets;

public class GetSocket
{
    private static Socket ConnectSocket(string server, int port)
    {
        Socket s = null;
        IPHostEntry hostEntry = null;

        // Get host related information.
        hostEntry = Dns.GetHostEntry(server);

        // Loop through the AddressList to obtain the supported AddressFamily. This is to avoid
        // an exception that occurs when the host IP Address is not compatible with the address family
        // (typical in the IPv6 case).
        foreach(IPAddress address in hostEntry.AddressList)
        {
            IPEndPoint ipe = new IPEndPoint(address, port);
            Socket tempSocket = 
                new Socket(ipe.AddressFamily, SocketType.Stream, ProtocolType.Tcp);

            tempSocket.Connect(ipe);

            if(tempSocket.Connected)
            {
                s = tempSocket;
                break;
            }
            else
            {
                continue;
            }
        }
        return s;
    }

    // This method requests the home page content for the specified server.
    private static string SocketSendReceive(string server, int port) 
    {
        string request = "GET / HTTP/1.1\r\nHost: " + server + 
            "\r\nConnection: Close\r\n\r\n";
        Byte[] bytesSent = Encoding.ASCII.GetBytes(request);
        Byte[] bytesReceived = new Byte[256];

        // Create a socket connection with the specified server and port.
        Socket s = ConnectSocket(server, port);

        if (s == null)
            return ("Connection failed");

        // Send request to the server.
        s.Send(bytesSent, bytesSent.Length, 0);  

        // Receive the server home page content.
        int bytes = 0;
        string page = "Default HTML page on " + server + ":\r\n";

        // The following will block until te page is transmitted.
        do {
            bytes = s.Receive(bytesReceived, bytesReceived.Length, 0);
            page = page + Encoding.ASCII.GetString(bytesReceived, 0, bytes);
        }
        while (bytes > 0);

        return page;
    }

    public static void Main(string[] args) 
    {
        string host;
        int port = 80;

        if (args.Length == 0)
            // If no server name is passed as argument to this program, 
            // use the current host name as the default.
            host = Dns.GetHostName();
        else
            host = args[0];

        string result = SocketSendReceive(host, port); 
        Console.WriteLine(result);
    }
}
Reference: http://msdn.microsoft.com/en-us/library/system.net.sockets.socket.aspx

Wednesday, 1 December 2010

WSS: Developing custom form, passing and populate field by querystring

Example: Javascript example by Marc D Anderson of populating fields by querystring
http://sympmarc.com/2008/07/11/using-query-string-variables-to-populate-sharepoint-form-fields/


Code: Getting the querystring parameter using XSL
In the DVWP, you can bind a parameter to the querystring:


<ParameterBindings>
<ParameterBinding Name="ListID" Location="QueryString(ListID)" DefaultValue=""/>
ParameterBindings>
Then call the parameter in the xsl stylesheet:

<xsl:param name="ListID" />
Then you can filter the node set by the parameter:

<xsl:variable name="Rows" select="dsQueryResponse/Rows/Row[@ListID=$ListID]"/>

Resource: Understanding SharePoint's ddwrtdatabind
http://www.bryancook.net/2009/09/understanding-sharepoints-ddwrtdatabind.html

Example: Setting form fields using querystring, step by step guide to create a custom form by Mark Rackley
http://www.endusersharepoint.com/2009/11/03/setting-sharepoint-form-fields-using-query-string-variables-without-using-javascript/

Resource: ASP.NET Form Textbox Validation
http://www.asp101.com/lessons/validation.asp

Resource: Custom form Validation
http://www.4guysfromrolla.com/articles/073102-1.aspx

Code: Submit Button, set page/redirecturl after form submission
You can put any URL insde the redirect curly braces without quotes.
http://www.go4answers.com/Example/solution-controlling-redirect-url-65469.aspx

<input name="btnSave" onclick="javascript: {ddwrt:GenFireServerEvent('__commit;__redirect={}')}" type="button" value="Save" />

Tuesday, 23 November 2010

Sysadmin: Using telnet to send emails, emails without clients

Open new cmd window.
Type the following:

telnet hostname portnumber e.g. telnet 10.11.11.11 25
helo
mail from: a@a.com
rcpt to: a@a.com
DATA

quit

Thursday, 11 November 2010

Sysadmin: Run Shortcuts

XP Run Command List:


To Access… Run Command
Accessibility Controls access.cpl
Accessibility Wizard accwiz
Add Hardware Wizard hdwwiz.cpl
Add/Remove Programs appwiz.cpl
Administrative Tools control admintools
Adobe Acrobat (if installed) acrobat
Adobe Designer (if installed) formdesigner
Adobe Distiller (if installed) acrodist
Adobe ImageReady (if installed) imageready
Adobe Photoshop (if installed) photoshop
Automatic Updates wuaucpl.cpl
Bluetooth Transfer Wizard fsquirt
Calculator calc
Certificate Manager certmgr.msc
Character Map charmap
Check Disk Utility chkdsk
Clipboard Viewer clipbrd
Command Prompt cmd
Component Services dcomcnfg
Computer Management compmgmt.msc
Control Panel control
Date and Time Properties timedate.cpl
DDE Shares ddeshare
Device Manager devmgmt.msc
Direct X Control Panel (if installed)* directx.cpl
Direct X Troubleshooter dxdiag
Disk Cleanup Utility cleanmgr
Disk Defragment dfrg.msc
Disk Management diskmgmt.msc
Disk Partition Manager diskpart
Display Properties control desktop
Display Properties desk.cpl
Display Properties (w/Appearance Tab Preselected) control color
Dr. Watson System Troubleshooting Utility drwtsn32
Driver Verifier Utility verifier
Event Viewer eventvwr.msc
Files and Settings Transfer Tool migwiz
File Signature Verification Tool sigverif
Findfast findfast.cpl
Firefox (if installed) firefox
Folders Properties folders
Fonts control fonts
Fonts Folder fonts
Free Cell Card Game freecell
Game Controllers joy.cpl
Group Policy Editor (XP Prof) gpedit.msc
Hearts Card Game mshearts
Help and Support helpctr
HyperTerminal hypertrm
Iexpress Wizard iexpress
Indexing Service ciadv.msc
Internet Connection Wizard icwconn1
Internet Explorer iexplore
Internet Properties inetcpl.cpl
Internet Setup Wizard inetwiz
IP Configuration (Display Connection Configuration) ipconfig /all
IP Configuration (Display DNS Cache Contents) ipconfig /displaydns
IP Configuration (Delete DNS Cache Contents) ipconfig /flushdns
IP Configuration (Release All Connections) ipconfig /release
IP Configuration (Renew All Connections) ipconfig /renew
IP Configuration (Refreshes DHCP & Re-Registers DNS) ipconfig /registerdns
IP Configuration (Display DHCP Class ID) ipconfig /showclassid
IP Configuration (Modifies DHCP Class ID) ipconfig /setclassid
Java Control Panel (if installed) jpicpl32.cpl
Java Control Panel (if installed) javaws
Keyboard Properties control keyboard
Local Security Settings secpol.msc
Local Users and Groups lusrmgr.msc
Logs You Out Of Windows logoff
Malicious Software Removal Tool mrt
Microsoft Access (if installed) msaccess
Microsoft Chat winchat
Microsoft Excel (if installed) excel
Microsoft Frontpage (if installed) frontpg
Microsoft Movie Maker moviemk
Microsoft Paint mspaint
Microsoft Powerpoint (if installed) powerpnt
Microsoft Word (if installed) winword
Microsoft Syncronization Tool mobsync
Minesweeper Game winmine
Mouse Properties control mouse
Mouse Properties main.cpl
Nero (if installed) nero
Netmeeting conf
Network Connections control netconnections
Network Connections ncpa.cpl
Network Setup Wizard netsetup.cpl
Notepad notepad
Nview Desktop Manager (if installed) nvtuicpl.cpl
Object Packager packager
ODBC Data Source Administrator odbccp32.cpl
On Screen Keyboard osk
Opens AC3 Filter (if installed) ac3filter.cpl
Outlook Express msimn
Paint pbrush
Password Properties password.cpl
Performance Monitor perfmon.msc
Performance Monitor perfmon
Phone and Modem Options telephon.cpl
Phone Dialer dialer
Pinball Game pinball
Power Configuration powercfg.cpl
Printers and Faxes control printers
Printers Folder printers
Private Character Editor eudcedit
Quicktime (If Installed) QuickTime.cpl
Quicktime Player (if installed) quicktimeplayer
Real Player (if installed) realplay
Regional Settings intl.cpl
Registry Editor regedit
Registry Editor regedit32
Remote Access Phonebook rasphone
Remote Desktop mstsc
Removable Storage ntmsmgr.msc
Removable Storage Operator Requests ntmsoprq.msc
Resultant Set of Policy (XP Prof) rsop.msc
Scanners and Cameras sticpl.cpl
Scheduled Tasks control schedtasks
Security Center wscui.cpl
Services services.msc
Shared Folders fsmgmt.msc
Shuts Down Windows shutdown
Sounds and Audio mmsys.cpl
Spider Solitare Card Game spider
SQL Client Configuration cliconfg
System Configuration Editor sysedit
System Configuration Utility msconfig
System File Checker Utility (Scan Immediately) sfc /scannow
System File Checker Utility (Scan Once At The Next Boot) sfc /scanonce
System File Checker Utility (Scan On Every Boot) sfc /scanboot
System File Checker Utility (Return Scan Setting To Default) sfc /revert
System File Checker Utility (Purge File Cache) sfc /purgecache
System File Checker Utility (Sets Cache Size to size x) sfc /cachesize=x
System Information msinfo32
System Properties sysdm.cpl
Task Manager taskmgr
TCP Tester tcptest
Telnet Client telnet
Tweak UI (if installed) tweakui
User Account Management nusrmgr.cpl
Utility Manager utilman
Windows Address Book wab
Windows Address Book Import Utility wabmig
Windows Backup Utility (if installed) ntbackup
Windows Explorer explorer
Windows Firewall firewall.cpl
Windows Magnifier magnify
Windows Management Infrastructure wmimgmt.msc
Windows Media Player wmplayer
Windows Messenger msmsgs
Windows Picture Import Wizard (need camera connected) wiaacmgr
Windows System Security Tool syskey
Windows Update Launches wupdmgr
Windows Version (to show which version of windows) winver
Windows XP Tour Wizard tourstart
Wordpad write

Bookmarklets: List of javascript bookmarklets

ASTEROIDS!!!
Play asteroids on any webage.

javascript:var%20s%20=%20document.createElement('script');s.type='text/javascript';document.body.appendChild(s);s.src='http://erkie.github.com/asteroids.min.js';void(0);

SPRITE ME
Generates sprite image from any website.

javascript:(function(){spritemejs=document.createElement('SCRIPT');spritemejs.type='text/javascript';spritemejs.src='http://spriteme.org/spriteme.js';document.getElementsByTagName('head')[0].appendChild(spritemejs);})();

SAML2: Resources

Liberty Alliance Project - SAML2 refresher
http://projectliberty.org/liberty/content/download/4408/29544/file/080828%20LAP%20workshop%20SAML2%20-%20V%20Ake.pdf

SAML2 resource listing
http://www.satyakomatineni.com/akc/display?url=displaynoteimpurl&ownerUserId=satya&reportId=3441

Friday, 22 October 2010

CSS: Constants

$boxstyles='background:#ccc;border-top:1px solid #eee; border-left:1px solid #eee; border-right:1px solid #aaa; border-bottom:1px solid #aaa;padding:.5em;margin:.5em 0';

h1{
$boxstyles
color:#000;
}
div#extras div {
$boxstyles
font-size:80%;
}

/* Company Colours */
$blue='#369';
$green='#363';
$lgreen='#cfc';

[…]

ul#navigation{
background:$blue;
color:#fff;
}
h1{
border-bottom:1px solid $green;
}

Outlook: Saving attachments automatically

Add into outlook rules the following VBScripts:

Sub SaveToFolder(MyMail As MailItem)
Dim strID As String
Dim objNS As Outlook.NameSpace
Dim objMail As Outlook.MailItem
Dim objAtt As Outlook.Attachment
Dim c As Integer
Dim save_name As String
'Place path to sav to on next line. Note that you must include the
'final backslash
Const save_path As String = "X:\SHARE\VCMS CSV\CSV\"

StrID = MyMail.EntryID
Set objNS = Application.GetNamespace("MAPI")
Set objMail = objNS.GetItemFromID(strID)

If objMail.Attachments.Count > 0 Then
For c = 1 To objMail.Attachments.Count
Set objAtt = objMail.Attachments(c)
Save_name = Left(objAtt.FileName, Len(objAtt.FileName) - 4)
'save_name = save_name & Format(objMail.ReceivedTime, "_mm-dd-yyyy_hhmm")
Save_name = save_name & Right(objAtt.FileName, 4)
ObjAtt.SaveAsFile save_path & save_name

Next
End If

Set objAtt = Nothing
Set objMail = Nothing
Set objNS = Nothing
End Sub

Piwik - Web analytics - Open source

Piwik - Web analytics - Open source: "Piwik is a downloadable, open source (GPL licensed) real time web analytics software program. It provides you with detailed reports on your website visitors: the search engines and keywords they used, the language they speak, your popular pages… and so much more.

Piwik aims to be an open source alternative to Google Analytics.

Piwik is a PHP MySQL software program that you download and install on your own webserver. At the end of the five minute installation process you will be given a JavaScript tag. Simply copy and paste this tag on websites you wish to track (or use an existing plugin to do it automatically for you) and access your analytics reports in real time."

Google Analytics: Tracking media and document files

Google Analytics Tracking code - jQuery to index docs, videos, zips, audios

$("a[href*='.pdf'], a[href*='.doc'], a[href*='.xls'], a[href*='.ppt']").click(function() {
pageTracker
._trackPageview('/doc/' + $(this).attr('href'));
});
$
("a[href*='.mov']").click(function() {
pageTracker
._trackPageview('/video/' + $(this).attr('href'));
});
$
("a[href*='.zip']").click(function() {
pageTracker
._trackPageview('/file/' + $(this).attr('href'));
});
$
("a[href*='.mp3']").click(function() {
pageTracker
._trackPageview('/audio/' + $(this).attr('href'));
});

Outlook: Google Calendar Sync

Download Google Calendar Sync and install on your desktop PC where you have outlook running.
After installation you can selection multiple sync options, I choose a one way sync to G-Cal.
On your Iphone goto settings > mail, contacts, calendar > accounts.
Add a new account and select Microsoft Exchange as the account type.

Email will be your gmail address.
Server is m.google.com
Domain is optional so leave it.
Fill out username and password and turn SSL on.

Once you've done this setup sync option on your phone to sync to G-Cal.

Wednesday, 15 September 2010

MSSQL: Best Practices

There are many resources available in the net but here I have a list of Best Practices,Design Guidelines and General Guidelines in Database Development and Designing specifically for SQL Server.

Best Practices

1. Use Stored Procedure: Benefits are as follows :-
(a) Code reusability
(b) Access Control: You can control permission on sp
(c) Execution plan reusability : Though adhoc query also create and reuse plan, the plan is reused only when the query is textual match and the datatypes are matching with the previous call. Any datatype or you have an extra space in the query then new plan is created
Eg.



Select  Sal from Employee where sal=$10  --Money --And Select  Sal from Employee where sal=10  -- Int  
Above statements will create different execution plan because the datatype of value is different.
(d) Prevent SQL Injection
(e) Procedure gives more Readability and Manageability.

2. Use Fully Qualified Name for objects : This is very significant. You must use fully qualified name when you refer any object in SQL Server. Ie. SchemaName.ObjectName. Because, when the execution plan is prepared by the query engine , in the binding process, Query engine has to resolve the Object existence. If you specify the fully qualified name the object resolution become easy for the engine and also it will be more readable.

3. Avoid using Scalar Function in SELECT statement: Recently I faced this issue and I emphasis this point. Never use Scalar function inside a query which returns a large number of rows. Scalar function behave like a cursor when you use Scalar function inside a query which returns large number of rows . Change the scalar function to Inline or Multiline table function or a view.

4. Avoid Mixing-up DML and DDL statement on a temp table inside sp : This is very important. When you Create a temptable (#table) and ALTER the same temptable in the same storedprocedure later, this DDL and DML mix-up causes the stored procedure to get recompiled. So, if a stored procedure is getting recompiled in each call check this point.

5. Select only the required columns: Select only the required column in select statement. Usage of SELECT * can be the cause of NOT using the indexes available on the table . Also if you are selecting more data then you are doing more IO. In short we should limit the IO.

6. Avoid Usage of HINTS : HINTS prevent Query engine automated optimization capability. You may find a hint gives you better performance on a particular scenario. But it may behave differently as the data grows or when scenario changes. Check this KB on HINTS http://msdn.microsoft.com/en-us/library/ms187713.aspx

7. Use Table variable and Temp table as far as possible: You must use Table variable (@TableName) or Temp table (#TableName) for intermediate storage of records in an procedure. Avoid using Table variable for large record set. There are pros and cons between Table variable and Temp table, but in general, if the record set is small you should go for Table variable.

8. Use SET NOCOUNT ON : Basically, you must reduce the data transferred on the network. Database Engine, return the number of rows effected by the statements to the client which is unnecessary and you can avoid that using this statement. It is a must in all Stored procedure.

9. Do not change SET OPTION In connection: Changing SET Option during connection or anywhere else will cause the stored procedure to recompile. Refer this KB for more info http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

10. EXISTS vs IN : IN Operator can easily be replaced by EXISTS which is more optimized for correlated queries. But you may find IN better for small tables.

11. Keep the transaction as short as possible: Deadlock is a outcome of ill-formed query. You must keep the transaction as short as possible to avoid dead lock. And also refer the object in the same order inside transaction.

12. Avoid user input inside a transaction: Do not accept a user input inside a transaction.

13. Avoid doing Front-End work in Databases : String processing , Ordering , Conversion etc can easily be done at the client side and you should avoid doing these kind of work in Database. The point here is, you should have a clear segregation of tasks between Data Layer, Data Access Layer (DAL) and Business Layer. For example, you can easily number your rows in client side where as if you do that in Database you have to use a Function or extra join which can be avoided.

14. Avoid Function in select statement: Any functions like CONVERT(),CAST,ISNULL usage in query may ignore the indexes available on the table.

15. Do not use EXEC (‘String’) , use sp_executeSql : As far as possible you try to avoid Dynamic SQL. If there is no other option use sp_ExecuteSQL DO NOT USE EXEC(‘string’). Because EXEC statement is prone to SQL Injection and it is not parametrized query which can re-use execution plan.

16. Use proper size for the input parameter: This is one of the step to avoid SQL Injection and also the reduce the memory usage.

17. Do not keep the name of sp with sp_ prefix: Naming convention is very important. Do not name the storedprocedures with SP_ as prefix (eg sp_ somespname ) because this naming convention is used for system stored procedure in MS SQL Server.

18. USE WHERE Condition as far as possible: Basically, you should limit the rows fetched by the query.

19. Avoid Negative operator : Avoid using <> , NOT IN, NOT EXISTS kind of operator because it causes a table scan. Query engine has to ensure there is not data till the last row is read.

20. Avoid Cursor /loops: In SET Based operation, in general looping can be avoided.

21. Avoid using Like ‘% %’ : If you use % in both side of the searching value, the query will go for table scan which should be avoided. If the application is more text searching kind go for Full Text Index.

22. Do not use WITH Recompile : Usage of WITH Recompile causes the procedure to recompile each time it call. You must avoid this command.

23. JOIN Consideration : When you JOIN two table consider these points
(a) Avoid using negative operator (<> ,NOT IN) in JOIN
(b) Avoid Like operator in Join


Design Guidelines

1. Create Covering indexes: Create covering indexes. Covering index will have all the data required by the query at the leaf level itself. Covering contains all the columns used in SELECT, WHERE, ORDERBY, JOIN etc.
Eg.


Select Col1,Col2 From YourTableName Where Col3=1 Order by Col4. 
The coveing index for the above mentioned query will be
Col1+ col2+ col3+ col4. (Note : Most selective column should come first in the index creation statement)

2. Remove Unwanted indexes : In SQL Server 2005 it is very easy to find unused indexes. Too many or too less indexes on a table are equally bad. If you have unwanted/unused indexes on a table Insert/Update statement will have performance hit and also we all know indexes consume space.

3. Create the indexes most selective column as first column in Index : Index creation has to be done after proper analysis. You must create the index with Most Selective column at first and so on.

4. Formatting the stored procedure and queries : You must have a format / template for each object (sp/function/views) and everyone (the dev team) should stick to the format defined. And also the query has to be formatted well so that it is more readable.

5. Use Identity column if the table is INSERT oriented table as Clustered Index to avoid page split : This is a design and data modeling issue. If you have more insert kind of table (some kind of logging table) then you must go for Identity Column (ever increasing) as Clustered Index. This helps to resolve page split. There may be Hotspot issue (all transaction contending for same page of a table), but I have never faced.

6. Use proper fillfactor for Indexes: Very important to avoid Page Split. In general transactional table can be kept at 80-90 fillfactor.

7. Balanced Normalization / De-normalization: You must have a trade off between Normalization and de-normalization. At time De-normalization can give you better performance at the cost of Data redundancy.

8. Primary Key size and Composite primary key : You must limit the size of the PK because, in a relational database, you may be creating foreign key which refers this primary key. If you have multiple Column in PK (composite PK) or big size , you are knowingly or unknowingly increasing the space usage. If the composite PK contains more than 3 columns then you may go for surrogate key like Identity column as PK.

9. Do not alter system Objects: If your application requires some tweaking of system objects then you are in trouble. The structure of system object can be changed by Microsoft in any release or patches. So avoid such modeling.


Guidelines for Datatype Selection

As a Database architect I believe in the significance of proper datatype selection while designing the tables. if you do a proper analysis of the data and then select the datatype, then you can control the row, page, table size and hence increase the overall performance of the database. Following points you may consider when you design a table :-

1. If your database is to support web-based application better to go for UNICODE for the scalability of the application. (Unicode (nchar, nvarchar) takes 2 bytes per char where as ASCII (char,varchar) datatypes takes 1 bytes per char)

2. If your application is multi-lingual go for UNICODE.

3. If you are planning to include CLRDatatype (SQL Server 2005) in the database go for UNICODE Datatypes , because, if CLRDatatype is going to consume the data then it must be in UNICODE.

4. For numeric column, find the range that column is going to have and then choose the datatype. For eg. You have a table called Department and DepartmentID is the Primarykey Column. You know that the maximum rows in this table is 20-30. In such cases it is recommended to choose TinyINT datatype for this column. Generally keeping all numeric columns type of INT without analyzing the range that column going to support is not at all recommended from storage perspective.

5. Description /Comments /Remarks sort of columns may or may not have data for all the rows. So it is better to go for Variable datatypes like Varchar ,Nvarchar.

6. If you know the column is not nullable and it may contain more or less the same size of the data then for sure go for Fixed datatype like CHAR or NCHAR. Having said that it is important to know that, if you select fixed datatypes and if the column is nullable then, if you do not have any data (null) then also the column will consume the space.

7. If the size of the column is less than 20 char , use fixed width datatypes like NCHAR or CHAR.

8. I have seen in many applications use Decimal to store currency kind of data though the application needs the precision which can be supported by money. So, my point here is, use Money datatype if you need only 4 precision.

9. Use UniqueIdentitifier column as PK and ClusteredIndex or so only when it is unavoidable because UniqueIdentitifier takes 16 Bytes of the space.



General Guidelines

1. Write ANSI standard Code : You must write standard code which will scale your application. ie migration to next version will not be an issue. Do not use Deprecated features. For eg. There are DBCC Command to rebuild index but in SQL Server 2005 in order to standardize things, you have ALTER INDEX command which does the same thing.

2. Do not give Error message which expose your architecture to the frontend: I have seen folks giving very detailed error message which tells you “ blah blah table do not have this rows in blah blah database” kind which can be a invitation to the hacker

3. Use proper Isolation level required for the application: This is very significant. Before going for any isolation level, you must know the implication. All application cannot afford READUNCOMMITTED Isolation level since it can cause data inconsistency issues like Dirty Read, Phantom read, Lost Update etc. WITH NOLOCK Hint is nothing but READ UNCOMMITTED isolation level.

4. Keep the Database Object Scripts in Source Control: We all are aware of this point but generally ignore. This is important for fault tolerance and management when multiple developers are working on same project.



Links:


QL Server 2008 Videos
http://msdn.microsoft.com/en-us/library/cc952928.aspx

SQL Server 2008 Virtual Labs
http://www.microsoft.com/events/series/technetsqlserver2008.aspx?tab=virtuallabs

SQL Server 2008 Podcasts
http://www.microsoft.com/events/series/technetsqlserver2008.aspx?tab=podcasts

24 Hours of SQL Server 2008
http://www.microsoft.com/events/series/technetsqlserver2008.aspx?tab=webcasts&id=42585

SQL Server Books On Line
SQL 2008 http://msdn2.microsoft.com/en-us/library/bb543165(sql.100).aspx
SQL 2005 http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
SQL 2000 http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b&displaylang=en

SQL Server 2005 Express Edition Video Learning
http://msdn.microsoft.com/hi-in/vstudio/aa718391(en-us).aspx

SQL Server 2005 Management Studio (SSMS) Tutorial
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqltut9/html/31f9e6dc-e946-4576-80bb-729f2bee7478.htm
(This Tutorial requires that Books Online be installed on the computer.)

SQL Server Stored Procedure Tutorial
http://www.mssqltips.com/tutorial.asp?id=160

Tutorials INCLUDED with Books Online
http://msdn2.microsoft.com/en-us/library/ms169620.aspx

Writing Transact-SQL Statements Tutorial
http://msdn2.microsoft.com/en-us/library/ms365303.aspx

Data -.NET Tutorials for SQL Data
http://dotnetjunkies.com/QuickStartv20/howto/doc/adoplus/xmlfromsqlsrv.aspx

Indexes –Tuning
http://www.exforsys.com/tutorials/sql-server-2005/sql-server-database-tuning.html

SourceSafe Tutorial
http://msdn2.microsoft.com/en-us/library/ms167593.aspx

Cluster –Instructional Video
http://www.jumpstarttv.com/Media.aspx?vid=33

Tutorial to format a datetime value or column into a specific date format
http://www.sql-server-helper.com/tips/date-formats.aspx

SQL Server - Free Video Tutorials
http://wikivid.com/index.php/SQL_Server



Reference: 


http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=Best%20practices%20,%20Design%20and%20Development%20guidelines%20for%20Microsoft%20SQL%20Server&referringTitle=Home