Thursday, December 20, 2012

MSSQL management

- Log file size can grow very large. So its max size should be set at the beginning.
- If a log file is already too large, need to shrink/trunk it: 1) right click -> properties -> options (set recovery mode to simple), 2) Tasks > Shrink > Database (specify size to shrink to, and do it), 3) right click -> properties -> options (set recovery mode to full).
- Set default memory usage: right click on instance, choose memory.
- Get size of tables. See here for example. Below command will show size of data, index, reserved and free.
  EXEC sp_spaceused [table name]

Tuesday, December 18, 2012

Gordon Moore - An inspiration

Gordon Moore co-funded Intel, is the author of the Moore's law, and with his wife endowed the Gordon and Betty Moore Foundation to support many higher education and research projects, including $3M to the one I'm working for right now.

He is an inspiration. This post is dedicated to him.

Friday, November 30, 2012

Monitor process memory and CPU usage in C#

There are relevant libraries to monitor process memory and CPU usage.

[1] Process Class
[2] How to get CPU usage of processes and threads
[3] Pushing the Limits of Windows: Physical Memory

It's a little confusing with multiple functions on memory usage, but WorkingSet64() is one that can be used.

Multi-threading with C#

Ok, I worked on multi-threaded applications in C# in the past. Now I'm back to do something again.

Some notes:

1. Below 2 are the same for C# 2.0 and after, ThreadStart can be omitted (but can be useful say when you want to start a group of functions):

    1) Thread newThread = new Thread(new ThreadStart(this.checkProcesses)); 
    2) Thread newThread = new Thread(this.checkProcesses); 

2. The new thread by default is not a background process, which means when the GUI exits, it continues to run in the background. If you set it as a background process, then GUI exit will cause it to exit as well (usually this is desired):

    newThread.IsBackground = true; // usually desired.

3. Access of GUI control needs special handling using Invoke() method:

    delegate void SetTextCallback(string a, string b);
    private void setMsg(string a, string b) {
        if (this.textBox1.InvokeRequired) {
            SetTextCallback d = new SetTextCallback(setMsg);
            this.Invoke(d, new object[] { s, b });
        }
        else { this.textBox1.Text = a + b; }
    }

Then call the setMsg method in the thread method: this.setMsg("hello, ", "world");

Ways of using multi-threading:

    // Method 1. basic method.
    Thread newThread = new Thread(this.checkProcesses); 
    newThread.IsBackground = true; 
    newThread.Start();

    // Method 2. use BackgroundWorker. 

References:

[1] Multi-process: http://msdn.microsoft.com/en-us/library/6x4c42hc.aspx
[2] Multiple process, access form control: http://msdn.microsoft.com/en-us/library/ms171728%28v=vs.80%29.aspx
[3] Background worker: http://stackoverflow.com/questions/363377/c-sharp-how-do-i-run-a-simple-bit-of-code-in-a-new-thread

Monday, November 19, 2012

Recover from winlogon failure

Ok, I got this blue screen error when booting my Dell/XP Professional computer after a shutdown with error with message:

STOP:  c000021a {Fatal System Error}
The Windows Logon Process system process terminated unexpectedly with a status of 
0xc0000005 (0x00000000 0x00000000).
THe system has been shut down.

There is no way to log in no matter what I do: not in safe mode, not in previous good configuration, not using the original XP Professional installation disk from Dell.

A good suggestion I received after searching on line is here.

It says my c:\windows\system32\winlogon.exe file is corrupted and I need to replace it with a good one.

Since I was not able to get in the system even with the original Dell installation CD, I tried to use Hiren's BootCD and it worked! I was able to back up my important files on C drive now.

I followed instruction by replacing c:\windows\system32\winlogon.exe with c:\windows\system32\dllcache\winlogon.exe, but it got the same error. I searched my computer and found C:\WINDOWS\SoftwareDistribution\Download\9866fb57abdc0ea2f5d4e132d055ba4e\winlogon.exe, then used this for replacement. It worked and now I can log in!

