Git Cheat sheet for all

Git is a version control system for tracking changes in computer files and coordinating work on those files among multiple people. Git is one of the, if the not the, most popular version control systems available. Its used by millions of projects across all languages.

Remembering all those commands to perform common git tasks can be a bit of a nightmare for anyone.

Creating Repositories

# create new repository in current directory
git init

# clone a remote repository
git clone [url]
# for example cloning the entire jquery repo locally
git clone https://github.com/inagasai/nsmodel

Branches and Tags

# List all existing branches with the latest commit comment 
git branch –av

# Switch your HEAD to branch
git checkout [branch]

# Create a new branch based on your current HEAD
git branch [new-branch]

# Create a new tracking branch based on a remote branch
git checkout --track [remote/branch]
# for example track the remote branch named feature-branch-foo
git checkout --track origin/feature-branch-foo

# Delete a local branch
git branch -d [branch]

# Tag the current commit
git tag [tag-name]

Local Changes

# List all new or modified files - showing which are to staged to be commited and which are not 
git status

# View changes between staged files and unstaged changes in files
git diff

# View changes between staged files and the latest committed version
git diff --cached
# only one file add the file name
git diff --cached [file]

# Add all current changes to the next commit
git add [file]

# Remove a file from the next commit
git rm [file]

# Add some changes in < file> to the next commit
# Watch these video's for a demo of the power of git add -p - http://johnkary.net/blog/git-add-p-the-most-powerful-git-feature-youre-not-using-yet/
git add -p [file]

# Commit all local changes in tracked  files
git commit –a
git commit -am "An inline  commit message"

# Commit previously staged changes
git commit
git commit -m "An inline commit message"

# Unstages the file, but preserve its contents

git reset [file]

Commit History

# Show all commits, starting from the latest 
git log 

# Show changes over time for a specific file 
git log -p [file]

# Show who changed each line in a file, when it was changed and the commit id
git blame -c [file]

Update and Publish

# List all remotes 
git remote -v

# Add a new remote at [url] with the given local name
git remote add [localname] [url]

# Download all changes from a remote, but don‘t integrate into them locally
git fetch [remote]

# Download all remote changes and merge them locally
git pull [remote] [branch]

# Publish local changes to a remote 
git push [remote] [branch]

# Delete a branch on the remote 
git branch -dr [remote/branch]

# Publish your tags to a remote
git push --tags

Merge & Rebase

# Merge [branch] into your current HEAD 
git merge [branch]

# Rebase your current HEAD onto [branch]
git rebase [branch]

# Abort a rebase 
git rebase –abort

# Continue a rebase after resolving conflicts 
git rebase –continue

# Use your configured merge tool to solve conflicts 
git mergetool

# Use your editor to manually solve conflicts and (after resolving) mark as resolved 
git add <resolved- file>
git rm <resolved- file>

Undo

# Discard all local changes and start working on the current branch from the last commit
git reset --hard HEAD

# Discard local changes to a specific file 
git checkout HEAD [file]

# Revert a commit by making a new commit which reverses the given [commit]
git revert [commit]

# Reset your current branch to a previous commit and discard all changes since then 
git reset --hard [commit]

# Reset your current branch to a previous commit and preserve all changes as unstaged changes 
git reset [commit]

#  Reset your current branch to a previous commit and preserve staged local changes 
git reset --keep [commit]

Hope this helps for quick reference!!

Quick note: How to get backup history of a database?

Here is how you can get via SQL Query, This works on any SQL Server version

USE database_name
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

Hope this helps!

Quick Note: Android Gradle distributions

Here is the link for list of all available Gradle Distributions with latest one being first.

Quick note: How to download Xcode DMG or XIP file?

You can find the DMGs or XIPs for Xcode and other development tools on https://developer.apple.com/download/more/ (requires Apple ID to login).

You must login to have a valid session before downloading anything below.

*(Newest on top. For each minor version (6.3, 5.1, etc.) only the latest revision is kept in the list.)

Xcode 10

• 10 beta 2

Xcode 9

• 9.4.1
• 9.4
• 9.3.1
• 9.2 (Last version supporting OS X 10.12.6 Sierra)
• 9.1
• 9.0.1

Xcode 8

• 8.3.3
• 8.2.1 (Last version supporting OS X 10.11.5 El Capitan)
• 8.1
• 8.0

Xcode 7

• 7.3.1
• 7.2.1 (Last version supporting OS X 10.10 Yosemite)
• 7.1.1
• 7.0.1

Xcode 6

• 6.4
• 6.3.2
• 6.2 (Last version supporting OS X 10.9.5 Mavericks)
• 6.1.1
• 6.0.1

Even Older Versions (unsupported for iTunes Connect)

• 5.1.1
• 5.0.2
• 4.6.3
• 4.5.2
• 4.4.1
• 4.3.2
• 4.2
• 4.1
• 4.0.2
• 3.2.6
• 3.1.4
• 3.0
• 2.5
• 2.4.1
• 2.3
• 2.2.1
• 1.5
• 1.0

SSIS: Cannot convert between unicode and non-unicode string data types

Best thing to resolve this to add Data Conversion transformations to convert string columns from non-Unicode (DT_STR) to Unicode (DT_WSTR) strings.

You need to do this for all the string columns.

Add a data conversion task between OLEDB and EXCEL Destinations and do string conversations like below for the columns you are using

.NET Core 2.0 will reach End of Life on October 1, 2018

.NET Core 2.0 was released on August 14, 2017. As a non-LTS release, it is supported for 3 months after the next release. .NET Core 2.1 was released on May 30th, 2018.

Upgrade to .NET Core 2.1

The supported upgrade path from .NET Core 2.0 is via .NET Core 2.1. Instructions for upgrading can be found in the following documents:

• Migrate from .NET Core 2.0 to 2.1
• Migrate from ASP.NET Core 2.0 to 2.1

.NET Core 2.1 will be a long-term support release. Its recommend that you make .NET Core 2.1 your new standard for .NET Core development.

'TRUNCATE_ONLY' is not a recognized BACKUP option.

In Microsoft SQL server 2000 and 2005, as part of the ‘BACKUP LOG’ command, there was an option to truncate the log file, without storing a backup to file. In Microsoft SQL Server 2008, this option has been removed, and you can now no longer use the ‘TRUNCATE_ONLY’ option when performing a transaction log backup.

The truncate only option is used to truncate the log file. This is generally done so you can then shrink the transaction log file, and recover the disk space back to the file system.

I ran into this issue, when we are migrating our servers from 2005 to higher version.

Msg 155, Level 15, State 1, Line 1
'TRUNCATE_ONLY' is not a recognized BACKUP option.

To truncate the transaction log file in Microsoft SQL Server 2008 or 2012 and above, without making an actual transaction log backup (possibly due to free space limitations), you need to change the recovery model of the database to “Simple”, and then change it back to “Full” (or “Bulked Logged” if that’s what it was previously).

USE ps;
GO

-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ps
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ps_log, 1);  
-- here 2 is the file ID for trasaction log file
--you can also mention the log file name (dbname_log)
GO

-- Reset the database recovery model.
ALTER DATABASE ps
SET RECOVERY FULL;
GO

Hope this helps.

Quick note: How to find queries, SP's or jobs that use a linked server?

Here is the query below that can pull all above results.

Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
Select name
   From sys.servers
   Where is_linked = 1
      
Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT OBJECT_NAME(object_id) 
      FROM sys.sql_modules 
      WHERE Definition LIKE '%'+@VName +'%' 
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
      
   Fetch next from Findlinked into @VName;
End

Close Findlinked
Deallocate Findlinked

SSIS Jobs are different, Here is one for SSIS jobs with Job Name and Details

Declare @VName varchar(256)
Declare Findlinked cursor
LOCAL STATIC FORWARD_ONLY READ_ONLY
     FOR
Select name AS name
   From sys.servers
   Where is_linked = 1
      
Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT OBJECT_NAME(object_id) as ProcedureName 
      FROM sys.sql_modules 
      WHERE Definition LIKE '%'+@VName +'%' 
      AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1 ;
      
   Fetch next from Findlinked into @VName;
