[SQL] UPDATE TABLE

SELECT *  FROM [Portal].[dbo].[ProviderUsers]
where Username like '%sptest5%'


Update [Portal].[dbo].[ProviderUsers]
SET AccessFailedCount =0,
    Lockout = 0
Where Username like '%sptest5%'


SELECT *  FROM [Portal].[dbo].[ProviderUsers]
where Username like '%sptest5%'

[SQL] ISNUMERIC CAST REPLACE

    select isnumeric('abc')
    0
    select isnumeric (100)
    1

    select cast(23.495 as int)
    23

    select cast(replace('1234534!!!','!!!','') as int)
    1234534

[SQL] SELECT ISNULL(NULL, 'TEST');

SELECT ISNULL(NULL, 'TEST');
TEST
SELECT ISNULL('HELLO', 'TEST');
HELLO

Return the specified value IF the expression is NULL, otherwise return the expression:

[SQL] Get column name from Table

select * from [DBName].Information_schema.columns
where table_name = 'final_outgoing_323'

[SQL] Having clause with Group By and Where

SELECT DEPARTMENTNAME, SUM(SALARY)
FROM DBO.EMPLOYEE
GROUP BY DEPARTMENTNAME
HAVING SUM(SALARY) > 100000
ORDER BY SUM(SALARY) DESC




SELECT DEPARTMENTNAME, SUM(SALARY)
FROM DBO.EMPLOYEE
WHERE DEPARTMENTNAME ='HR'
GROUP BY DEPARTMENTNAME



OR

SELECT DEPARTMENTNAME, SUM(SALARY)
FROM DBO.EMPLOYEE
GROUP BY DEPARTMENTNAME
HAVING DEPARTMENTNAME='HR'



- WHERE CLAUSE AND HAVING TOGETHERE:

SELECT DEPARTMENTNAME,S UM(SALARY)
FROM DBO.EMPLOYEE
WHERE GENDER ='M'
GROUP BY DEPARTMENTNAME
HAVING SUM(SALARY) > 50000
ORDER BY SUM(SALARY) DESC

[CodedUI] MS batch file example to run Web Performance Test

@echo off


Rem ________________________________________________________________________________________________
Rem
Rem Select Windows Version
Rem
Rem ________________________________________________________________________________________________

rem MS Windows version 10.0.14393
rem set CURRENT_DATE=%date:~10,4%%date:~4,2%%date:~7,2%
rem MS Windows version 10.0.10586

 set CURRENT_DATE=%date:~6,4%%date:~0,2%%date:~3,2%

echo %CURRENT_DATE%

Rem ________________________________________________________________________________________________
Rem
Rem       Define Project Folders, the Exe file and Test result and logfile folders
Rem               TextExeFileProd = WebTestProd.webtest    - check Production sites only
Rem            TestExeFileAll  = WebTestAll.webtest     - check all sites including Test and Staging
Rem 
Rem ________________________________________________________________________________________________

set exeDir=C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE
set ProjDir=C:\TestProject

set TestExeFileProd=%ProjDir%\WebAndLoadTestProject1\WebTestProd.webtest
set TestExeFileAll=%ProjDir%\WebAndLoadTestProject1\WebTestAll.webtest

set TestResultDir=%ProjDir%\TestResults\

set TestLogDir=\WebTest\TestResults\%CURRENT_DATE%\

Echo Creating %TestLogDir%

IF NOT EXIST "%TestLogDir%" (mkdir "%TestLogDir%")

echo Project Folder= %ProjDir%


Rem ________________________________________________________________________________________________
Rem
Rem       Cleanup the subfolders in Test LOG Folder
Rem
Rem ________________________________________________________________________________________________

pushd %TestLogDir%

 FOR /D %%P IN ("*HQ*") DO ECHO DELETING "%%P" & IF EXIST "%%P" RD /S /Q "%%P"

Rem ________________________________________________________________________________________________
Rem
Rem        Cleanup Test Log Folder and delete existing files
Rem 
Rem ________________________________________________________________________________________________

Echo Cleaning up %TestLogDir%

IF EXIST "%TestLogDir%\*" ( DEL /Q "%TestLogDir%\*" )


Rem ________________________________________________________________________________________________
Rem
Rem       Execute Test for Prod and All and Create a TestResult Log file
Rem               TextExeFileProd = WebTestProd.webtest    - check Production sites only
Rem            TestExeFileAll  = WebTestAll.webtest     - check all sites including Test and Staging
Rem ________________________________________________________________________________________________

Echo Executing %TestExeFileProd%

 "%exeDir%"\MSTest.exe /testcontainer:"%TestExeFileProd%" /resultsfile:"%TestLogDir%\testResultsProd.trx" > "%TestLogDir%"\TestResultsProd_%CURRENT_DATE%.log

Echo Executing "%TestExeFileAll%"

 "%exeDir%"\MSTest.exe /testcontainer:"%TestExeFileAll%" /resultsfile:"%TestLogDir%\testResultsAll.trx" > "%TestLogDir%"\TestResultsAll_%CURRENT_DATE%.log


Rem ________________________________________________________________________________________________
Rem
Rem     Move the Test Result file to Test LOG FOLDER
Rem
Rem ________________________________________________________________________________________________

Echo Moving Test Result file to %TestLodDir%

pushd  %TestLogDir%

FOR /R "%TestLogDir%" %%X IN (WebTestProd.webtestResult) do (IF EXIST "%%X" ECHO "%%X" & IF EXIST "%%X" copy "%%X" "%TestLogDir%\")

FOR /R "%TestLogDir%" %%X IN (WebTestAll.webtestResult) do (IF EXIST "%%X" ECHO "%%X" & IF EXIST "%%X" copy "%%X" "%TestLogDir%\")


Rem ________________________________________________________________________________________________
Rem
Rem       Cleanup the subfolders in Test LOG Folder again
Rem
Rem ________________________________________________________________________________________________

pushd %TestLogDir%

 FOR /D %%P IN ("*HQ*") DO ECHO DELETING "%%P" & IF EXIST "%%P" RD /S /Q "%%P"


Rem ________________________________________________________________________________________________
Rem
Rem     Run the powershell script to send an email
Rem
Rem ________________________________________________________________________________________________

Powershell.exe -executionpolicy remotesigned -File  SendEmailWebTestResult.ps1


popd

[DOS] Call more than one command in For Loop

Add '&'

here is an example:

FOR /D %%P IN ("*HQ*") DO ECHO DELETING "%%P" & PAUSE & IF EXIST "%%P" RD /S /Q "%%P"