But I still see nothing after logging in, which means c:\windows\explorer.exe is also corrupted. Again, replace it with c:\windows\system32\dllcache\winlogon.exe did not work, but C:\WINDOWS\SoftwareDistribution\Download\9866fb57abdc0ea2f5d4e132d055ba4e\explorer.exe worked!

So now, one day later, my computer is recovered. I'm glad I didn't have to reinstall it.

Tuesday, November 13, 2012

DOS command

[1] Remove a non-empty folder (see Remove a Directory and All Subdirectories): RMDIR c:\blah /s /q
/s - remove directory when it's not empty.
/q - quiet mode, no prompt for yes/no.

[2] in vbs file, call a dos command, may have error "file not found", adding "CMD /C" before the command will remove the error: objShell.Exec("cmd /C rmdir folder_to_remove /S /Q")

Friday, October 5, 2012

"using" keyword in C#, and large file processing

1. Using

The "using" keywork in C# is used to either import a library, or to cause a local variable to be disposed immediately after use.

To design a class that can be used in the using(...) clause, the class needs to implemented the IDisposable interface. This mostly means to implement the Dispose() and Dispose(boolean) methods, and deallocate local resources in the Dispose(boolean) method. See http://msdn.microsoft.com/en-us/library/system.idisposable.aspx.

2. Processing large data file

Processing of large data file may run out of memory if everything is done inside memory, for example, XmlSerializer may do this. The solution is to do the processing chunk by chunk (e.g., line by line, or block by block if no line separator).

For example, processing a file of 13GB will exhaust almost 16GB memory, causes the machine to hang for 30 minutes and fail. Using line by line processing, it takes 15 minutes and works successfully. Of course, for line by line processing, output can use buffering to avoid too many IO which also can be slow.

Another example is when reading a large file, in C/C++, read by line is much faster than read by char. But, for a binary file, you will not be able to read by line.

So processing large file requires careful handling of memory and IO.

Also, when a file is large, for example the 13GB file which does not contain any new line character (so read by line does not work), it can't be open by any common editor on windows including notepad, wordpad or VS.NET studio; it also can't be open on linux by vi. Well, when use vi to open it, it waits and seems there is never an end to the waiting. Search google shows that vi will have difficulty opening file with more than 9070000 character or file of size 2GB. Also for openning large file under 2GB, it will be faster for vi by disabling swap file, syntax parsing or undo history, see How to open big size file using vi editor or Faster loading of large files.

Use Perl to open the file also waits for ever. Actually using Perl it should also work if read as byte stream. Using C or Java to read as byte stream it works immediately.

Below is Java code to read as a byte stream:

import java.lang.*;
import java.io.*;

public class readByteFile {
  public static void main(String[] args) {
    InputStream is = null;
    ByteArrayOutputStream os = null;

    try  {
      File f = new File("filename");
      byte[] b = new byte[100];      // byte buffer.
      is = new FileInputStream(f);
      os = new ByteArrayOutputStream();

      int read = 0;
      int len = 0;
      while ( (read = is.read(b)) != -1 ) {
        os.write(b, 0, read);
        System.out.print(new String(b));
        len += read;
        if (len > 1000) break;
      }

      System.out.println(new String(b));
    } catch (Exception e) {

    } finally {
      try { if (os != null) os.close(); } catch (IOException e) {}
      try { if (is != null) is.close(); } catch (IOException e) {}
    }
  }
}
Or read in C using fgetc():
#include 

int main() {
  FILE * f = fopen("filename", "r");
  char ch;
  long ct = 0;      // char count.
  long line_ct = 0; // line count.

  if (f != NULL) {
    while (1) {
      ch = fgetc(f);
      ct ++;
      if (ch == '\r' || ch == '\n') line_ct ++;

      if (ch == EOF) break;

      putchar(ch);

      if (ct > 1000) break;
      if (line_ct > 5) break;
    }
  }
  printf("\n");
  fclose(f);
  return 0;
}

Tuesday, September 4, 2012