END
Close Findlinked

Open Findlinked;
Fetch next from Findlinked into @VName;

while @@FETCH_STATUS = 0
Begin
   SELECT j.name AS JobName,js.command 
      FROM msdb.dbo.sysjobsteps js
         INNER JOIN msdb.dbo.sysjobs j
            ON j.job_id = js.job_id
      WHERE js.command LIKE '%'+@VName +'%'
   Fetch next from Findlinked into @VName;
END

Close Findlinked
Deallocate Findlinked

Fun with Keyboard Shortcut Keys

1. Ctrl+Alt+Down Arrow. This key command can magically rotate your monitor screens to 180 degrees. It would be very interesting to see someone try to log in to their PC and be surprised by an upside down screen. It would be pretty funny to see someone get dumbfounded by this shortcut. But do keep in mind is that this key only works with computers who have Intel graphics chipsets and may not work on all PCs.
   
2. Left Alt + Left Shift + Print Screen. This amusing shortcut is a great way to both irritate and puzzle your friends; this command turns on the high contrast visibility setting on any computer set.
   
3. Ctrl + F4. You can use this shortcut to sneak-up and wreck havoc on your pals who are working on just about any MS Office program. This shortcut immediately exits the document that is currently running and will most probably upset the victim. So be ready to run away just in case he or she gets violent.
   
4. Windows + M. This command is a bit more advanced than the one above, because aside from shutting your current program, it also closes all your opened programs in a blink of an eye. Again, be ready to run or to defend yourself when executing this prank.
   
5. Windows + D then Alt + F4 then press enter. This command is at the top rung when it comes to instantly closing programs. What does it do? Well, it just closes any version of windows as soon as you enter the shortcut, which makes it a very effective pal irritant.
   
6. Ctrl + H. If you are at your friend’s place and have access to his or her computer, you can use this keyboard shortcut to get easy access to his or her browsing history. Be warned though, that this can reveal way too much information about your friend, so be ready for anything before trying this out.
   
7. Left Alt + Left Shift then enter. This command will make your Num Lock key beep every time it is pressed. It doesn’t do any harm but it can make your friend’s computer seem broken by giving off an odd beep.
   
8. Windows key + “+” then Ctrl + Alt + F. This shortcut makes your screen bigger, which can annoy users who prefer a certain screen size.
   
9. PRT SC, Ctrl+V to make an unclick-able desktop. Press “Print Screen” on your keyboard (PRT SC) to make a screenshot of your desktop. Now go to Paint and Paste using (Ctrl+V). You will now have an image of your desktop which you must then save to your PC (not on your desktop) and save it as BMP. You’ll now have the file like this: C:/Desktop.bmp. Next, right-click on your desktop and select Properties. Select the tab Desktop and click Browse… Navigate to your screenshot and press OK to set it as your background. Right-click your desktop again and navigate to “Arrange icons by” and uncheck “Show desktop icons”. Your icons are now effectively unclickable, and the next user will probably give up in frustration.
   
10. Windows key + “+” then windows key + “-” then Ctrl + Alt + I. This shortcut can turn the colors on your monitor screen upside down; it’s another great prank to make it seem that the computer is busted.

Quicknote: install stunnel as service

stunnel is an open-source multi-platform application used to provide universal TLS/SSL tunneling service. stunnel is used to provide secure encrypted connections for clients or servers that do not speak TLS or SSL natively.

Installing and setting up is pretty standard and configuration is derived form stunnel.config file, Here is how you can setup and configure stunnel in windows

For setting up stunnel service as automatic run this command from command prompt: “stunnel.exe -install  -quiet" and start it.

This will install stunnel as service, open the service and set service as automatic and start service.

How to Configure Stunnel Windows

stunnel is an open-source multi-platform application used to provide universal TLS/SSL tunneling service. stunnel is used to provide secure encrypted connections for clients or servers that do not speak TLS or SSL natively.

stunnel can be used as TLS proxy, I have used when we have issues with TLS 1.0 with one of API End point.

