SQL - Encryption / decryption

Today find a web page which provides example about usage of symmetric and asymmetric for SQL encryption:

http://www.mssqltips.com/tip.asp?tip=1886&home

SQL - lock a record for update

Below is an example of how to lock a record for update operation

strCmd = "select serial_num from DBase..Table with (UPDLOCK) where mobile_num = '' order by serial_num asc";
cmd.CommandText = strCmd;
reader = cmd.ExecuteReader();

if (reader.Read() == true)
{
strResult = reader.GetString(0);
}
reader.Close();

// update content of the record
strCmd = "update DBase..Table set mobile_num='" + strMobile + "', create_timestamp='" + strPresTime + "', SID='" + strSID + "' where serial_num='" + strResult + "'";
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();

SQL - insert special character

Today I try to insert string with special characters in SQL dbase, below is a possible approach:

insert DBase..tableXXX (title) values (N'电影原生' + char(39) + N'东邪西毒' + char(39) )

It will result in something like: 电影原生'东邪西毒'

SharePiont and e-Learning

Find a web page which provide valid information about this.

http://elearningtech.blogspot.com/2008/12/using-sharepoint.html

SQL - collation error

Today I try to use Union to join query result of tables of different SQL server (use linked server) and get the following error message

Cannot resolve the collation conflict between "Chinese_PRC_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the UNION operation.

The solution wil be syntax similar as below

WHERE A.Column COLLATE SQL_Latin1_General_CP1_CI_AS = B.Column
(i.e. to override/convert A.column to proper collation)

SQL - linked server

Today try to use SQL2005 SSMS to create a linked server, below is an URL which provides valid information:

http://blog.miniasp.com/post/2008/07/How-to-setup-Linked-Server-in-SQL-Server-2005.aspx

Note:
1. Simply specify IP address in the field of "Server Name"
2. Select "SQL Server" as "Server Type"
3. Remember to enter login information in the page of "Security" setting.

Access linked server by command as below:

select * from [linked server].dbase.schema.table where ...

SQL - injection

Find the following information from Web Site related to SQL injection

 http://support.microsoft.com/kb/954476
 http://msdn.microsoft.com/en-us/library/ms161953(SQL.90).aspx