A Chinese poem processor

A lot of language games can be done. I am also thinking of doing some Natural Language Processing stuff later.
The code below can be improved significantly in the data processing part. For example, on things such as 1) length of poem sentence, 2) position of target character, 3) tone (平仄), 4) semantic analysis so the mood matches.
More poem source files can be added.
The only tricky thing here so far is handling UTF8 characters.
#
# This script reads Chinese poems and store the sentences into a repository,
# then find poem sentences that start with letters in the given target sentence.
#
# This can be used for some language games, 
# such as forming a poem for somebody's birthday in the form of a "藏头诗".
#
# This script should be saved in utf8 format. 
#
# http://ahinea.com/en/tech/perl-unicode-struggle.html
# http://stackoverflow.com/questions/519309/how-do-i-read-utf-8-with-diamond-operator
# http://stackoverflow.com/questions/9574198/comparing-two-unicode-strings-with-perl
# 
# Chinese poems:
# http://www.shuku.net/novels/mulu/shici.html
#
# 藏头诗 generator: http://www.zhelizhao.com/cangtoushi/
#
# By: HomeTom
# Created on: 2012/09/04
#

require Encode;
use utf8;
use strict;

##################
# Change setting here.
##################

# Data source
my @files = ("tang300.txt", "song100.txt");
# Target sentence
my $target = ("小明生日快乐");

##################

my $cnt = 1;
my $len;
my @chars;
my @first = (); # first char.
#my $char;
my $DEBUG1 = 0;
my $DEBUG2 = 0;
#print "hi\n";

#binmode STDIN, ":utf8";
binmode STDOUT, ":utf8";

##################
# Read data.
##################

my @lines = ();
my @lines2;

foreach my $file (@files) {
  open FILE, $file or die $!;
  @lines2 = <FILE>;
  close FILE;
  push(@lines, @lines2);
}

##################
# Process and analyze data.
##################

foreach my $line (@lines) {
  chomp($line);
  $line = trim($line);
  $line = Encode::decode_utf8($line);
  @chars = split //, $line;

  if ($DEBUG1) { 
    print "$cnt: $line\n"; 
    print "$cnt: "; 
    print "$_." foreach (@chars); 
    print "\n"; 
  }

  #if ($chars[0] ne "") 
  {
    push (@first, $chars[0]);
  }

  $cnt ++;
}

if ($DEBUG2) {
  print "$_\n" foreach (@first);
}

$len = @first;


##################
# Search poem sentences for each word in the target sentence.
##################

my @words = split //, $target;

foreach my $w (@words) {
  print "==$w==\n";
  getLetterLines($w);
}


1;


##################
# Subroutines
##################

sub getLetterLines() {
  my $i;
  my $c;
  my $letter = shift;
  for ($i = 0; $i < $len; $i ++) {
    $c = $first[$i];
    if ($c eq $letter) { print "$i: $lines[$i]\n"; }
  }
  #print "\n";
}


#
# Trim functions, from: http://www.somacon.com/p114.php
#

# Perl trim function to remove whitespace from the start and end of the string
sub trim($)
{
  my $string = shift;
  $string =~ s/^\s+//;
  $string =~ s/\s+$//;
  return $string;
}
# Left trim function to remove leading whitespace
sub ltrim($)
{
  my $string = shift;
  $string =~ s/^\s+//;
  return $string;
}
# Right trim function to remove trailing whitespace
sub rtrim($)
{
  my $string = shift;
  $string =~ s/\s+$//;
  return $string;
}

Sunday, August 26, 2012

2001 A Space Odyssey

2001: A Space Odyssey (film).

This is the 1968 sci-fi film made by Stanley Kubrick and Arthur Clarke, famous for its pioneering depiction of technology, artificial intelligence, anthropology and human evolution etc. The supercomputer HAL 9000 is a famous figure of AI, which possess not just strong computational power, but also human-like emotions.

