.NET Diary

October 28, 2009

ACID

Filed under: All, SQL — leoullas @ 10:10 am

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.

  • Atomicity states that database modifications must follow an “all or nothing” rule. Each transaction is said to be “atomic.” If one part of the transaction fails, the entire transaction fails. It is critical that the database management system maintain the atomic nature of transactions in spite of any DBMS, operating system or hardware failure.
  • Consistency states that only valid data will be written to the database. If, for some reason, a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules. On the other hand, if a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules.
  • Isolation requires that multiple transactions occurring at the same time not impact each other’s execution. For example, if Joe issues a transaction against a database at the same time that Mary issues a different transaction, both transactions should operate on the database in an isolated manner. The database should either perform Joe’s entire transaction before executing Mary’s or vice-versa. This prevents Joe’s transaction from reading intermediate data produced as a side effect of part of Mary’s transaction that will not eventually be committed to the database. Note that the isolation property does not ensure which transaction will execute first, merely that they will not interfere with each other.
  • Durability ensures that any transaction committed to the database will not be lost. Durability is ensured through the use of database backups and transaction logs that facilitate the restoration of committed transactions in spite of any subsequent software or hardware failures.
Advertisements

January 21, 2009

SQL error : Conversion failed when converting datetime from character string

Filed under: All, SQL — leoullas @ 10:29 am

 

sql-error-conversion-failed-when-converting-datetime-from-character-string

sql-error-conversion-failed-when-converting-datetime-from-character-string

In case of two THEN conditions of the CASE statement reference expressions of different data types. In this case, SQL Server implicitly converts the data type for the entire CASE expression to the data type in the THEN clause that has the highest order of data-type precedence. (See the SQL Server Books Online (BOL) topic “Data Type Precedence.”)

BEFORE:

declare @SortExpression varchar(20)

set @SortExpression =‘OverSpeed’

 

SELECT        Journey_Id,

                     Drv_ID,

                     Journey_Start_Dt AS ‘StartDateTime’,

                     Journey_End_Dt AS ‘EndDateTime’,

                     Journey_Time_Sec AS ‘TotalTime’,

                     Journey_Dest_Miles AS ‘TotalDistance’,

                     Max_Speed_Mph AS ‘MAXSpeed’,

                     Brake_Flg AS ‘AggDriving’,

                     OverSpeed_Flg AS ‘OverSpeed’

FROM         DRIVER_JOURNEY_INFO AS td

WHERE     (Company_Id = 9003) AND (Drv_ID = 2017) AND (Journey_Start_Dt BETWEEN ’08/01/2008′ AND ’09/30/2008′) AND

                      (Journey_End_Dt BETWEEN ’08/01/2008′ AND ’09/30/2008′)

ORDER BY

              CASE

                     WHEN @SortExpression = ‘Journey_Id’ THEN Journey_Id

                     WHEN @SortExpression = ‘StartDateTime’ THEN Journey_Start_Dt

                     WHEN @SortExpression = ‘EndDateTime’ THEN Journey_End_Dt

                     WHEN @SortExpression = ‘TotalTime’ THEN Journey_Time_Sec

                     WHEN @SortExpression = ‘TotalDistance’ THEN Journey_Dest_Miles

                     WHEN @SortExpression = ‘MAXSpeed’ THEN Max_Speed_Mph

                     WHEN @SortExpression = ‘AggDriving’ THEN Brake_Flg

                     WHEN @SortExpression = ‘OverSpeed’ THEN OverSpeed_Flg

              ELSE Journey_Id

              END

DESC

CORRECTED:

 

 

ORDER BY

CASE

       WHEN @SortExpression = ‘Journey_Id’ THEN Journey_Id 

       WHEN @SortExpression = ‘StartDateTime’ THEN Journey_Start_Dt 

       WHEN @SortExpression = ‘EndDateTime’ THEN Journey_End_Dt 

       WHEN @SortExpression = ‘TotalTime’ THEN Journey_Time_Sec 

       WHEN @SortExpression = ‘TotalDistance’ THEN Journey_Dest_Miles                                         WHEN @SortExpression = ‘MAXSpeed’ THEN Max_Speed_Mph

       WHEN @SortExpression = ‘TAII’ THEN (CAST(TAI_Time_Sec AS DECIMAL(18,2))/Journey_Time_Sec)*10

       WHEN @SortExpression = ‘TIP’ THEN (CAST(TI_Time_Sec AS DECIMAL(18,2))/Journey_Time_Sec)*100

       WHEN @SortExpression = THEN Journey_Id

       END 

       DESC,

       CASE WHEN @SortExpression = ‘AggDriving’ THEN Brake_Flg END  DESC,

   CASE WHEN @SortExpression = ‘OverSpeed’ THEN OverSpeed_Flg END  DESC

Syntax error in TextHeader of StoredProcedure

Filed under: All, Javascript, SQL — leoullas @ 8:46 am

Microsoft SQL Server Management Studio get this error when trying to modify script the above stored procedure

    

syntax-error-in-textheader-of-storedprocedure1
syntax-error-in-textheader-of-storedprocedure1

Script failed for StoredProcedure ‘dbo.usp_Edit_User’.  (Microsoft.SqlServer.Smo)

