Microsoft Excel - Formula to get last 2 digits from number

Watch this example on YouTube



=RIGHT(A1, 2)

Microsoft Excel - Formula to removed specified characters from string

Watch this example on YouTube




To Remove
=SUBSTITUTE(A1, "ABC", "")

To repalce with a space

=SUBSTITUTE(A1, "ABC", " ")

Microsoft Excel - Formula to remove leading 2(or any number) characters

Watch this example on YouTube



=RIGHT(A1, LEN(A1)-2)

Microsoft Excel - Formula to show text until first space

Watch this example on YouTube



=LEFT(A1, FIND(" ", A1, 1)-1)

Microsoft Excel - Formula to get text after first empty space

Watch this example on YouTube



=MID(A1, FIND(" ", A1)+1, 256)

MSSQL - Fix Error - Incorrect syntax near the keyword 'Tran'

Watch this example on YouTube




To fix it replace

ALTER PROCEDURE spTest
    @Name Varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN Tran
        IF @Name is Null
        BEGIN
            Rollback
            Return
        END
        Select * FROM Test1 Where Name = @Name;
    END Tran
   
END
GO

With

ALTER PROCEDURE spTest
    @Name Varchar(10)
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN Tran
        IF @Name is Null
        BEGIN
            Rollback
            Return
        END
        Select * FROM Test1 Where Name = @Name;
    COMMIT Tran
   
END
GO

MSSQL - Fix Error - Operand data type bit is invalid for sum operator.

Watch this example on YouTube





To fix it replace

Select SUM(IsValid) as NumberOfValidRecords from Test1

with

Select SUM(Cast (IsValid) as NumberOfValidRecords from Test1

MSSQL - Fix Error - Argument data type datetime is invalid for argument 2 of dateadd function.

Watch this example on YouTube



1. To Fix it replace
select DATEADD(DAY, GETDATE(), -1)

with

select DATEADD(DAY, -1, GETDATE())

MSSQL - Delete all stored Procedures in table at once in seconds

Watch this example on YouTube








1. To delete all tables in db - in object explorer click desired database and press F7 (or Function Key + F7)<br />
<br />
2. This will open Object Explorer Details - here you can select all tables right click and delete them

MSSQL - Delete all tables in database in seconds

Watch this example on YouTube



1. To delete all tables in db - in object explorer click desired database and press F7 (or Function Key + F7)

2. This will open Object Explorer Details - here you can select all tables right click and delete them

MSSQL - Fix Error - The file MDF is compressed but does not reside in a read-only database or filegroup. The file must be decompressed

Watch solution on YouTube


To fix it
1. Right click the folder where database files are being saved (for instance C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS01\MSSQL\DATA)
2. Go to properties
3. Click Advanced
4. Uncheck "compress contents to save disk space"