The story is centered around a sentinel rock found on Moon, which sent signal to Jupiter. A mission group is sent from early to investigate the signal destination on Jupiter. Of the crew of 5 humans and HAL, only HAL knew this true purpose. HAL killed 4 crew members in fear that they would shut him down and interfere with the trip mission. One crew survived, shut down HAL, and arrived at the Jupiter.

In my opinion, the 1968 movie is superb in its whimsical sense of sci-fi thoughts. The scenes setup both internally inside spacecraft and externally in the universe are still convincing today. But lack of dialogue and slow pace of storyline may not be liked by some people.

There are 4 episodes of the Space Odyssey series, published as 4 books. The first 2 were made into movies in 1968 (2001 A Space Odyssey) and 1984 (2010 Space Odyssey II). The later two are set in years 2061 and 3001. The 1968 movie is regarded as among the best movies in history. Before these 4 books, Clarke published a short story "The Sentinel" (PDF) in 1951, which he later expanded into the "2001 A Space Odyssey" in 1964, and further rewritten as a movie.

Arthur Clarke's books on the Space Odyssey series can be found here.

Wednesday, August 15, 2012

The magic squares

The matlab7 book I'm reading uses Durer's matrix as an example. This leads to my finding of the wiki page on magic squares. Sleek, or sick?

Another unrelated but similarly magical description on numbers encrypted in architectures: Secrets In Plain Sight - Art, Architecture & Urban Design, and here on youtube.

Saturday, August 11, 2012

Virtual machines

- VMware 
  - VMware server *: Virtualization on windows/linux, free
    - Step-by-Step VMware Server Setup Tutorial 
    - Support ended on 6/30/2011, replaced by:
      - VMware Player *: Free, support 1-2 virtual machines
      - VMware vSphere Hypervisor: Free, support buyable
      - VMware vSphere: 30-day evaluation. For cloud (e.g., >100 virtual servers)
  - VMware Workstation: 60-day evaluation. 
  - VMWare Fusion: Run windows on Mac, free to try, $49.99 to buy.
  - Comparison between VMware Workstation, Player and Server
- Bochs 

- QEMU 
 
- Oracle VirtualBox
 

Monday, August 6, 2012

Problems/Bugs with MSSQL server

There are some noticeable issues with MSSQL Server 2008.

- Cannot call function from remote machine.
- IDE issue: right click on a database, choose "Property -> Files", changes made to the Autogrowth dialog box sometimes cannot be saved. One has to repeat the steps several times before the change can take effect.
- Issues with extended property: 1) not included in replication synchronization process, and 2) no Information_Schema view is provided for extended properties. (from here)
- Also for extended property, I sometimes need to return extended properties of all the columns in a table, the order of returned list is un-deterministic and causes a headache when I want to display the columns in a certain order.

Thursday, August 2, 2012

Interesting blogs

== Good sites on compiler ==
Surely I Am Joking, 当然我在扯淡
Ying Wang @ Github
 
Book: Structure and Interpretation of Computer Programs (SICP), 2nd edition, H. Abelson and G. Sussman with J. Sussman

编译点滴 (Blog of a ICT student in compiler theory)

== Physics ==
The messenger series

== General suggestions ==
IT学生解惑真经

Friday, July 27, 2012

Some vbscript utilities

Here are some vbscript code examples.
--Table of contents--
1. Send email
2. Get computer name
3. Connect to database and execute query
4. Restart a windows service
5. Use of a log file
--

1. Send email

Sub sendEmail(ByRef text)
  Dim objMessage
  Set objMessage = CreateObject("CDO.Message")
  objMessage.Subject = "..."
  objMessage.From = "..."
  objMessage.To = "...@..."
  objMessage.TextBody = "...text..."

  objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing")=2 
  objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "..." 
  objMessage.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") =25 
  objMessage.Configuration.Fields.Update

  objMessage.Send
End Sub

2. Get computer name

Dim objShell, strComputer
Set objShell = CreateObject("WScript.Shell")
strComputer = objShell.ExpandEnvironmentStrings( "%COMPUTERNAME%" )
Set objShell = nothing

3. Connect to database and execute query

Option Explicit

on error resume next
Set objShell = CreateObject("WScript.Shell")

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open "Provider=SQLOLEDB.1;Data Source=localhost;Initial Catalog=<db>","<username>","<password>"

if err.number <> 0 then
    WScript.Echo "error " & hex(err.number) & ": " & err.description 
else
    WScript.Echo "value = " & getVal()
    objConnection.close
end if
on error goto 0

Set objConnection = Nothing

Set objShell = nothing
WScript.Quit(0)

Function getVal()
    Dim sql, strWatchFile
    sql = "select val from table"
    set strWatchFile = objConnection.Execute( sql )
    if err.number <> 0 then
        WScript.Echo hex(err.number) & vbcrlf & err.description 
        getVal = -1
        Exit Function
    else
        getVal = strWatchFile(0).value
        strWatchFile.close
    end if
    set strWatchFile=nothing
End Function

4. Restart a windows service (by calling the batch file below)

objShell.Run "restart.bat ""<service name>""", 0, true

The three parameters are:
  1) string: shell command, a batch file in this case (plus its parameter).
  2) int: 1 - show window, 0 - hide window.
  3) boolean: true - wait until the shell command ends, false - do not wait.

5. Use of a log file

Dim objFileSystem, objLogFile, logFileName, useLog

useLog = True
logFileName = "C:\mylog.log"
Set objShell = CreateObject("WScript.Shell")

openLog()
writeLog("hello world")
closeLog()

Set objShell = nothing
WScript.Quit(0)

Sub openLog()
  If NOT useLog Then Exit Sub
  Const OPEN_FILE_FOR_APPENDING = 8
  Set objFileSystem = CreateObject("Scripting.fileSystemObject")
  If NOT objFileSystem.FileExists(logFileName) Then
    Set objLogFile = objFileSystem.CreateTextFile(logFileName, TRUE)
  Else
    Set objLogFile = objFileSystem.OpenTextFile(logFileName, OPEN_FILE_FOR_APPENDING)
  End If
End Sub

Sub writeLog(txt)
  WScript.Echo txt
  If useLog Then objLogFile.WriteLine(date & " " & time & ": " & txt)
End Sub

Sub closeLog()
  If NOT useLog Then Exit Sub
  objLogFile.Close
  Set objLogFile = Nothing
  Set objFileSystem = Nothing
End Sub

Restart windows service in a batch file

::
:: usage: restart.bat <service name>
:: Note quotation marks can be used if the service name contains space.
::
:: This batch script restarts a windows service (given as a parameter of the batch file).
:: It does this by:
:: 1) issue a stop command;
:: 2) check the status of the service, go back to 1) if it is not stopped yet;
:: 3) when the service has been stopped, restart it.
::
:: Note: the provided service MUST exist, otherwise it will get into an infinite loop.
::
:: References:
:: [1] http://serverfault.com/questions/25081/how-do-i-restart-a-windows-service-from-a-script
:: [2] http://boards.straightdope.com/sdmb/showthread.php?t=458812
:: [3] http://www.robvanderwoude.com/errorlevel.php
::
::

@ECHO OFF

if [%1]==[] goto end

:stop
sc stop %1

rem cause a ~2 seconds sleep before checking the service state
ping 127.0.0.1 -n 2 -w 1000 > nul

sc query %1 | find /I "STATE" | find "STOPPED"

if errorlevel 1 goto :stop
goto :start

:start
sc start %1

:end

Disable Visual Studio JIT debugger

Sometimes when a program gets into an exception or runs into any error, a dialog box will pop up saying "An unhandled exception ('...') occurred in ...exe." This is annoying at run time, say, when this is a windows service and you want it to run, and handle any error by program without human intervention. To disable it, there are two ways:
1) disable in VS.NET: Tools -> Options -> Debugging -> JIT, deselect native/managed/script
2) disable in registry:
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AeDebug\Debugger
     HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\DbgManagedDebugger
   For 64-bit operating system, delete the following registry keys also:
     HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\AeDebug\Debugger
     HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\DbgManagedDebugger
   I did 1). Wait to see if it still happens. If it still happens do 2). This should fix it!
       
