Tag: SQL server

Login failed. The login is from an untrusted domain

If you started seeing this message;

One of the reason might be that your Azure AD password has been expired. Try to login here;

https://myapps.microsoft.com/

User Function – Self service password reset

Click on upper right corner on your user icon and select “View Account”. Click on “Change Password” tile. Enter your old password and new password. Logout and Log back in with your new password. Microsoft Authenticator or Google Authenticator is required to authenticate.

If the computers are not joined to domain then user has to open Credential Manager on their computers to store new credentials if they are using windows authentication for any cloud services.

If user has forgotten his/her password, then admin can follow Admin Function and reset user password to a temporary password. User has to go through same steps again to reset the password.

Admin Function – To Reset the password for other User

Click on Admin;

Click on Reset password under user management. Select your user, change password. This is a temporary password. Communicate this password with user and ask them to follow self service password reset.

Get File Extension using T-SQL

The easies way to do this using SQL Server 2017 and up;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	--check there is a '.' in ExcelFileName
	CASE WHEN @FileName LIKE '%.%' THEN
		REVERSE(left(REVERSE(@FileName), CHARINDEX('.', REVERSE(@FileName)) - 1))
	ELSE ''
	END ExcelFileExtension
WHERE 1=1

Earlier versions can use this approach;

DECLARE @FileName NVARCHAR(255) = N'Need to get extension of this file.xlsx';
--PRINT @FileName
SELECT 
	@FileName ExcelFileName,
	CASE 
         WHEN @FileName LIKE '%.%' THEN 
			RIGHT(@FileName, LEN(@FileName) - CHARINDEX('.', @FileName)) 
         ELSE '' 
       END ExcelFileExtension 
WHERE 1=1

Cross Apply and Outer Apply

Microsoft has introduced APPLY operator in SQL Server 2005. It allows joining between two table expressions, for example joining left/outer table expression with right/inner table expression. The way it works is that we have a table-valued expression on the right side and we want this table-valued expression to be evaluated for each row from the left table expression.

An ideal use case would be where we are unable to form any kind a join between two tables. For example a project table and calendar table.

(Left Side Table – Project Table)

ProjectKey           ProjectEndDate

XX-ABC10            2018-10-31

XX-ABC11            2018-11-30

XX-ABD12            2019-01-31

XX-ABC13

(Right Side Table – Calendar Table)

FiscalQuarterStartDate   FiscalQuarterEndDate

2018-10-01                        2018-12-31

2019-01-01                        2019-03-31

Each ProjectEndDate falls between a FiscalQuarter. We need to append a new column in our result set that will be FiscalQuarterEndDate.

Here is the query;

SELECT p.ProjectKey, p.ProjectEndDate, dates.FiscalQuarterEndDate 
FROM Project p
CROSS APPLY
(
	SELECT FiscalQuarterEndDate 
	FROM FiscalCalendar calendar
	WHERE 1=1
	AND p.ProjectEndDate 
	BETWEEN calendar.FiscalQuarterStartDate AND calendar.FiscalQuarterEndDate
) dates
WHERE 1=1

Here is the result;

ProjectKey           ProjectEndDate                 FiscalQuarterEndDate

XX-ABC10            2018-10-31                        2018-12-31

XX-ABC11            2018-11-30                        2018-12-31

XX-ABD12            2019-01-31                        2019-03-31

If we look at the result set, it returns only those rows that matches with the right table expression. Project Number XX-ABC13 is missing. CROSS APPLY is equivalent to an INNER JOIN. To be more precise its like a CROSS JOIN with a correlated sub-query).

If we want to return all rows from the left side then we need to apply OUTER APPLY which is equivalent to a LEFT OUTER JOIN.

Resources

https://www.mssqltips.com/sqlservertip/1958/sql-server-cross-apply-and-outer-apply/

Throw exceptions in SQL and C#

This is how we can throw an exception in C#;

static void CopyObject(SampleClass original)
{
    if (original == null)
    {
        throw new System.ArgumentException("Parameter cannot be null", "original");
    }
}

This is how we can throw an exception in SQL;

BEGIN TRY
	SET NOCOUNT ON;

	SELECT 1/0;

END TRY  
BEGIN CATCH  
    --SELECT   
    --    ERROR_NUMBER() AS ErrorNumber  
    --    ,ERROR_SEVERITY() AS ErrorSeverity  
    --    ,ERROR_STATE() AS ErrorState  
    --    ,ERROR_PROCEDURE() AS ErrorProcedure  
    --    ,ERROR_LINE() AS ErrorLine  
    --    ,ERROR_MESSAGE() AS ErrorMessage;  
    THROW;
END CATCH;

If you don’t want to throw exception, comment “THROW” keyword. This will stop propagating exception to calling method and “catch(SqlException ex)” block will never be able to see it.

Uncomment all other lines. You have to use data reader to get result back and handle exception manually.