D365 Visual studio (VS) missing Dynamics 365 FO extension

 This blog is to show how to install Dynamics 365 FO extension.

Recently we has an issue in one of the dev boxes, where the Dynamics 365 FO extension was missing, and here is what we have done to resolve the issue.

we can find the Dynamics 365 FO installer in the path 

K:\DeployablePackages\8eadd66c-f069-43f7-ae9f-8834c1a75cde\DevToolsService\Scripts

Run the installer after closing VS if open. 

This will install the Dynamics 365 FO extension. 

Structured Query language (SQL)

 

1. Create database	create database sample2
2. Use the database	use sample2
3. Create table	create table customer ( customerid int identity(1,1) primary key, customernumber int not null unique check (customernumber>0), lastname varchar(30) not null, firstname varchar(30) not null, areacode int default 71000, address varchar(50), country varchar(50) default 'Malaysia' )
4. Insert values into table	insert into customer values (100,'Fang Ying','Sham','418999','sdadasfdfd',default), (200,'Mei Mei','Tan',default,'adssdsadsd','Thailand'), (300,'Albert','John',default,'dfdsfsdf',default)
5. Display record from table	-- display all records select * from customer -- display particular columns select customerid, customernumber, lastname, firstname from customer
6. Add new column to table	alter table customer add phonenumber varchar(20)
7. Add values to newly added column/ Update table	update customer set phonenumber='1234545346' where customerid=1 update customer set phonenumber='45554654' where customerid=2
8. Delete a column	alter table customer drop column phonenumber
9. Delete record from table --if not put ‘where’, will delete all record	delete from customer where country='Thailand'
10. Delete table	drop table customer
11. Change data type	alter table customer alter column phonenumber varchar(10)
12. Check what table inside	select * from information_schema.tables
13. View specific row	--top: show only the first two select top 2 * from customer --top 40 percent: also means show the first two select top 40 percent * from customer
14. View specific column	--sort result (by default is ascending) select customerfirstname, customerlastname from customer order by customerlastname desc select customerfirstname, customerlastname from customer order by 4, 2, 3 desc -- Order By Based on column no. without typing column name --distinct: only show unique value select distinct customerlastname from customer order by customerlastname
15. Save table to another table	--into file_name: save result in another table (BASE TABLE) select distinct customerlastname into temp from customer order by customerlastname select * from temp --see the table (data type will remain)
16. Like (search something)	-- (underscore sign) _ is only specific for one character only -- (percent sign) % represents zero, one, or multiple characters select * from customer where customerlastname like '_r%'
17. In (search something)	-- search multiple items select * from customer where customerlastname in ('Brown', ‘Michael’, ’Jim’)
18. > (search something)	select * from customer where customerlastname > 'Brown' or customerlastname>'Cross'
19. <> (Not Equal)	select * from customer where customerlastname <> 'Brown'
20. IS NULL	-- check null values select * from customer where customerlastname IS NULL
21. IS NOT NULL	select * from customer where customerlastname IS NOT NULL
22. between	select * from sale where saleunitprice between 5 and 10 --not include 5 & 10
23. count	-- returns the number of rows in a table -- AS means aliasing, temporary giving name to a column/ table select count(*) as [Number of Records] from customer where customerfirstname like 'B%'
24. sum	select sale.employeeid ,EmployeeFirstName, EmployeeLastName , count(*) as [Number of order] , sum(salequantity) as [Total Quantity] from sale,employee where sale.employeeid = employee.employeeid group by sale.employeeid ,EmployeeFirstName, EmployeeLastName
25. count month	select month(saledate) as [Month], count ( * ) as [Number of sale], sum(salequantity*saleunitprice) as [Total Amount] from sale group by month(saledate)
26. Max/Min/Average	SELECT MAX(Salary) FROM EmployeeSalary
27. having	SELECT JobTitle, COUNT(JobTitle) FROM EmployeeDemographics ED JOIN EmployeeSalary ES  ON ED.EmployeeID = ES.EmployeeID GROUP BY JobTitle HAVING COUNT(JobTitle) > 1 SELECT JobTitle, AVG(Salary) FROM EmployeeDemographics ED JOIN EmployeeSalary ES  ON ED.EmployeeID = ES.EmployeeID GROUP BY JobTitle HAVING AVG(Salary) > 45000 ORDER BY AVG(Salary)
28. Change data type temporary for use	-- CAST(expression AS datatype(length)) SELECT CAST('2017-08-25 00:00:00.000' AS date) -- CONVERT(data_type(length), expression, style) SELECT CONVERT(date,'2017-08-25 00:00:00.000')
29. CASE Statement	SELECT FirstName, LastName, Age, CASE  WHEN Age > 30 THEN 'Old'  WHEN Age BETWEEN 27 AND 30 THEN 'Young'  ELSE 'Baby' END FROM EmployeeDemographics ED WHERE Age IS NOT NULL ORDER BY Age -- SELECT FirstName, LastName, JobTitle, Salary, CASE  WHEN JobTitle = 'Salesman' THEN Salary + (Salary *.10)  WHEN JobTitle = 'Accountant' THEN Salary + (Salary *.05)  WHEN JobTitle = 'HR' THEN Salary + (Salary *.000001)  ELSE Salary + (Salary *.03) END AS SalaryAfterRaise FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID
30. Partition By --returns a single value for each row	SELECT FirstName, LastName, Gender, Salary, COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID
31. String Functions	-- Remove space Select EmployeeID, TRIM(EmployeeID) AS IDTRIM FROM EmployeeErrors Select EmployeeID, RTRIM(EmployeeID) as IDRTRIM FROM EmployeeErrors Select EmployeeID, LTRIM(EmployeeID) as IDLTRIM FROM EmployeeErrors -- Replace Select LastName, REPLACE(LastName, '- Fired', '') as LastNameFixed FROM EmployeeErrors -- Substring Select Substring(err.FirstName,1,3), Substring(dem.FirstName,1,3), Substring(err.LastName,1,3), Substring(dem.LastName,1,3) FROM EmployeeErrors err JOIN EmployeeDemographics dem  on Substring(err.FirstName,1,3) = Substring(dem.FirstName,1,3)  and Substring(err.LastName,1,3) = Substring(dem.LastName,1,3) -- UPPER and LOWER CASE Select firstname, LOWER(firstname) from EmployeeErrors Select Firstname, UPPER(FirstName) from EmployeeErrors"
32. Stored Procedure	CREATE PROCEDURE Temp_Employee @JobTitle nvarchar(100) AS DROP TABLE IF EXISTS #temp_employee Create table #temp_employee ( JobTitle varchar(100), EmployeesPerJob int , AvgAge int, AvgSalary int ) Insert into #temp_employee SELECT JobTitle, Count(JobTitle), Avg(Age), AVG(salary) FROM EmployeeDemographics emp JOIN EmployeeSalary sal  ON emp.EmployeeID = sal.EmployeeID where JobTitle = @JobTitle --- make sure to change this in this script from original above group by JobTitle Select * From #temp_employee GO; By Fang Ying, Sham 6 --- only need to run this on next time EXEC Temp_Employee @JobTitle = 'Salesman'
33. Subquery	-- Subquery in Select SELECT EmployeeID, Salary, (SELECT AVG(Salary) FROM EmployeeSalary) AS AllAvgSalary FROM EmployeeSalary -- with Partition By SELECT EmployeeID, Salary, AVG(Salary) OVER () AS AllAvgSalary FROM EmployeeSalary -- Subquery in From SELECT a.EmployeeID, AllAvgSalary FROM (SELECT EmployeeID, Salary, AVG(Salary) OVER () AS AllAvgSalary  FROM EmployeeSalary) a ORDER BY a.EmployeeID   -- Subquery in Where SELECT EmployeeID, JobTitle, Salary FROM EmployeeSalary WHERE EmployeeID in (SELECT EmployeeID FROM EmployeeDemographics  WHERE Age > 30) SELECT EmployeeID, JobTitle, Salary FROM EmployeeSalary WHERE Salary in (SELECT Max(Salary) FROM EmployeeSalary)
1. getting data from multiple tables (explicit join - without using join command)	select * from inventory,sale where sale.inventoryid=inventory.inventoryid select inventoryname,saledate,saleunitprice,salequantity,salequantity*saleunitprice as [Total amount] from sale,inventory where sale.inventoryid=inventory.inventoryid group by sale.inventoryid,inventoryname,saledate,salequantity,saleunitprice order by inventoryname
2. getting data from multiple tables (implicit join - using join command)	--inner join select * from inventory inner join sale on sale.inventoryid=inventory.inventoryid select inventoryname,saledate,saleunitprice,salequantity,saleunitprice*salequantity as [Total Amount] from inventory inner join sale on sale.inventoryid=inventory.inventoryid order by inventoryname
	--full outer join (shows everything) select sale.inventoryid,inventoryname from inventory full outer join sale on sale.inventoryid=inventory.inventoryid where sale.inventoryid is NULL
	--left join (might have NULL value, since some inventory might not have sales) select inventory.inventoryid,inventoryname from inventory left join sale on sale.inventoryid=inventory.inventoryid
	--left join select inventory.inventoryid,inventoryname from inventory left join sale on sale.inventoryid=inventory.inventoryid where sale.inventoryid is NULL   -- without join: use subquery select inventoryid,inventoryname from inventory where inventoryid not in (select inventoryid from sale)
	--right join select sale.inventoryid,inventoryname from inventory right join sale on sale.inventoryid=inventory.inventoryid