Syntax error in TextHeader of StoredProcedure ‘usp_Edit_User’. (Microsoft.SqlServer.Smo)

 Solve:

You have nested comments before your ALTER PROC, which is not possible in TSQL.  Change your header.

BEFORE:

/*

exec usp_Edit_User @Company_Id=N’9003′, @User_Id=N’WWWWWWWWWWWWWWWWWWWW’,/* @Password=N’dris#12345′,*/ @User_Name=N’cWWWWWW’, @Role_Id =N’1′, @Branch_Id=N’13001′, @Is_Locked=N’True’, @Active_Flg=N’Y’, @Modified_By=N’Test_001′, @UserTimeStamp=N’106060′

*/

CORRECTED:

 

exec usp_Edit_User @Company_Id=N’9003′, @User_Id=N’WWWWWWWWWWWWWWWWWWWW’, @User_Name=N’cWWWWWW’, @Role_Id =N’1′, @Branch_Id=N’13001′,@Is_Locked=N’True’,@Active_Flg=N’Y’, @Modified_By=N’Test_001′, @UserTimeStamp=N’106060′

January 20, 2009

Convert Total time (seconds ) to hh:mi:ss format

Filed under: All, SQL — leoullas @ 11:43 am

Three  options:

i>                    Get timespan in seconds from SP level. Convert it in aspx level

Console.WriteLine(TimeSpan.FromSeconds(70165))

ii>                  Convert it directly in SP level.

declare @i int

set @i = 3683

–set @i = 56769

–set @i = 663719 — more than 100 hours

select replace(str(@i/3600,len(ltrim(@i/3600))+abs(sign(@i/35999)-1)) + ‘:’ + str((@i/60)%60,2) + ‘:’ + str(@i%60,2) ,‘ ‘,‘0’) as ‘raw’

–O/P:   01:01:23

iii>                   Function fordifferent formats

ALTER FUNCTION [dbo].[fn_FormatTotalTime]( @TimeInSecond INT,

@Format_type VARCHAR(25)

)

RETURNS VARCHAR(40) AS

BEGIN

— SET @SecondsToConvert = 31536000

— Declare variables

DECLARE @Hours int

DECLARE @Minutes int

DECLARE @Seconds int

DECLARE @f_hrs VARCHAR(20)

DECLARE @f_mnt VARCHAR(20)

DECLARE @f_sec VARCHAR(20)

DECLARE @Time VARCHAR(40)

— Set the calculations for hour, minute and second

SET @Hours = @TimeInSecond/3600

SET @Minutes = (@TimeInSecond % 3600) / 60

SET @Seconds = @TimeInSecond % 60

SELECT @f_hrs = CASE WHEN LEN(CONVERT(VARCHAR(10),@Hours))= 1

THEN ’00’+ CONVERT(VARCHAR(10),@Hours)

WHEN LEN(CONVERT(VARCHAR(10),@Hours))= 2

THEN ‘0’+ CONVERT(VARCHAR(10),@Hours)

ELSE CONVERT(VARCHAR(10),@Hours)

END,

@f_mnt = CASE WHEN LEN(CONVERT(VARCHAR(2),@Minutes))= 1

THEN ‘0’+ CONVERT(VARCHAR(2),@Minutes)

ELSE CONVERT(VARCHAR(2),@Minutes)

END,

@f_sec = CASE WHEN LEN(CONVERT(VARCHAR(2),@Seconds))= 1

THEN ‘0’+ CONVERT(VARCHAR(10),@Seconds)

ELSE CONVERT(VARCHAR(10),@Seconds)

END

— To show 0 for two digits only

IF(LTRIM(RTRIM(@Format_type)) = ‘HH:MM:SS’ )

BEGIN

SELECT @f_hrs = CASE WHEN LEN(CONVERT(VARCHAR(10),@Hours))= 1

THEN ‘0’+ CONVERT(VARCHAR(10),@Hours)

ELSE CONVERT(VARCHAR(10),@Hours)

END

END

— Display the @Time variable as per format

SELECT @Time = CASE WHEN LTRIM(RTRIM(@Format_type)) = ‘HHH:MM:SS’

THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘:’ +CONVERT(CHAR(2), @f_mnt) + ‘:’ +CONVERT(CHAR(2), @f_sec)

WHEN LTRIM(RTRIM(@Format_type)) = ‘HH:MM:SS’

THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘:’ +CONVERT(CHAR(2), @f_mnt) + ‘:’ +CONVERT(CHAR(2), @f_sec)

WHEN LTRIM(RTRIM(@Format_type)) = ‘HHH:MM’

THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘:’ +CONVERT(CHAR(2), @f_mnt)

WHEN LTRIM(RTRIM(@Format_type)) = ‘hours,min,sec’

THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘ hours ‘ +CONVERT(CHAR(2), @f_mnt) + ‘ min ‘ +CONVERT(CHAR(2), @f_sec) + ‘ sec’

WHEN LTRIM(RTRIM(@Format_type)) = ‘hours,min’

THEN RTRIM(CONVERT(CHAR(10), @f_hrs) ) + ‘ hours ‘ +CONVERT(CHAR(2), @f_mnt) + ‘ min’

END

RETURN(@Time)

END

 

 
 

 

 

Blog at WordPress.com.

%d bloggers like this: