/
SQL TDS.dib
96 lines (65 loc) · 2.4 KB
/
SQL TDS.dib
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
#!meta
{"kernelInfo":{"defaultKernelName":"csharp","items":[{"aliases":[],"name":"csharp"}]}}
#!csharp
#r "nuget: Microsoft.DotNet.Interactive.SqlServer, *-*"
#!csharp
#!connect mssql --kernel-name dreamingincrm "Server=dreamingincrm.crm6.dynamics.com; Authentication=Active Directory Interactive;"
#!markdown
### List of managed solutions
#!sql
#!sql-dreamingincrm
SELECT solutionid, friendlyname, uniquename
FROM Solution
WHERE isvisible=1 AND ismanaged=1
ORDER by friendlyname
#!markdown
### Top 10 users sorted by createdon desc
#!sql
#!sql-dreamingincrm
SELECT TOP 10 systemuserid,fullname,internalemailaddress
FROM SystemUser
ORDER BY createdon DESC
#!markdown
### List of Code Components
The query below will not work because _customcontrol_ entity is not available in the Dataverse TDS endpoint.
#!sql
#!sql-dreamingincrm
SELECT
solution.friendlyname AS Solution,
customcontrol.Name,
customcontrol.CompatibleDataTypes,
customcontrol.ComponentState,
customcontrol.Version, customcontrol.createdon
FROM customcontrol
join solution ON customcontrol.solutionid = solution.solutionid
ORDER BY customcontrol.createdon DESC
#!markdown
### List of Plugins
#!sql
#!sql-dreamingincrm
SELECT
sdkmessagefilter.PrimaryObjectTypeCode,
sdkmessage.Name AS Message,
sdkmessageprocessingstep.Rank,
sdkmessageprocessingstep.StageName,
plugintype.AssemblyName,
plugintype.Name AS PluginName,
sdkmessageprocessingstep.Name AS StepName,
sdkmessageprocessingstep.Description AS StepDescription,
sdkmessageprocessingstep.StatusCodeName,
sdkmessageprocessingstep.FilteringAttributes
FROM sdkmessageprocessingstep
JOIN sdkmessagefilter ON SdkMessageProcessingStep.sdkmessagefilterid = sdkmessagefilter.sdkmessagefilterid
JOIN sdkmessage ON sdkmessagefilter.sdkmessageid = sdkmessage.sdkmessageid
JOIN plugintype ON sdkmessageprocessingstep.eventhandler = plugintype.plugintypeid
WHERE
sdkmessagefilter.IsCustomProcessingStepAllowed = 1
AND sdkmessageprocessingstep.IsHidden = 0
AND sdkmessageprocessingstep.CustomizationLevel = 1
ORDER BY sdkmessagefilter.PrimaryObjectTypeCode,sdkmessage.Name,plugintype.AssemblyName, plugintype.Name
#!markdown
### Variable sharing
Notebooks support variable sharing. The previous query result is stored in the _lastResults_ variable inside the SQL kernel. So, you can use this value inside a C# cell as seen in the example below.
#!csharp
#!share --from sql-dreamingincrm lastResults
lastResults