3. Self Join --commonly used in processing hierarchy	--inner join Staff Table employeeID employeefirstname employeelastname managerID 1001 Tan Mei Ling NULL 1002 Kelvin Koh 1001 1003 Amin Wong 1002   select E.employeeID, E.employeefirstname+' '+E.employeelastname as [Full Name], E.managerID, , M.employeefirstname+' '+M.employeelastname as [Manager Name] from staff E inner join staff M on E.managerID = M.employeeID     --left outer join (list all the employees) select E.employeeID, E.employeefirstname+' '+E.employeelastname as [F Name], E.managerID, , M.employeefirstname+' '+M.employeelastname as [Manager Name] from staff E left outer join staff M on E.managerID = M.employeeID
4. Cross Join --generate all combination of records (all possibility	select * from inventory1 cross join inventory2
1. Union --allow you to combine two tables together (but the no. of columns & each column’s data types for 2 tables must be match) --don't need common key, only need common attributes --merge, not showing duplicate record	select cust_lname,cust_fname from customer union select cust_lname,cust_fname from customer_2
2. Union all --merge, but show you everything, even the duplicate record	select cust_lname,cust_fname from customer union all select cust_lname,cust_fname from customer_2
3. Intersect --keep only the rows in common to both query --not showing duplicate record	select cust_lname,cust_fname from customer intersect select cust_lname,cust_fname from customer_2
	select c.cust_lname,c.cust_fname from customer c,customer_2 c2 where c.cust_lname=c2.cust_lname and c.cust_fname=c2.cust_fname
4. Except --generate only the records that are unique to the CUSTOMER table	select cust_lname,cust_fname from customer except select cust_lname,cust_fname from customer_2
	--use subquery select cust_lname,cust_fname from customer where(cust_lname) not in (select cust_lname from customer_2) and (cust_fname) not in (select cust_fname from customer_2)
Table & View
1. view table (view will be updated when update base) --view is a result set of SQL statements, exists only for a single query	create view CustomerView as select customerfirstname+' '+customerlastname as [Customer Name] , customerphonenumber, inventoryname,saledate,salequantity,saleunitprice,salequantity*saleunitprice as [Total Amount] from customer inner join sale on customer.customerid=sale.customerid inner join inventory on sale.inventoryid=inventory.inventoryid
2. Temp table (temp will NOT be updated when update base) --a single hashtag (#) sign must be added in front of their names --used to store data temporarily, physically created in the Tempdb database --can perform CRUD, join, and some other operations like the persistent database tables	DROP TABLE IF EXISTS #temp_Employee Create table #temp_Employee ( JobTitle varchar(100), EmployeesPerJob int, AvgAge int, AvgSalary int ) Insert INTO #temp_Employee SELECT JobTitle, Count(JobTitle), Avg(Age), AVG(salary) FROM EmployeeDemographics emp JOIN EmployeeSalary sal  ON emp.EmployeeID = sal.EmployeeID group by JobTitle SELECT * FROM #temp_Employee
3. CTE (Common Table Expression) --create temporary result set which is used to manipulate the complex sub-queries data --created in memory rather than Tempdb database, so cannot create any index on CTE.	WITH CTE_Employee AS ( SELECT FirstName, LastName, Gender, Salary, COUNT(Gender) OVER (PARTITION BY Gender) AS TotalGender FROM EmployeeDemographics ED JOIN EmployeeSalary ES ON ED.EmployeeID = ES.EmployeeID WHERE Salary > '45000' ) SELECT FirstName, LastName, Gender, TotalGender FROM CTE_Employee WHERE TotalGender = (SELECT MIN(TotalGender) FROM CTE_Employee)
4. Duplicate Table	select customerfirstname+' '+customerlastname as [Customer Name] , customerphonenumber, inventoryname,saledate,salequantity,saleunitprice,salequantity*saleunitprice as [Total Amount] into customerRec from customer inner join sale on customer.customerid=sale.customerid inner join inventory on sale.inventoryid=inventory.inventoryid order by customerfirstname +' '+ customerlastname,inventoryname
SQL RANKS
1. ROW_NUMBER()	--get a unique sequential number for each row --get different ranks for the row having similar values SELECT *,  ROW_NUMBER() OVER(ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary
2. RANK()	--specify rank for each row in the result set --use PARTITION BY to performs calculation on each group --each subset get rank as per Salary in descending order USING PARTITION BY SELECT *,  RANK() OVER(PARTITION BY JobTitle ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary ORDER BY JobTitle, SalaryRank   NOT USING PARTITION BY -- get SAME ranks for the row having similar values SELECT *,  RANK() OVER(ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary ORDER BY SalaryRank
3. DENSE_RANK()	-- if have duplicate values, SQL assigns different ranks to those rows. -- will get the same rank for duplicate or similar values SELECT *,  DENSE_RANK() OVER(ORDER BY Salary DESC) SalaryRank FROM EmployeeSalary ORDER BY SalaryRank
3. Split by delimiter ❖ SUBSTRING(string, start, length) ❖ CHARINDEX(substring, string, start) ❖ LEN(string)	SELECT PropertyAddress, SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1 ) as Address , SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress)) as City From NashvilleHousing   ALTER TABLE NashvilleHousing Add PropertySplitAddress Nvarchar(255); ALTER TABLE NashvilleHousing Add PropertySplitCity Nvarchar(255);   Update NashvilleHousing SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1 ) Update NashvilleHousing SET PropertySplitCity = SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress))
PARSENAME('object_name' , object_piece) --numbering works from right to left ❖ REPLACE(string, old_string, new_string)	Select OwnerAddress, PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3) ,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2) ,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1) From NashvilleHousing   ALTER TABLE NashvilleHousing Add OwnerSplitAddress Nvarchar(255); ALTER TABLE NashvilleHousing Add OwnerSplitCity Nvarchar(255); ALTER TABLE NashvilleHousing Add OwnerSplitState Nvarchar(255); Update NashvilleHousing SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3) Update NashvilleHousing SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2) Update NashvilleHousing SET OwnerSplitState = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1)
5. Remove duplicate records	WITH RowNumCTE AS( Select *, ROW_NUMBER() OVER ( PARTITION BY ParcelID, PropertyAddress, SalePrice, SaleDate, LegalReference ORDER BY UniqueID) as row_num From NashvilleHousing order by ParcelID ) --DELETE Select * From RowNumCTE Where row_num > 1 Order by PropertyAddress