MSSQL Server: Stored Procedure - Beginner

There are some programmers that feel afraid of Stored Procedures (SP), even though they are pretty good in much more complex programming languages, such as object programming. Why afraid? Is it because it is coded in the database? Is it "too" structural? Is it the codes seem too messy in the SQL Query Analyzer or Enterprise Manager?


Stay tune for more updates...

MSSQL Server: Transactions

If you doing updates (add, edit, delete) to several tables at the same time and interdependent among the updates, you are encourage to use "transaction" in order to ensure successful update to all tables.

Some articles in the Internet will teach you to initiate the transcation command from the ASP codes. BUT, that doesn't mean it is a correct method to use. It is working fine in the ideal environment and infrastructure. Look at the example below.

What happen at:

ASP	SQL Server
Initiate begin transaction	Mark the start point in the transaction log to inform the server to expect transaction activities
Add record to Table1	Server updates the Table1
Connection down due to bandwidth "clog"
Delete from Table1 (not successful)	Server still expecting the subsequent query(s) OR commit/rollback command
Commit transaction	Server still expecting further command(s). Worst case, resource locking will be occured.

Uncommit transaction will maintain in the server (more accurate - at the transaction log) as incomplete updates. When you query at the Table1, you will see the updates (add one new record). BUT, this update will not last forever! Once the DBA "flush" the transaction log or restart the server, the record in Table1 will be gone! It is no magic but SQL Server is designed such a way by Microsoft. You will get more knowledge about this if you do some research about its architecture.

The example above doesn't mean Transaction is not good, but unproper use of it will create "disaster" to your application. If you really need to use Transaction, try to write all the queries in the Stored Procedures. ASP only send a single command to the server to execute the stored procedure, so the possibility of lost connection during the updates will be very minimum. In the stored procedures, use global variable @@error to detect the any error during the executions. Look at the example below:

CREATE PROCEDURE dbo.proc_test_transaction
AS
BEGIN TRANSACTION
INSERT INTO TABLE1 (FL1, FL2) VALUES (0,1)
DELETE FROM TABLE2 WHERE FL3 = 0
....
IF @@ERROR > 1 /* if no error, @@ERROR will be 0*/
BEGIN
ROLLBACK TRANSACTION
RETURN /* exit from the procedure immediately*/
END
COMMIT TRANSACTION /* without this command, server will not commit the transaction, unless the server specially configured*/
GO

Learn Data Transformation Services (DTS) in 1 hour

Is it nice if you could learn about MS SQL Server's Data Transformation Services (DTS) in 1 hour? I am not talking about being the expect, but at least you know how to get start and "survive"...

Stay tune for the updates!

Microsoft - Out from the market?

I never thought before and will not revisit my decision to think that Microsoft will be "kick-off" from the IT market soon, whereas other technologies are higher possibilities to face their fate soon, very soon...