Server administrators and home users alike feel the pressure to secure their Internet communications, but not every application supports using Transport Layer Security (TLS). Recently all OS and all major stopped TLS 1.0 support. So we used Stunel as an fall back approach to TLS 1.0.

Step 1
Install Stunnel. Download the Windows binary file from Stunnel.org. Double-click on the executable "stunnel-4.34-installer.exe" and accept the default values for installing Stunnel on your computer.

Step 2
Copy a valid SSL public certificate to the directory "C:\Program Files (x86)\stunnel." To make things more trouble-free, combine the public key and private key certificates into one .PEM file.

Step 3
Modify the Stunnel configuration file. Open the file "C:\Program Files (x86)\stunnel\stunnel.conf" using a text editor such as Notepad. Modify the file to include the line "cert = C:\Program Files (x86)\stunnel\," where is the name of your certificate file.

Here is the example of my config file

client = yes
[myService1]
accept          = 4010
connect         = www.pld.ups.com:443
sslVersion 	= TLSv1.2

[myService2]
accept          = 4011
connect         = wwwcie.ups.com:443
sslVersion 	= TLSv1.2

Step 4
Configure Stunnel to start automatically. Navigate to the Start menu, "stunnel" folder and click on "Service Install." Stunnel will configure a Windows service called "stunnel" to start automatically when the computer boots.

Determining which version and edition of SQL Server Database Engine is running

Open SQL Server Management Studio (SSMS) and connect to SQL Server. Run below query to find version and edition of SQL server.

SELECT  
    SERVERPROPERTY('productversion') as 'Product Version', 
    SERVERPROPERTY('productlevel') as 'Product Level',  
    SERVERPROPERTY('edition') as 'Product Edition',
    SERVERPROPERTY('buildclrversion') as 'CLR Version',
    SERVERPROPERTY('collation') as 'Default Collation',
    SERVERPROPERTY('instancename') as 'Instance',
    SERVERPROPERTY('lcid') as 'LCID',
    SERVERPROPERTY('servername') as 'Server Name'

Hosts File in Windows 10 : Locate, Edit and Manage

Locating the Hosts file in Windows 10, navigate to C:\Windows\System32\Drivers\etc to find your Windows 10 hosts file. You can see it in the image given below

Editing the Windows 10 Hosts File

Before you can edit this file, you have a pre-requisites that need to be done, Make sure that your account has Administrator privilege as only administrators can modify this file.

Quick note: What does spherical, cylindrical, and axis mean in an eyeglass prescription?

• Spherical (or sphere): Specifies if you’re have either myopia or hyperopia.
  • Prescriptions with myopia (i.e. are nearsighted) will have a MINUS symbol followed by a number (that indicates the amount)
  • Prescriptions with hyperopia (i.e. are farsighted) will have a PLUS symbol followed by a number (which also indicates the amount).
• Cylindrical (or cyl): Ever heard of the word astigmatism? If you have a number in this column, guess what, you have some form of astigmatism.
  • Depending on the doctor you see, you may have a MINUS symbol here (if you see an optometrist) or a PLUS symbol here (if you see an ophthalmologist) followed by a number (that indicates the amount).
• Axis: This number indicates the orientation of where your astigmatism is located. This number will be between 0 to 180 degrees and can change over time.
• Add: This number is for patients that have presbyopia (or focusing/accommodative trouble) and need a bifocal. It provides focusing relieve and will have a PLUS symbol followed by a number (typically between +0.75 and +2.50).

Remote desktop connection authentication error due to “CredSSP encryption oracle remediation” #GDPR

In March, Microsoft released a security update to address vulnerabilities for the Credential Security Support Provider protocol (CredSSP) used by Remote Desktop Protocol (RDP) connections for Windows clients and Windows Server.

Previously, you were able to connect remotely from the updated machine to machines without the update. However, with the latest update released this May, Microsoft hardened security, and you can no longer connect to machines without the update.These might came in due to #GDPR

You will face the CredSSP encryption oracle remediation error if you have applications or services such as the Remote Desktop Connection that use CredSSP on an updated machine. Authentication will not work and you will get this error message:

