MS Dynamics CRM - Display Unique/Auto Number on the form before save.

My client requirement is display the unique reg number for new contact before the save record.
My Previous blog <a href="http://makdns.blogspot.com/2016/03/ms-dynamics-crm-generate-uniqueauto.html">MS Dynamics CRM - Generate Unique/Auto Number</a> will show the reg number on form after save the new contact. if the user clicks save and close, they can't see the number.

So i come up with the web service concept to generate unique number for new contact and display on the contact screen.
Also i am using SQL Server sys.sequences for generating new number.

Web Service Code

SQL Server - Common Table Expressions (CTE)

It's a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. It is not stored as an object and lasts only for the duration of the query. A CTE can be self-referencing and can be referenced multiple times in the same query.

Syntax

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

SELECT <column_list> FROM expression_name;

Multiple cTE

WITH expression_name1 [ ( column_name [,...n] ) ],
     expression_name2 [ ( column_name [,...n] ) ]   

select <column_list> FROM expression_name1
union all
select <column_list> FROM expression_name2

select <column_list> FROM expression_name1 en1 inner join expression_name2 en2 on en1.column_name= en2.column_name

SQL Server - Shortcut to add alias for all the columns

In SSMS, for example

I have a sql query,

select
    BusinessEntityID,
    Bonus,
    SalesLastYear
from
    Sales.SalesPerson

 now  i added alias Sales.SalesPerson sp, want to apply alias name to all the columns quickly, press ALT+Shift button and hold them down while i move my cursor to the lower right of the block.

Now i change to SalesPerson alias name to all column name

select
    SalesPerson.BusinessEntityID,
    SalesPerson.Bonus,
    SalesPerson.SalesYTD,
    SalesPerson.SalesLastYear
from
    Sales.SalesPerson

SQL Server Last executed query history

SELECT

    deqs.last_execution_time AS [Time],

    dest.TEXT AS [Query]

FROM

    sys.dm_exec_query_stats AS deqs

    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest

ORDER BY

    deqs.last_execution_time DESC

http://msdn.microsoft.com/en-us/library/ms188068.aspx

 

Microsoft Dynamics CRM – Exporting more than 10,000 rows to Excel

Log into SQL Management Studio, OPEN CRM Database

update OrganizationBase set MaxRecordsForExportToExcel = 30000 where OrganizationId = ‘GUID of your organization’ 

Update through XRM

Organization organization = new Organization();
organization.Id = “your org id”;
organization.MaxRecordsForExportToExcel = 30000;
service.Update(organization);

Dynamics CRM - Change Contact Name Format

UPDATE contactbase SET fullname = ISNULL(lastname, ”) + ‘, ‘ + ISNULL(firstname, ”)

import file is too large to upload

UPDATE ServerSettingsProperties SET IntColumn =15 where  columnname=‘ImportMaxAllowedFileSizeInMB’ 

Increase Grid Page Limit

Open the UserSettingsBase table,  change the PagingLimi.

SQL Server Shrink Log File

SELECT name,recovery_model_desc FROM sys.databases
GO 

 Step 1
ALTER DATABASE <Your Database Name> SET RECOVERY SIMPLE; 
ALTER DATABASE WSS_Proposal_Content SET RECOVERY SIMPLE;

Step 2
DBCC SHRINKFILE (<Your Database Name>_log, 5); 
DBCC SHRINKFILE (WSS_Proposal_Content_log, 5);

Step3
ALTER DATABASE <Your Database Name> SET RECOVERY FULL; 
ALTER DATABASE WSS_Proposal_Content SET RECOVERY FULL;


Database 1
Alter database SharePoint_Config set Recovery simple
GO

USE [SharePoint_Config]
GO

DBCC SHRINKFILE (N'SharePoint_Config_log' , 50)
go

Alter database SharePoint_Config set Recovery full
GO


Database 2
Alter database WSS_SESProposal_Content set Recovery simple
GO

USE [WSS_SESProposal_Content]
GO
DBCC SHRINKFILE (N'WSS_SESProposal_Content_log',50)
go

Alter database WSS_SESProposal_Content set Recovery full
GO


Database 3
Alter database WSS_Search_Content set Recovery simple
GO

USE [WSS_Search_Content]
GO
DBCC SHRINKFILE (N'WSS_Search_Content_log',50)
go

Alter database WSS_Search_Content set Recovery full
GO

Find Logical File Name
Declare @LogFileLogicalName sysname
select @LogFileLogicalName=Name from sys.database_files where Type=1
print @LogFileLogicalName

Deploying SSIS Packages in SQL Server 2008/2012

1) Create the SSIS Package and Execute it.

