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.
|
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 |
|
|
|