Here is error message that is seen after recent windows update

The workable solution I found is to edit client Windows’ local group policy (gpedit.msc):

Under Computer Configuration -> Administrative Templates -> System -> Credentials Delegation

Find setting “Encryption Oracle Remediation”. Its default value is “Not configured”. Just change it to “Enabled”, and set “Protection Level” as “Vulnerable”.

Now your remote desktop should be able to connect. Remember to revert the setting after you are done.

.NET Core 2.1 released

Latest .NET Core 2.1 includes improvements to performance, to the runtime and tools. It also includes a new way to deploy tools as NuGet packages. There are many other new APIs, focused on cryptography, compression, and Windows compatibility. It is the first release to support Alpine Linux and ARM32 chips.

With the release of .NET Core 2.1, You can start updating existing projects to target .NET Core 2.1 today. The release is compatible with .NET Core 2.0, making updating easy.
 

Here are features of ASP.NET Core 2.1 and Entity Framework Core 2.1

You can download and get started with .NET Core 2.1, on Windows, macOS, and Linux:

• .NET Core 2.1 SDK (includes the runtime)
• .NET Core 2.1 Runtime

.NET Core 2.1 is supported by Visual Studio 15.7, Visual Studio for Mac and Visual Studio Code.

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error"

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error

or

ole db provider "microsoft.ace.oledb.12.0" for linked server "(null)" returned message "the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.".

Here is the quick resolution for this fix, try run below commands in SQL Server

USE [master] 
GO

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

This resolved my problem!

New partition created after Windows 10 update 1803

A new partition of size 450 MB was created after this Windows 10 1803 recent update.

Resolution:

This is a known issue in the 1803 Update
It is the recovery drive created during the windows 1803 update, we are not meant to see this partition. Since we are seeing and update was successful we can remove the drive letter and everything will return to normal.

1. Click your Start Button, type cmd, then right click Command Prompt and choose 'Run as Administrator'
2. Run this command and hit Enter
3. diskpart
4. Run this command and hit Enter
5. list volume
6. Note down the letter associated with that new drive
7. Run these commands one at a time and hit Enter (replace X with the correct drive letter)
8. select volume X
9. remove letter=X
10. Close Command Prompt and restart your PC

“md-select” - How to set a selected option of a dropdown list control using angular JS?

Try this following code to set selected value based on selected index.

<div layout="row" flex layout-align="start">
	<md-input-container flex>
		<label>{{'TicketType_Label'| translate}}</label>
		<md-select ng-model="feedbackObj.ticketType"  ng-required="true" md-no-asterisk="false" >
			<md-option ng-selected="i == 0 ? true:false"  ng-repeat="(i,type) in TicketTypes" >{{type}}</md-option>
		</md-select>					
	</md-input-container>
</div>

Difference between #temptable and ##TempTable and table variable?

Local Temporary tables

Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash ("#") sign. scope of Local Temporary table is only bounded with the current connection of current user.

CREATE TABLE #TempTable(
 ID int,
 Name varchar(50))

Global Temporary tables

Global Temporary tables name starts with a double hash ("##"). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it.

CREATE TABLE ##GlobalTempTable(
 ID int,
 Name varchar(50))

Table variables in T-SQL

Microsoft introduced table variables with SQL Server 2000 as an alternative to using temporary tables. In many cases a table variable can outperform a solution using a temporary table
Table variables store a set of records, so naturally the declaration syntax looks very similar to a CREATE TABLE statement, as you can see in the following example:

DECLARE @UserTable TABLE
( ID int,
  Name varchar(50))

Unlike the majority of the other data types in SQL Server, you cannot use a table variable as an input or an output parameter. In fact, a table variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exist after the procedure exits - there will be no table to clean up with a DROP statement.

Because of the well-defined scope, a table variable will generally use fewer resources than a temporary table. Transactions touching table variables only last for the duration of the update on the table variable, so there is less locking and logging overhead.
Using a temporary table inside of a stored procedure may result in additional re-compilations of the stored procedure. Table variables can often avoid this recompilation hit.