2) After executing the solution, the deployment utility is created in the default location if the build is completed successfully. Navigate to the (default location) deployment folder, C:\SSIS\Packages\Import\ExcelFile\bin\Deployment in our case and we will find the deployment files i.e. the integration services project deployment file (ExcelFile.ispac).

3) Copy the Deployment file(local machine)  to the SQL Server Server (Target Server).

SQL Server Integration Services (SSIS)

SQL Server Integration Services (SSIS)  -  to get the data from various sources and put the data to tables or create Cubes for data warehouse, used for ETL.

SSIS is a visual tool with drag and drop feature, which enables us to create SSIS, packages to perform ETL in a very short amount of time. 

Business Intelligence Development Studio (BIDS) is the only way where you develop and test the SSIS package. SSIS package is nothing but a complex XML file.

Start All Programs ->  Microsoft SQL Server 2008 R2 -> SQL Server Business Intelligence Development Studio.

HierarchyID Data type, sp_spaceused

HierarchyID Data type - enables a tree structure, modeling an organizational structure, representing of a file system, representing a set of tasks in a project, modeling a graph of links between web pages.

Methods
child.GetAncestors(n) - returns a hierarchyid representing the nth ancestors
parent.GetDescendant(child1, child2) - returns a child node
node.GetLevel() - returns an integer value representing the depth of the node in the tree.
HierarchyID::GetRoot() - returns the root of the herarchy
parent.IsDescendant(child) - is used to find out if a node is descendant of another node
HierarchyID::Parse(input) - converts a string representation of a herarchyid to a herarchyid value
node.Reparent(oldRoot, newRoot)
node.ToString()

create table tblFolders(OrgNode HierarchyID, FolderName VARCHAR(MAX))

--insert root
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(HierarchyID::GetRoot(),'C:\')

--insert second row-child of root folder
DECLARE @Root HierarchyID;
SELECT @Root = HierarchyID::GetRoot() FROM tblFolders;
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(NULL, NULL),'C:\Program Files')

--insert third row-child of root folder
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Root.GetDescendant(CAST('/1/' AS HierarchyID), NULL),'C:\windows')

--insert a child for the 'C:\Program Files' folder
DECLARE @Parent HierarchyID;
SELECT @Parent = CAST('/1/' AS HierarchyID)
INSERT INTO tblFolders(OrgNode, FolderName) VALUES(@Parent.GetDescendant(NULL, NULL),'C:\Program Files\Microsoft SQL Server')

select OrgNode.ToString(), * from tblFolders

/ C:\
/1/ 0x58 C:\Program Files
/2/ 0x68 C:\windows
/1/1/ 0x5AC0 C:\Program Files\Microsoft SQL Server

CREATE TABLE SimpleDemo (Level hierarchyid NOT NULL, Location nvarchar(30) NOT NULL, LocationType nvarchar(9) NULL);

INSERT SimpleDemo
VALUES ('/1/', 'Europe', 'Continent'), ('/2/', 'South America', 'Continent'), ('/1/1/', 'France', 'Country'),
('/1/1/1/', 'Paris', 'City'), ('/1/2/1/', 'Madrid', 'City'), ('/1/2/', 'Spain', 'Country'),
('/3/', 'Antarctica', 'Continent'), ('/2/1/', 'Brazil', 'Country'), ('/2/1/1/', 'Brasilia', 'City'),
('/2/1/2/', 'Bahia', 'State'), ('/2/1/2/1/', 'Salvador', 'City'), ('/3/1/', 'McMurdo Station', 'City');

SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;

INSERT SimpleDemo VALUES ('/1/3/1/', 'Kyoto', 'City'), ('/1/3/1/', 'London', 'City');
SELECT CAST(Level AS nvarchar(100)) AS [Converted Level], * FROM SimpleDemo ORDER BY Level;

INSERT SimpleDemo
VALUES ('/', 'Earth', 'Planet');


exec sp_spaceused 'Sales.Customer'
exec sp_spaceused 'Sales.Store'

GROUPING SETS, GROUPING_ID(), CUBE, ROLLUP

GROUP BY 
a new operator - the GROUPING SETS operator
a new function - the GROUPING_ID() function

select
t.[Group], t.CountryRegionCode, SUM(h.TotalDue)
from
sales.SalesTerritory t
inner join sales.SalesOrderHeader h on t.TerritoryID= h.TerritoryID
GROUP BY
t.[Group], t.CountryRegionCode
UNION ALL
select t.[Group], NULL, SUM(h.TotalDue) from ... GROUP BY t.[Group]
UNION ALL
select NULL, NULL, SUM(h.TotalDue) from  ...


