Welcome to, WEB ZONE: September 2019

Sunday, September 29, 2019

GIT Commands

Important GIT Commands:

npm config --global set proxy http://yogesh.upreti@gmail.com@23.90.64.10:8080
npm config --global set proxy http://yogesh.upreti@gmail.com:10080

git config --global http.proxy http://yogesh.upreti@gmail.com@proxy.domain.com:10080
git config --global http.proxy http://yogesh.upreti@gmail.com@23.90.64.10:8080

http://dev.azure.com/YOUR_PROJECT_PATH
Step 1: Pull the updated data before commit
git pull

Step 2: Add the changes made by you
git add .

Step 3: Commit the changes with the detailed comment whatever you have done if single file then use "-m"
while if you have made changes in multiple files then use -F
git commit -m "Changes comment"

For multiple comment first create a text file where you want
git commit -F "Changes comment"

To log your commit changes comments. Like this given below
git commit -F "../CommitDetails.txt"

Step 4: Finally push the changes that are made by you using:
git push

Check Status: Like 'Your branch is up to date with 'origin/master'.'
git status

Check log: Checking log of the commit changes
git log

Changing the git remote origin url:

D:\Yogesh\Project\MY_PROJECT_PATH>git remote set-url origin https://dev.azure.com/YOUR_PROJECT_PATH/_git/YOUR_PROJECT_PATH.Web
Check the Remote Origin URL
git remote get-url --all origin

D:\Yogesh\Project\MY_PROJECT_PATH> git remote set-url origin https://dev.azure.com/MY_PROJECT_PATH_API/_git/MY_PROJECT_PATH_API.API

D:\Yogesh\Project\MY_PROJECT_PATH> git remote get-url origin https://dev.azure.com/MY_PROJECT_PATH_API/_git/MY_PROJECT_PATH_API.API
git push

git stash

git reset --hard

Git Clone: Creating clone of project.
git clone -b DEV https://dev.azure.com/MY_PROJECT_PATH_API/_git/MY_PROJECT_PATH_API.API

Using SQL Server Logging Exception in SQL table

Writing logs in SQL Server:

First create a table to log the system exceptions occurred:

CREATE TABLE [dbo].[tblExceptionLog](
[Id] [int] IDENTITY(1, 1) NOT NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](4000) NULL,
[ErrorNumber] [int] NULL,
[ErrorProcedure] [nvarchar](128) NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[DateErrorRaised] [datetime] NULL
)

Create a stored procedure: This procedure will insert the exception log in case of error will come

Create PROC [dbo].[uspGetErrorInfo]
AS
BEGIN
INSERT INTO tblExceptionLog(
ErrorLine,
ErrorMessage,
ErrorNumber,
ErrorProcedure,
ErrorSeverity,
ErrorState,
DateErrorRaised
)
SELECT
ERROR_LINE () AS ErrorLine,
Error_Message() AS ErrorMessage,
Error_Number() AS ErrorNumber,
Error_Procedure() AS 'Proc',
Error_Severity() AS ErrorSeverity,
Error_State() AS ErrorState,
GETDATE () AS DateErrorRaised
END
GO

Use the following approach to log the exception:

BEGIN TRY
BEGIN TRAN
--SQL Statements
COMMIT TRAN
END TRY
BEGIN CATCH
--Print 'Catching Exceptio Here'
IF @@TRANCOUNT > 0
ROLLBACK TRAN

EXEC dbo.uspGetErrorInfo
END CATCH

Using SQL Server list all the tables that are not in used since a long time Unused tables list

Create CTE for the unused tables:

Which are the tables from the sys.all_objects and not in the sys.dm_db_index_usage_stats table.

WITH UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate )
AS (
SELECT DBTable.name AS TableName
,PS.row_count AS TotalRowCount
,DBTable.create_date AS CreatedDate
,DBTable.modify_date AS LastModifiedDate
FROM sys.all_objects DBTable
JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
WHERE DBTable.type ='U'
AND NOT EXISTS (SELECT OBJECT_ID
FROM sys.dm_db_index_usage_stats
WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate
FROM UnUsedTables
ORDER BY TotalRowCount ASC

Using SQL Server Temporarily disabled all check constraints

Temporarily disabled all check constraints:

Classification for disable:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' NOCHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

For enabling again:

DECLARE @sql NVARCHAR(MAX) = N'';

;WITH x AS
(
SELECT DISTINCT obj =
QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
+ QUOTENAME(OBJECT_NAME(parent_object_id))
FROM sys.foreign_keys
)
SELECT @sql += N'ALTER TABLE ' + obj + ' WITH CHECK CHECK CONSTRAINT ALL;
' FROM x;

EXEC sp_executesql @sql;

Using SQL Server Validate Email ID OR Email Address

Different kind of input validation at SQL server level:

Check For Valid Email Id in SQL Server:

Declare @Email NVARCHAR(MAX) = 'yogesh.upreti14@yahoo.co.in'
IF @Email LIKE '%_@__%.__%' AND PATINDEX('%[^a-z,0-9,@,.,_,\-]%', @Email) = 0
BEGIN
----- Execute you Data manipulation operation here.
END

Using SQL Server Remove Duplicate From A Table

Different strategies for removing duplicate records in SQL Server

Delete duplicate rows from a table example using temporary table:
BEGIN TRAN

--Pull distinct records in the temporary table

SELECT DISTINCT * INTO #T_TEMP FROM TableName

--Truncate the target table

TRUNCATE TABLE TableName

--Insert the distinct records from temporary table back to target table

INSERT INTO TableName SELECT * FROM #T_TEMP

--Drop the temporary table

IF OBJECT_ID('tempdb..#T_TEMP') IS NOT NULL
DROP TABLE #T_TEMP
COMMIT TRAN

GO

SELECT * FROM TableName

Delete duplicate rows from a table example using CTE:

WITH cte AS (

SELECT

contact_id,

first_name,

last_name,

email,

ROW_NUMBER() OVER (

PARTITION BY

first_name,

last_name,

ORDER BY

first_name,

last_name,

) row_num

FROM

sales.contacts

)

DELETE FROM cte

WHERE row_num > 1;

Delete duplicate rows from a table Using correlated sub query:

--With correlated subquery

CREATE TABLE Employee

(

[ID] INT IDENTITY,

[FirstName] Varchar(100),

[LastName] Varchar(100),

[Address] Varchar(100),

)