Configure the remote query timeout Server Configuration Option

Use below query to increase default time out or disable time out time.

USE ps;  
GO  
EXEC sp_configure 'remote query timeout', 0 ;  
GO  
RECONFIGURE ;  
GO 

The remote query timeout option specifies how long, in seconds, a remote operation can take before SQL Server times out. The default value for this option is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine. To disable the time-out, set the value to 0. A query will wait until it completes.

For more information, see Server Configuration Options (SQL Server).

Quick Note: How to Enable USB Debugging on Android Phone

For the newer Android operating system like Android 4.2 or later,
You can enable USB debugging by the following steps:

Step 1. Enter "Settings", then, click "About Phone".
Step 2. Tap "Build number" for 7 times until you get a note "You are under developer mode".
Step 3. Then back to "Setting", then, click "Developer options".
Step 4. The last step, tick "USB debugging"

TLS 1.2 and .NET Support: How to Avoid Connection Errors

I recently ran into an interesting issue when developing we are connecting to a third-party Carrier API. When trying to connect to the API endpoint, I received the following error message:

“An error occurred while making the HTTP request to https://<API endpoint>. This could be due to the fact that the server certificate is not configured properly with HTTP.SYS in the HTTPS case. This could also be caused by a mismatch of the security binding between the client and the server.” Inner exception was “Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.”

With data security in place lot of API providers are ending TLS 1.0 support. This can be overcomed by adding a line of code in .NET by setting TLS 1.1 or above
The default System.Net.ServicePointManager.SecurityProtocol in both .NET 4.0/4.5 is SecurityProtocolType.Tls|SecurityProtocolType.Ssl3.

.NET 4.0 supports up to TLS 1.0 while .NET 4.5 supports up to TLS 1.2

However, an application targeting .NET 4.0 can still support up to TLS 1.2 if .NET 4.5 is installed in the same environment. .NET 4.5 installs on top of .NET 4.0, replacing System.dll

Adding below one line of code will help you to avoid above error.

ServicePointManager.SecurityProtocol = (SecurityProtocolType)192 |
(SecurityProtocolType)768 | (SecurityProtocolType)3072;

Reference:

namespace System.Net
{
    [System.Flags]
    public enum SecurityProtocolType
    {
       Ssl3 = 48,
       Tls = 192,
       Tls11 = 768,
       Tls12 = 3072,
    }
}

SQL Server 2016 Service Pack 2 (SP2) released !!!

The SQL Server 2016 Service Pack 2 (SP2) contains a rollup of released hotfixes as well as multiple improvements centered around performance, scalability, and supportability based on feedback from customers and the SQL community. These improvements enable SQL Server 2016 to perform faster and with expanded supportability and diagnostics. 

SQL Server 2016 Service Pack 2 includes:

• Performance and scale improvements for SQL Server, such as improved database backup performance on large memory machines and added backup compression support, which helps performance of almost all databases.
• Supportability and diagnostics enhancements, such as improved troubleshooting and additional information about statistics used during query plan optimization.

SSMS: Index was outside the bounds of the array. (Microsoft.SqlServer.Smo)

We will see this when we connect to SQL Server 2012 from SQL Server 2008/2008 R2

Cause
This issue occurs because of an error in SSMS 2008 R2.

Solution
The problem is generated due to an error in SQL Server Management Studio 2008 & 2008 R2.

Fix
To solve this, apply the latest service pack available.

Quick Note: How to find out which Service Pack is installed on SQL Server?

-- SQL Server 2000/2005/2008/2008R2/2012
SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

-- SQL Server 6.5/7.0
SELECT @@VERSION

Difference between HTTP.SYS and WAS in IIS

HTTP.SYS (Hypertext Transfer Protocol Stack) is the kernel level components of IIS. All client request comes from client hit the HTTP.Sys of Kernel level. HTTP.SYS then makes a queue for each and every request for each and individual application pool based on the request.

Whenever we create any application pool IIS automatically registers the pool with HTTP.SYS to identify the particular during request processing.