Group CountryRegionCode (No column name)
Pacific AU 11814376.0952
North America CA 18398929.188
Europe DE 5479819.5755
Europe FR 8119749.346
Europe GB 8574048.7082
North America US 70829863.203
Europe NULL 22173617.6297
North America NULL 89228792.391
Pacific NULL 11814376.0952
NULL NULL 123216786.1159

select
t.[Group], t.CountryRegionCode, SUM(h.TotalDue),GROUPING_ID(t.[Group]),
GROUPING_ID(t.CountryRegionCode), GROUPING_ID(t.[Group], t.CountryRegionCode)
from
sales.SalesTerritory t
inner join sales.SalesOrderHeader h on t.TerritoryID= h.TerritoryID
GROUP BY GROUPING SETS
((t.[Group], t.CountryRegionCode),(t.[Group]),())

Group CountryRegionCode Total GID GID GID
Europe DE 5479819.5755 0 0 0
Europe FR 8119749.346 0 0 0
Europe GB 8574048.7082 0 0 0
Europe NULL 22173617.6297 0 1 1
North America CA 18398929.188 0 0 0
North America US 70829863.203 0 0 0
North America NULL 89228792.391 0 1 1
Pacific AU 11814376.0952 0 0 0
Pacific NULL 11814376.0952 0 1 1
NULL NULL 123216786.1159 1 1 3


Group CountryRegionCode
AU CA DE FR GB US Total
Pacific GROUP BY(Group, CountryRegionCode) GROUP BY([Group])
North America
Europe
ALL GROUP BY(CountryRegionCode) GROUP BY()

GROUP BY GROUPING SETS((t.[Group], t.CountryRegionCode),(t.CountryRegionCode),(t.[Group]),())
Equivalent to 
CUBE => GROUP BY CUBE (t.[Group], t.CountryRegionCode) OR
GROUP BY t.[Group], t.CountryRegionCode WITH CUBE
Generates the GROUP BY aggregate, sub total of group by aggregate, aggregate of group by and total row.

Group CountryRegionCode (No column name)
Pacific AU 11814376.0952
NULL AU 11814376.0952
North America CA 18398929.188
NULL CA 18398929.188
Europe DE 5479819.5755
NULL DE 5479819.5755
Europe FR 8119749.346
NULL FR 8119749.346
Europe GB 8574048.7082
NULL GB 8574048.7082
North America US 70829863.203
NULL US 70829863.203
NULL NULL 123216786.1159
Europe NULL 22173617.6297
North America NULL 89228792.391
Pacific NULL 11814376.0952

ROLLUP => GROUP BY ROLLUP (t.[Group], t.CountryRegionCode) OR GROUP BY t.[Group], t.CountryRegionCode WITH ROLLUP 
Generates the GROUP BY aggregate, aggregate of group by and total row.

Group CountryRegionCode (No column name)
Europe DE 5479819.5755
Europe FR 8119749.346
Europe GB 8574048.7082
Europe NULL 22173617.6297
North America CA 18398929.188
North America US 70829863.203
North America NULL 89228792.391
Pacific AU 11814376.0952
Pacific NULL 11814376.0952
NULL NULL 123216786.1159

Table-valued parameters

Table-valued parameters - allows you to use multiple rows of data in T-SQL statments or send a table as a parameter to functions and stored procedures. It benefits such as flexibility, better performance than other methods of passing list of parameters adn reduce round trips to the server.The user-defined table type used for the table-valued parameters.
(Programmability->Types->User-Defined Table Types->dbo.StocksType)

DROP TABLE Stocks

CREATE TABLE Stocks(StocksName varchar(100), Qty int, Price dec(10,2))

CREATE TYPE StocksType AS TABLE(StocksName varchar(100), Qty int, Price dec(10,2))


CREATE PROCEDURE uspStocks
@tvp StocksType READONLY
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Stocks(StocksName, QTY, Price)
SELECT StocksName, QTY, Price FROM @tvp
END;
GO

DECLARE @v as StocksType
INSERT INTO @v(StocksName, QTY, Price) VALUES('MSFT',100, 36.67), ('SUN',100, 26.67)

EXEC uspStocks @v;
GO

SELECT * FROM Stocks


DataTable dt = new DataTable("Stocks");

dt.Columns.Add("StocksName", typeof(string));
dt.Columns.Add("Qty", typeof(int));
dt.Columns.Add("Price", typeof(decimal));

dt.Rows.Add("GOO", 100, 45.78);
dt.Rows.Add("APP", 50, 35.78);

