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

SQL - access rights

Below is an example of how to set access rights in SQL2005 server. A trigger is created in DBase B which will add/update record in a table of DBase A. Account "developer" has full control of DBase B but only allow to insert/update record in that particular table of DBase A.

Account "developer" is used to create a record in DBase B and the trigger will create related record in DBase A

Steps:
1) Create account "developer" and add it to DBase A. (without set role to db_owner, db_denyreader, db_denywriter, etc)

2) Login SSMS as SA, execute commands as below to grant "developer" to access particular table of DBaseA:

use [DBaseA]
GO
GRANT INSERT ON [dbo].[table_XXX] TO [developer]
GRANT UPDATE ON [dbo].[table_XXX] TO [developer]
GRANT SELECT ON [dbo].[table_XXX] TO [developer]
GO

3) Login SSMS as SA, check access rights by command as below:

USE DBaseA;
EXECUTE AS USER = 'developer';
SELECT *
FROM fn_my_permissions('dbo.table_XXX', 'Object')
ORDER BY subentity_name, permission_name ;
REVERT;
GO

4) Login SSMS as "developer", try to create a record in related table of DBaseB, the trigger will be activated to insert a record in "table_XXX" of DBaseA.

Remark:
**Commands in step-2 will be ignored (and become useless) by SQL Server if role of user is set to "db_denywriter" in step 1.

Reference:
http://www.mssqltips.com/tip.asp?tip=1440

SQL - trigger

[Sample of trigger for "inserted"]

Create trigger MMS_insert_dup on t_mms_mo_mms_send_history for insert
as
begin
declare @TxID bigint
declare @MMSTitle nvarchar(50)
select @TxID=TransactionID,
@MMSTitle = MMSTitle
from inserted
insert XXX..XXX(TransactionID, MMSTitle)
values (@TxID, @MMSTitle)
end

Sample of trigger for "updated" (There is no "Updated" table)
Create trigger MMS_update_dup on t_mms_mo_mms_send_history for update
as
begin
declare @TxID bigint
declare @MMSTitle nvarchar(50)
select @TxID=TransactionID,
@MMSTitle = MMSTitle
from inserted
update XXX..XXX
set
MMSTitle = @MMSTitle
where TransactionID = @TxID
end

SQL - Shrink file

Below is an example of reduce size of log file:

USE AdventureWorks;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks
SET RECOVERY FULL;
GO