See:
[1] http://weblogs.asp.net/fmarguerie/archive/2004/08/27/how-to-turn-off-disable-the-net-jit-debugging-dialog.aspx
[2] http://msdn.microsoft.com/en-us/library/k8kf6y2a%28v=vs.80%29.aspx
[3] http://msdn.microsoft.com/en-us/library/5hs4b7a6%28v=vs.90%29.aspx

Friday, May 25, 2012

More T-SQL

- In T-SQL, string comparison is NOT case-sensitive.
  IF 'asc' = 'ASC' PRINT 'equal'
  ELSE PRINT 'NOT equal'
  -- This will out put 'equal'.

- Construct dynamic query and run it in stored procedure. Example:
 
  DECLARE @cmd varchar(200)
  DECLARE @cond varchar(100) = ' WHERE name=''Mary'''
  SET @cmd = 'SELECT * from Users ' + @cond 
  EXEC (@cmd)

- Add a row number in the returned data set:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users

  Note that if "ORDER BY" is used at the end of the query, it should match the
  "ORDER BY" in the ROW_NUMBER() to be fast and starts from 1. E.g.:

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID DESC

  But the following will run slow, and does not start from 1 (actually list the last 10 rows of the returned list):

  SELECT TOP 10 ROW_NUMBER() OVER (ORDER BY UserID DESC) as RowCount, * FROM Users ORDER BY UserID ASC

- Create a unique value of type uniqueidentifier.
  See: http://msdn.microsoft.com/en-us/library/ms190348.aspx

  -- Creating a local variable with DECLARE/SET syntax.
  DECLARE @myid uniqueidentifier
  SET @myid = NEWID()
  PRINT 'Value of @myid is: '+ CONVERT(varchar(255), @myid)

- Split a string by delimiter ','.
  Reference: http://codebetter.com/raymondlewallen/2005/10/26/quick-t-sql-to-parse-a-delimited-string/    

  DECLARE @loadTable varchar(100) = 'a,b,c,d'
  DECLARE @pos int
  DECLARE @tblResult TABLE (v varchar(10))
  DECLARE @piece varchar(10)

  -- Need to tack a delimiter onto the end of the input string if one doesn’t exist
  IF right(rtrim(@loadTable),1) <> ',' SET @loadTable = @loadTable  + ','

  SET @pos =  patindex('%,%' , @loadTable)
  WHILE @pos <> 0
  BEGIN
    SET @piece = left(@loadTable, @pos - 1)
    INSERT INTO @tblResult (v) VALUES ( @piece ) -- Add to the batch list.
    SET @loadTable = stuff(@loadTable, 1, @pos, '')
    SET @pos =  patindex('%,%' , @loadTable)
  END
    
  select * from @tblResult 

- Temporary table and in-memory table
  1) Temporary table:
  A temporary table is stored in "System Databases\tempdb\Temporary Tables\".

  To declare a table as a local temporary table, use "#" in front of it.
  To declare a table as a global temporary table, use "##" in front of it.
  See Quick Overview: Temporary Tables in SQL Server 2005.

  CREATE TABLE #MyTable1 ( ID int NOT NULL, name varchar(100) )
  INSERT INTO #MyTable1 (ID, name) VALUES (1, 'Mike')
  CREATE INDEX ix_MyTable1ID ON #MyTable1 (ID)
  SELECT * FROM #MyTable1
  DROP TABLE #MyTable1

  The table will be stored as something like "System Databases\tempdb\Temporary Tables\dbo.#MyTable1____________________________________00000002FBEE".

  The table #MyTable1 stays there after the query is done, unless you call the DROP statement.

  Note that if you don't use the "#", then MyTable1 will be created as a physical table in current database.

  2) In comparison, an in-memory table (table variable) can be used this way:

  DECLARE @x TABLE (ID int NOT null, name varchar(100))
  INSERT INTO @x (ID, name) VALUES ('1', 'Mike')
  SELECT * FROM @x

  Note for this one, you can't use index or DROP statement. The table x disappears after the query is done.

