SQL Server - SET XACT_ABORT

SET XACT_ABORT                                                                                                                                                                     
Usually we use TRY… CATCH block of error handling where in CATCH block transaction is rolled back. But sometimes we simply use BEGIN.. COMMIT transaction without any TRY..CATCH block. In this scenario entire batch is not rolled back.

           BEGIN TRANACTION

                   INSERT 1

                   INSERT 2                  --This statement failed

                   INSERT 3

           COMMIT TRANSACTION

In above  case INSERT 1 and INSERT 3 still executes successfully.

SET XACT_ABORT

• It is ideal for "all or nothing" kind of transactions.
• It takes the whole batch as one transaction. If any of the statement in batch fails, whole batch is rolled back.

          SET XACT_ABORT ON

           BEGIN TRANACTION

                   INSERT 1

                   INSERT 2                  --This statement failed

                   INSERT 3

           COMMIT TRANSACTION

• In this case INSERT 1 and INSERT 3 are also rolled back.

Advantage

• TRY .. CATCH does not catch the schema related errors. It will return an error without hitting CATCH block where you have rolled back opened transaction. So You will end up with an opened transaction.
• XCAT_ABORT rolls back transaction as soon as error occurs. So there will be no open transaction. Open transactions are major cause of performance degradation.

Disadvantage of using with TRY .. CATCH block (For non-schema related errors)

• As CATCH block will not be executed, If you are handling / raising error in CATCH block. It will not be possible.

SQL Server Date Format conversion

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM

SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy – 10/02/2008                  

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

appcmd - to list sites using wildcard mapping

You can use wildcard characters with appcmd using the following syntax:

appcmd list apppool /name:"$=*2007"

This command will list all application pools with a name ending in "2007". Using a variation on the same method, you can find all application pools running under the identify of users in a particular domain.

appcmd list apppool /processModel.userName:"$=MyDomain\*"

This will list all application pools with running with an identity of users in the "MyDomain" domain.

appcmd list site /name:"$=*cdo*"

This will list all sites with a name having "cdo".

Tip: You can find out all of the attributes that can be used with appcmd by querying for help after using an attribute. For example:

appcmd list apppools /name:DefaultAppPool -?

ERROR ( message:-name 
-queueLength 
-autoStart 
-enable32BitAppOnWin64 
-managedRuntimeVersion 
-enableConfigurationOverride 
-managedPipelineMode 
-passAnonymousToken 
-processModel.identityType 
-processModel.userName 
-processModel.password 
-processModel.loadUserProfile 
-processModel.manualGroupMembership 
-processModel.idleTimeout 
-processModel.maxProcesses 
-processModel.shutdownTimeLimit 
-processModel.startupTimeLimit 
-processModel.pingingEnabled 
-processModel.pingInterval 
-processModel.pingResponseTime 
-recycling.disallowOverlappingRotation 
-recycling.disallowRotationOnConfigChange 
-recycling.logEventOnRecycle 
-recycling.periodicRestart.memory 
-recycling.periodicRestart.privateMemory 
-recycling.periodicRestart.requests 
-recycling.periodicRestart.time 
-recycling.periodicRestart.schedule.[value='timespan'].value 
-failure.loadBalancerCapabilities 
-failure.orphanWorkerProcess 
-failure.orphanActionExe 
-failure.orphanActionParams 
-failure.rapidFailProtection 
-failure.rapidFailProtectionInterval 
-failure.rapidFailProtectionMaxCrashes 
-failure.autoShutdownExe 
-failure.autoShutdownParams 
-cpu.limit 
-cpu.action 
-cpu.resetInterval 
-cpu.smpAffinitized 
-cpu.smpProcessorAffinityMask 
-cpu.smpProcessorAffinityMask2 
)

By appending "-?" to the command above, appcmd generates an error that lists all of the attributes that can be used with application pools.

Source : https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/cc771280(v%3dws.10)