SqlConnection conn= new SqlConnection("Data Source=local,1433;Initial Catalog=master;Integrated Security=True");
conn.Open();
SqlCommand cmd = new SqlCommand("uspStocks", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param = new SqlParameter("tvp", SqlDbType.Structured);
param.Value = dt;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
conn.Close();

https://msdn.microsoft.com/en-us/library/bb675163%28v=vs.110%29.aspx

Insert Over DML - Merge, Using, Matched, Output, Action

DROP TABLE Stocks
DROP TABLE DailyTradeUpdates
DROP TABLE StockPriceEvolution

CREATE TABLE Stocks(StocksName varchar(100), Qty int, Price dec(10,2))

CREATE TABLE DailyTradeUpdates(StocksName varchar(100), Delta int, Price dec(10,2))

CREATE TABLE StockPriceEvolution(StocksName varchar(100), Price dec(10,2), TradingDate date)


INSERT Stocks VALUES('MSFT',100, 36.67), ('SUN',100, 26.67)

INSERT DailyTradeUpdates  VALUES('MSFT',20, 35.67), ('SUN',10, -6.67), ('GOO',100, 66.67)

SELECT * FROM Stocks

StocksName Qty Price
MSFT 100 36.67
SUN 100 26.67

SELECT * FROM DailyTradeUpdates

StocksName Delta Price
MSFT 20 35.67
SUN 10 -6.67
GOO 100 66.67

MERGE Stocks AS s
USING DailyTradeUpdates AS d ON s.StocksName = d.StocksName and Qty!=0
WHEN MATCHED THEN
--DELETE
UPDATE SET Price = d.Price, Qty+=d.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(d.StocksName, d.Price, d.Delta)
--INSERT (StocksName, Price, Qty) VALUES (d.StocksName, d.Price, d.Delta)
OUTPUT $action, d.StocksName, d.Price;

SELECT * FROM Stocks
StocksName Qty Price
MSFT 120 35.67
SUN 110 -6.67
GOO 66 100.00

INSERT INTO StockPriceEvolution(StocksName, Price, TradingDate)
SELECT StocksName, Price, CONVERT(DATE, SYSDATETIME())
FROM
(
MERGE Stocks AS s
USING DailyTradeUpdates AS d ON s.StocksName = d.StocksName and Qty!=0
WHEN MATCHED THEN
UPDATE SET Price = d.Price, Qty+=d.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(d.StocksName, d.Price, d.Delta)
OUTPUT $action, d.StocksName, d.Price
) Updates(Action, StocksName, Price)
WHERE Action='UPDATE' OR Action='INSERT'

StockPriceEvolution
StocksName Price TradingDate
GOO 66.67 2015-02-11
MSFT 35.67 2015-02-11
SUN -6.67 2015-02-11

https://msdn.microsoft.com/en-us/library/bb510625.aspx
https://msdn.microsoft.com/en-us/library/ms177564.aspx

TSQL Row Constructors (also knows as TABLE Values Constructors), Compound assignment operators

TSQL Row Constructors (also knows as TABLE Values Constructors)
DECLARE @Employees TABLE(
EmpID int,
Name varchar(200),
Email varchar(250),
Wage dec(10,2)
)

INSERT INTO @Employees(EmpID, Name, Email) VALUES(1, 'Noor', 'noor@email.com')
INSERT INTO @Employees(EmpID, Name, Email) VALUES(2, 'Kader', 'kader@email.com')

-- Using Row Constructors
INSERT INTO @Employees(EmpID, Name, Email, wage)
VALUES(1, 'Noor', 'noor@email.com', 100), (2, 'Kader', 'kader@email.com',200)

--pseudo table
SELECT *
FROM (VALUES(1, 'Noor', 'noor@email.com', 100), (2, 'Kader', 'kader@email.com', 200))
Employees(EmpID, Name, Email, wage)

--sub query expression
INSERT INTO @Employees(EmpID, Name, Email, wage)
VALUES((SELECT 1+MAX(EmpID) FROM @Employees), 'Hassan', 'hassan@email.com',75)

--Statment variable declaration/assignment
DECLARE @id int = 5, @bookname varchar(100) =N'SQL SERVER';
DECLARE @minwage dec(20,2) = (SELECT min(wage) FROM @Employees);

select @minwage

Compound assignment operators - +=, -=, *=, /=, %=, &=, !=, ^=
set @minwage=@minwage*10
set @minwage*=10
select @minwage

UPDATE @Employees SET wage*=10

SELECT * FROM @Employees

ISNULL VS COALESCE

For example Production.Product table records

Class	Color	ProductNumber
cl1	Null	Null
cl2	Null	Null
Null	co3	Null
Null	co4	Null
Null	Null	pn5
cl6	co6	Null
cl7	co7	pn7