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:10080git 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...
Sunday, September 29, 2019
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...
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...
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
(
...
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
...
Saturday, September 28, 2019
SQL important Hierarchy Queries
Common type expression for employee organisation hierarchy
,OrgHierarchyLevel
As
(
SELECT Id, [Name], ParentID, LevelId FROM T_Organisation WHERE Id = @OrgId
UNION All ...
Using JSON in SQL server
Selecting data using JSON in sql server:
SELECT gid, email FROM tempEmails FOR json auto
Selecting Column Values From JSON Format in sql server:
For Example the below is the JSON format:
[{"gid":"0000036285","email":"tetsuya.ono@gmail.com,tetsuya.ono@gmail.com"},{"gid":"0000047244","email":"Miyuki.Yamamoto@gmail.com"}]
select JSON_Value(JSON_F52E2B61-18A1-11d1-B105-00805F49916B,'$.gid') as gid
JSON_Value(JSON_F52E2B61-18A1-11d1-B105-00805F49916B,'$.email') as email
;WITH...
Using SQL query Insert Data From CSV File in to the SQL server database
drop table #test1
CREATE TABLE #test1(
[GID] [nvarchar](MAX) NULL,
[Staff_Name] [nvarchar](MAX) NULL,
[E_mail_Address] [nvarchar](MAX) NULL,
[OrgLevel1] [nvarchar](MAX) NULL,
[OrgLevel2] [nvarchar](MAX) NULL,
[OrgLevel3] [nvarchar](MAX) NULL,
[OrgLevel4] [nvarchar](MAX) NULL,
[OrgLevel5] [nvarchar](MAX) NULL,
[OrgLevel6] [nvarchar](MAX) NULL,
[HeaOrDeputy] [nvarchar](MAX) NULL,
[RoleType] [nvarchar](MAX) NULL,
[OrgLevelId] [nvarchar] (MAX) NULL
)
--BULK INSERT #test1
-- ...
Friday, September 27, 2019
Capitalize only the first letter of each word of each sentence in SQL Server
BEGIN TRAN UPDATE T_MST_Skills SET Name = stuff((select ' '+upper(left(T3.V, 1))+lower(stuff(T3.V, 1, 1, ''))
from (select cast(replace((select Name as '*' for xml path('')), ' ', '') as xml).query('.')) as T1(X)
cross apply T1.X.nodes('text()') as T2(X)
cross apply (select T2.X.value('.', 'varchar(1000)')) as T3(V)
for xml path(''), type
...
Subscribe to:
Posts (Atom)
My Blog List
-
-
-
-
Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)11 years ago
