Category Archives: SQL Server

Execute store procedure with input and output parameters

By | January 10, 2021

Syntax for Executing store procedure with input and output parameters is as below, Declare @Count int exec StoredProcedureName @inputparameters = input parameter value, @outputParamsOfSP = @rowCount output For example, Lets say you are building store procedure to get details of employee by passing employee Id, and that sp should return employee is permanent or contractual.… Read More »

How to compare Columns of two tables in SQL Server

By | November 6, 2020

Background / Problem Statement – Many times while working on projects, we need to compare columns of two tables in sql server and find out what are matching columns and what are different columns in both the tables. Solution –  lets say we have below two tables, CREATE TABLE dbo.Employee ( EmployeeId INT ,fullName VARCHAR(100)… Read More »

Cannot Find One or More Components. Please Reinstall the Application

By | June 28, 2020

Recently i faced below problem while opening SQL Server Management Studio [SSMS], after trying multiple things finally it started working, Solution – Go to Control panel -> uninstall programs and search for Shell. [In my case its 2015, it may depend on different versions on our system.]   3. Right click and choose to repaire.… Read More »

The Visual Studio component cache is out of date, Please restart Visual Studio – SSMS

By | June 14, 2020

This is not frequent error but yes we may face such error while connecting to ssms. The Visual Studio component cache is out of date, Please restart Visual Studio. Solution, clear temp files & run disk clean up for c drive. make sure you select temporary files. try reconnecting to ssms again, it will connect.

Using group by of two columns and count in sql

By | December 19, 2019

Here is explanation of Using group by of two columns and count in sql as below, lets say we have two table Emp and Dep. Select * from [dbo].[tblDep] id name 1 IT 2 HR 3 Finance Select * from [dbo].[tblEmp] Empid name Gender depId 1002 Jatin male 1 1003 Nirmal male 1 1004 Aditi… Read More »

SQL Server UPDATE JOIN

By | November 28, 2019

UPDATE Table1 SET column1 = T2.column1, column2 = T2.column2 FROM #Table1 T2 JOIN #Table2 T2 ON T1.Column3 = T2.Column3 Lets say we have employee & ProjectTeam table as below respectively, Id Name Address PhoneNumber 11 Ajay Mumbai 887 12 Jatin Pune 356 ProjectId EmpId PhoneNumber P123 11   X234 12   So now we want… Read More »

Find all tables containing column with specified name

By | November 30, 2018

Here is how to find all tables containing column with specified name in sql server select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like ‘%ProductName%’ order by TABLE_NAME

Invalid object name STRING_SPLIT in sql server

By | November 3, 2018

While i was working on one of the upgrade project, we were doing DB upgrade from current DB version to latest version and we faced error like ‘Invalid object name STRING_SPLIT’. Our Query was as below, [we wanted to spilt ] DECLARE @StringToSpilt NVARCHAR(50) = ‘A;B;C;D’; SELECT value FROM STRING_SPLIT(@StringToSpilt, ‘;’); Erro: Invalid object name… Read More »

How to check if a column exists in a SQL Server table

By | June 11, 2018

How to check if a column exists in a SQL Server table, Syntax: IF EXISTS(SELECT 1 FROM sys.columns WHERE Name = N’columnName’ AND Object_ID = Object_ID(N’schemaName.tableName’)) BEGIN — Column is exists in DB END Example: Let’s Check wether address column is present in Employee table or not. IF EXISTS(SELECT 1 FROM sys.columns WHERE Name =… Read More »