WAS(Windows Activation Service) is a new feature of IIS, that allows all the features of the Windows Communication Framework stack, like non-HTTP channels and other stuff.

IIS is the web server that hosts the services that are activated via WAS.

WAS - is the new process activation mechanism that ships with IIS 7.0. WAS builds on the existing IIS 6.0 but is more powerful because it provides support for other protocols besides HTTP, such as TCP and Named Pipes.

For more details, go through IIS architecture

Duplicate Menu Items in Visual Studio

Recently I had a situation with Visual Studio 2010 where menu items where duplicated like 3-4 times. Looks like some configuration file was corrupted. I have tried to restore current settings and setup new settings but none worked out.

To resolve this I ran the following from command line

devenv.exe /safemode /setup

Once this ran, I restarted Visual Studio I could able to see Visual studio with default factory settings.

PS: If you have any personal settings done before you have to redo those settings again.

Visual Studio 2017 version 15.7 Preview 3

The third preview of the next update Visual Studio 2017 version 15.7 is released and below are top highlights of this Preview.

• Updates to Universal Windows Platform development
• C++ development improvements
• Significant updates in Xamarin and .NET Mobile Development
• Ability to configure installation locations
• Debugger support for authenticated Source Link
• Live Unit Testing improvements
• New tooling for migrating to NuGet PackageReference
• Connected Service improvements to deployment and Key Vault functionality

To acquire the Preview, you can either install it fresh from here, you can update the bits directly from the IDE, or if you have an Azure subscription, you can provision a virtual machine with this latest preview.

for more details

Visual Studio for Mac version 7.4

Visual Studio for Mac version 7.4 is also available. It includes improvements in performance and stability, as well as fixes for many of the top reported issues. This release includes support for macOS High Sierra and C# 7.1, and core architectural changes for C# editing (powered by Roslyn), resulting in improved IntelliSense performance and typing responsiveness.

You can read the complete release notes and access Visual Studio for Mac downloads on VisualStudio.com.

How to get size of all tables in database

Here is how you can get table sizes via SQL query, this works from SQL 2005 and above

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
    CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

Alternatively if you want to get size of one table you can do it by using sp_spaceused, this can get you information on the disk space used by a table, indexed view, or the whole database.

USE psdb  
GO

EXEC sp_spaceused contact
GO

You can use the sp_spaceused command to get all tables in database by using the below command.

USE psdb  
GO

sp_msforeachtable 'EXEC sp_spaceused [?]' 
GO

Hope this useful

How to Get First and Last Day of a Year from SQL

Here is how we get first day and last day of the year.

DECLARE @Year int 
set @Year = 2018
select DATEADD(year,0,DATEADD(year,@Year-1900,0)) 
/*First Day of the year*/
select DATEADD(day,-1,DATEADD(year,1,DATEADD(year,@Year-1900,0))) 
/*Last Day of the year*/

We can also get Year dynamically from SQL by passing current date. This works form SQL 2005 and above.

SELECT Year(getdate())

Paste JSON as Code - quicktype

quicktype infers types from sample JSON data, then outputs strongly typed models and serializers for working with that data in your desired programming language. To use this extension, just copy some JSON and use Edit/Paste JSON as Code.

This extensions works only in VS2017 as of now.

For a more powerful experience, including custom options and the ability to generate code from JSON Schema or multiple JSON samples, try app.quicktype.io.

Download here

How to get the IP Address of a Remote Socket Endpoint

Here is how we can IP address, you need to use below namespace library

using System.Net.Sockets;

IPHostEntry ipHostInfo1 = Dns.GetHostEntry(Dns.GetHostName());
// loop through list of system IP address 
// get the IP4 Address of the current machine
foreach (IPAddress ipaddr in ipHostInfo1.AddressList)
{
    if (ipaddr.ToString() == ConfigurationManager.AppSettings["SystemIPAddress"].ToString())
    {
        strIpAddress = ipaddr.ToString();
        break;
    }

}

                
IPAddress ipAddress = IPAddress.Parse(strIpAddress);

Hope this helps!