- Use cursor
  DECLARE @ID int
  DECLARE @ct int
  DECLARE cs CURSOR FOR SELECT ID FROM table
  OPEN cs

  FETCH NEXT FROM cs INTO @ID
  WHILE @@FETCH_STATUS = 0
  BEGIN
    @ct = @ct + 1 -- keep count of cycles
    PRINT 'cycle: ' + CONVERT(varchar(20), @int)

    FETCH NEXT FROM cs INTO @ID
  END

  CLOSE cs
  DEALLOCATE cs

- ERROR_MESSAGE() - returns error message when @@error != 0

- exception handling in SQL. E.g.:
  BEGIN TRY
    ...
  END TRY

  BEGIN CATCH
    if @@ERROR <> 0 print ERROR_MESSAGE()
  END CATCH

- OUTPUT
  This returns output of a query. The grammar is a little convoluted though. E.g.:

  Declare @sql as nvarchar(512) 
  Declare @params nvarchar(512) = N'@outParam int OUTPUT'
  Declare @out int
  set @sql = 'select @outParam = count(*) from users'
  execute sp_executesql @sql, @params, @outParam = @out OUTPUT   
  print 'out = ' + CONVERT(varchar(100), @out) -- result is in @out

- Get count of rows in each table:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
  INSERT INTO @TableRowCounts ([TableName], [RowCount])
  EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
  SELECT [TableName], [RowCount]
  FROM @TableRowCounts
  ORDER BY [TableName]

- Show space usage:
  DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128),
                              [Rows] VARCHAR(128),
                              [Reserved] VARCHAR(128),
                              [Data] VARCHAR(128),
                              [Index] VARCHAR(128),
                              [Unused] VARCHAR(128)) ;
  INSERT INTO @TableRowCounts ([TableName], [Rows], [Reserved], [Data], [Index], [Unused])
  EXEC sp_MSforeachtable 'EXEC sp_spaceused ''?''';
  SELECT [TableName], [Rows], [Reserved], [Data], [Index], [Unused]
  FROM @TableRowCounts
  ORDER BY [TableName]

- A Visual Explanation of SQL Joins

- Run query on remote linked server, e.g., return a list of databases.
  SELECT * FROM openquery([linked server], 'select name from sys.databases');

Saturday, May 12, 2012

Wednesday, May 2, 2012

Use TOP and DISTINCT together in t-sql

Surprisingly it is difficult to use TOP and DISTINCT together in SQL. This will not work: select top 2 distinct t.ID as tID, t.Name as tName from Table1 order by tID It can be replaced by something like this to work: select top 2 * from (select distinct t.ID as tID, t.Name as tName from Table1 t) y order by tID Also see some other web links, such as Here

Friday, April 20, 2012

Wednesday, April 18, 2012

Debugging experience

Debugging is a hard thing. Here records some of my experiences.

- A same program instance is running on many machines for years without problem. But today one single machine reports an error. A stored procedure that takes multiple parameters, is now getting those parameters in reverse order.
Reason: A query that returns the list of parameters is now returning the entries in reverse. MSSQL returns data set in undeterministic order if "order by" is not used. It has worked consistently for years on all the machines, now for unknown reason is functioning differently on one single machine.
Solution: Add "order by" clause to the query.

- The Database server in production was accidentally uninstalled ... This happened because the admin logged into the wrong server. As the solution, the desktop background is changed to reflect the server name.

"Same Domain Policy" limitation of jQuery

The same domain policy prevents javascripts hosted on one domain from accessing data from a remote domain. This is for security purpose. So when use jQuery, you can not directly access a web service from a remote server.

The solution is to create a serverside script (for example, in php, as shown below) on your domain, which will serve as a proxy, get the data from the remote domain, and then output it. For example:

<?php
readfile('http://remote_server.com/service?param=' . urlencode($_GET['param']);
?>

[1] Getting data from remote URL using jquery
[2] Cross-domain requests with jQuery

Wednesday, March 14, 2012

Drop Schema

DROP SCHEMA (Transact-SQL)

DROP SCHEMA schema_name

To drop a schema, any objects (e.g.: tables) associated with the schema must be dropped first.

Sunday, March 11, 2012

Some articles from CACM

On software engineering: The Risks Of Stopping Too Soon - By David Lorge Parnas
On publication system: Stop the Numbers Game - By David Lorge Parnas

Thursday, February 23, 2012

Optimizing MySQL performance

- Use of Index
- The EXPLAIN keyword - analyze the execution of a query.
- Avoid calculation on a field. e.g., use "rate < 40" instead of "rate / 2 < 20".
- MySQL uses leftmost prefixing, which means that a multi-field index A,B,C will also be used to search not only for a,b,c combinations, but also A,B as well as just A.
- The Query Optimizer, OPTIMIZE and ANALYZE.
ANALYZE TABLE tablename
OPTIMIZE TABLE tablename
- Use short index: index the first few characters of a field. e.g., ALTER TABLE employee ADD INDEX(surname(20),firstname(20));
- Load data in batch, instead of insert 1-by-1:
$db->query("LOAD DATA INFILE 'datafile.txt' INTO TABLE employee (employee_number,firstname,surname,tel_no,salary) FIELDS TERMINATED BY '|'");
LOAD DATA INFILE has defaults of:
FIELDS TERMINATED BY 't' ENCLOSED BY '' ESCAPED BY ''

INSERT LOW PRIORITY - insert only when no read.
INSERT DELAYED - non-block insert, put insert requests on a queue.

- Fast delete: in MySQL 4.0 or later
TRUNCATE TABLE classifieds;
runs faster than
DELETE FROM classifieds;
since "TRUNCATE" deletes all at once, but "DELETE" deletes one by one.
Reference:
[1] databasejournal.com: Optimizing MySQL: Queries and Indexes

Friday, February 17, 2012

Change forgotten postgresql root password

The root of postgresql is called 'postgres'. See details below:

Change forgotten PostgreSQL password

Some common postgresql commands:

- psql -l -- list all databases
- psql -U [user] -W -d testdb -- login using password

In psql shell:

- \l -- list all databases
- \du -- list users
- \c [database] -- switch to a database
- \dt -- list tables in a database
- \d [table] -- describe a table (\d+ does the same thing)
- \dv -- list views in a database

Check syntax: use "EXPLAIN". E.g.: EXPLAIN Select * from Table1

PostgreSQL ODBC driver (psqlODBC): Download here.

By default postgresql allows only local connection. To enable remote connection you need to change 2 configuration files [1]:
1) vi /var/lib/pgsql/data/pg_hba.conf
add this line: host all account_name 100.100.100.100/32 md5
2) vi /var/lib/pgsql/data/postgresql.conf
add this line: listen_addresses = '*'
Then you need to restart the postgresql server, similar to this:
sudo /sbin/service postgresql restart

[1] How Do I Enable remote access to PostgreSQL database server? [2] PostgreSQL SQL Syntax and Use

Tuesday, January 31, 2012

Web RIA technology comparison

Bubblemark animation test: Silverlight (JavaScript and CLR) vs DHTML vs Flash (Flex) vs WPF vs Apollo vs Java (Swing)

Monday, January 30, 2012

Fix UNMOUNTABLE_BOOT_VOLUME problem

Upon startup, a computer running windows XP is dead with a blue screen reporting "UNMOUNTABLE_BOOT_VOLUME" error.

The reason is that the master boot record of the computer is damaged. The fix is to reboot the computer with a XP installation disk. Press "R" to enter recovery console. Then at C:> prompt, enter:

chkdsk c: /r /p
fixmbr
fixboot
exit (to reboot)

References: 1, 2, 3

Blog Archive

Followers