# startSqlService.ps1
# special provision to deal with SQL Server not starting up due to various reasons
$sqlServerService='mssqlserver'
$sqlWaitSeconds=120
$desiredState='Running'
$sqlWaitTimespan=[timespan]::fromseconds($sqlWaitSeconds)
if($sqlServerService -in $originalStoppedServices.Name){
try{
$sqlService=get-service $sqlServerService -EA Ignore
if($sqlService.Status -ne $desiredState -and $null -ne $sqlService){
$stopped=try{$sqlService.waitforstatus('Stopped',$sqlWaitTimespan);$true}catch{$false}
if($stopped){
$attempt=net start $sqlServerService /T902 2>&1
$running=(get-service $sqlServerService).Status -eq 'Running'
$running=if(!$running){try{$sqlService.waitforstatus('Running',$sqlWaitTimespan);$true}catch{$false}}else{$running}
if($attempt -match 'started' -and $running){
write-host "$sqlServerService has been started with trace flag /T902" -foregroundcolor Green
}else{
write-host "$sqlServerService has NOT been started with trace flag /T902" -foregroundcolor Red
}
}else{
write-warning "$sqlServerService has current state is $((get-service $sqlServerService).Status)"
}
}
}catch{
write-warning $_
}
}
Category: Database
PowerShell: Automatically Fix SQL Server by Killing Long Running Queries
# autofixSqlLongRunningQueries.ps1
# version 0.01
# This version is limited to SQL Server Localhost execution, Not Remote [yet]
# Kill session_id basing on these variables
$sqlServername=$env:computername
$maxBlockDuration=3600000 # 1 hour = 3600000 ms
$maxRunningDuration=3600000
$excludeKeywords='BACKUP','ROLLBACK'
function autofixSqlLongRunningQueries{
param(
$sqlServername=$env:computername,
$maxBlockDuration=3600000,
$maxRunningDuration=3600000,
$excludeKeywords=@('BACKUP','ROLLBACK')
)
$checkRunningQueries="
USE Master
SELECT s.TEXT,r.total_elapsed_time,r.session_id,r.status,r.command
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) s
"
$checkBlockingQueries="
USE Master
GO
SELECT session_id, wait_duration_ms, wait_type, blocking_session_id
FROM sys.dm_os_waiting_tasks
WHERE blocking_session_id <> 0
GO
"
function killSqlSession($sessionId){
return "
USE Master
GO
KILL $sessionId
"
}
$runningQueries=try{
$result=Invoke-Sqlcmd -ServerInstance $sqlServername -Query $checkRunningQueries
$result|?{$_.total_elapsed_time -ge $maxRunningDuration -and $($excludeKeywords|?{$_.TEXT -notmatch $_}) }
}catch{write-warning $_}
$blockingQueries=try{
$result=Invoke-Sqlcmd -ServerInstance $env:computername -Query $checkBlockingQueries
$result=|?{$_.wait_duration_ms -ge $maxBlockDuration}
}catch{write-warning $_}
$sessionsToKill=$runningQueries.session_id+$blockingQueries.blocking_session_id
foreach($sessionId in $sessionsToKill){
$killSessionCommand=killSqlSession $sessionId
write-host $killSessionCommand
# pause
Invoke-Sqlcmd -ServerInstance $sqlServername -Query $killSessionCommand
}
}
autofixSqlLongRunningQueries $sqlServername `
$maxBlockDuration `
$maxRunningDuration `
$excludeKeywords
T-SQL: Ad-hoc Commands to Backup All Databases
-- turn on Windows cmd shell
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell',1
GO
RECONFIGURE
GO
-- use Windows command to map a drive
EXEC XP_CMDSHELL 'net use B: \\SMBSERVER\ShareName'
-- check the drive
EXEC XP_CMDSHELL 'Dir B:'
-- backup all databases, AD-HOC method, not recommended for daily backups
DECLARE @bk_path VARCHAR(256);
DECLARE @db_name VARCHAR(50);
DECLARE @bk_file_date VARCHAR(20);
DECLARE @bk_file_name VARCHAR(256);
DECLARE @i INT = 1;
/* Set backup path */
SET @bk_path = 'B:\Backup\';
/* Backup file format DBname_YYYYMMDD_HHMMSS.BAK. Change this as needed. */
SELECT @bk_file_date = FORMAT(GETDATE(), 'yyyyMMdd_hhmmss');
DECLARE @db_names TABLE (
id INT IDENTITY(1,1) PRIMARY KEY,
db_name VARCHAR(50) NOT NULL );
INSERT INTO @db_names
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') /* Databases which needs to be excluded */
AND state = 0 /* Include only the database which are online */
AND is_in_standby = 0; /* database is not read only for log shipping */
WHILE EXISTS (SELECT 1 from @db_names WHERE Id = @i)
BEGIN
SELECT @db_name = db_name from @db_names WHERE Id = @i;
PRINT 'Backup Started: ' + @db_name
SET @bk_file_name = @bk_path + @db_name + '_' + @bk_file_date + '.BAK';
BACKUP DATABASE @db_name TO DISK = @bk_file_name WITH STATS;
SET @i = @i + 1;
END
GO
-- delete a mapped drive after back has completed
EXEC XP_CMDSHELL 'net use B: /delete'
Microsoft SQL Server Connection Timeout
Error Message:
Resolution:
a. Check SQL Server’s Query Execution Timeout: SSMS > Login > Tools > Options > Query Execution
b. Test port 1433 (known common default sql port) from client to server
# Quick commands
$servername='sql01'
Test-NetConnection $servername -port 1433
portqry.exe -n $servername -p tcp -e 1433
# Sample output
PS C:\Users\sqladmin> Test-NetConnection $servername -port 1433
ComputerName : sql01
RemoteAddress : x.x.x.x
RemotePort : 1433
InterfaceAlias : Ethernet
SourceAddress : y.y.y.y
TcpTestSucceeded : True
PS C:\Users\sqladmin> portqry.exe -n $servername -p tcp -e 1433
Querying target system called:
sql01
Attempting to resolve name to IP address...
Name resolved to x.x.x.x
querying...
TCP port 1433 (ms-sql-s service): LISTENING
Microsoft SQL Server Replication Setup Error Number MSSQL_REPL2110 and Resolution
Symptom:

The distribution agent failed to create temporary files in 'C:\Program Files\Microsoft SQL Server\130\COM' directory. System returned errorcode 5. (Source: MSSQL_REPL, Error number: MSSQL_REPL21100)
Get help: http://help/MSSQL_REPL21100
Resolution:
– Add ‘NT Service\SQLSERVERAGENT’ account FULL permissions into the ‘C:\Program Files\Microsoft SQL Server\[NNN]\COM’ directory (e.g. ‘C:\Program Files\Microsoft SQL Server\130\COM’)
– Assuming that the default account has been used as the run-as account for the SQL Server Agent service.
– Assuming the referenced account above is assigned to the replication job.
– If the Distribution Agent is invoked from a command line, one must grant write permissions to the COM folder for the account that is used to run the Distribution Agent.
SQL: Truncate Transaction Logs
Overview:
Significance: many small teams lack Database Admin resource to babysit SQL databases; therefore, certain transaction logs would grow to be oversized and cause performance issues with SQL Server. Here are the comparisons:
SIMPLE: the SQL Server Transaction Log would automatically truncate on every transaction being committed if Database is configured with the Simple recovery model. This also happens when a Checkpoint operator is triggered.
FULL: the transaction log (LDF) will grow continuously. It will be cleared when a backup of the transaction log triggered (e.g. BACKUP LOG dbname TO DISK = ‘DB_LOG.bak’.
BULK LOGGED: minimal log space usage mode. The transaction log files would clear during a backup operation. There is no automatic log truncation prior.
Practical T-SQL Commands
-- Get Recovery Model of All Databases:
SELECT Name,DATABASEPROPERTYEX(Name,'RECOVERY') AS [Recovery Model]
FROM master.dbo.sysdatabases
-- Sample Output
Name Recovery Model
master SIMPLE
tempdb SIMPLE
model SIMPLE
msdb SIMPLE
TestDB1 FULL
TestDB2 SIMPLE
-- Check log sizes
DBCC SQLPERF (LOGSPACE);
GO
-- Sample output
Database Name Log Size (MB) Log Space Used (%) Status
master 2.242188 39.89547 0
tempdb 71.99219 20.26587 0
model 7.992188 17.64418 0
msdb 28.80469 5.994033 0
TestDB1 71.99219 5.225176 0
TestDB2 7.992188 29.86315 0
-- Get logical name of log file
SELECT name,physical_name FROM sys.master_files WHERE type_desc = 'LOG'
-- Shrink database log
USE [TestDB1]
GO
DBCC SHRINKFILE (N'TestDB1_LOG' , 0, TRUNCATEONLY)
GO
-- Shrink log by performing backup
BACKUP LOG [TestDB1] TO T:\TestDB1_log.bak
-- Set recovery model to SIMPLE and shrink log to 8GB
USE [TestDB1]
ALTER DATABASE [TestDB1] SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE ([TestDB1_log], 8192);
GO
-- Set recovery model to SIMPLE and shrink log to 1MB
USE TestDB1
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE TestDB1
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB
DBCC SHRINKFILE (TestDB1_log, 1);
GO
-- Optional: reset the database recovery model to FULL mode
ALTER DATABASE TestDB1
SET RECOVERY FULL;
GO
-- Change recovery model to FULL
ALTER DATABASE [TestDB1] SET RECOVERY FULL
GO
-- Truncating Data File - NOT log file
USE TestDB1;
GO
SELECT file_id, name -- obtain file_id prior to truncation
FROM sys.database_files;
GO
DBCC SHRINKFILE (1, TRUNCATEONLY);
-- Set all databases recovery model to SIMPLE and truncate all logs
declare @db_name nvarchar(124)
declare cursor cursor FOR
SELECT name AS DBName
FROM sys.databases
where name NOT IN ('tempdb','master','msdb','model)
ORDER BY Name;
OPEN cursor
FETCH NEXT FROM cursor INTO @db_name
WHILE (@@FETCH_STATUS=0)
BEGIN
exec ('declare @logname nvarchar(124)
USE [' + @db_name + ']
SELECT @logname = name FROM sys.database_files where type = 1
ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE
DBCC SHRINKFILE (@logname , 0, TRUNCATEONLY)')
ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL
FETCH NEXT FROM cursor INTO @db_name
END
CLOSE cursor
DEALLOCATE cursor
T-SQL: How To Rename Database in Microsoft SQL Server
The T-SQL
USE master;
GO
ALTER DATABASE TEST_MSCRM SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE TEST_MSCRM MODIFY NAME = RENAMED_MSCRM;
GO
ALTER DATABASE RENAMED_MSCRM SET MULTI_USER;
GO
Sample Output
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
The database name 'RENAMED_MSCRM' has been set.
Completion time: 2012-10-29T16:09:11.0019350-07:00
PowerShell: Detect Microsoft SQL Version and Installed Location
function getSqlInfo{
$results=@()
$instances=(get-itemproperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances
foreach ($i in $instances){
$p=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
$instance=Get-ItemProperty "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$p\Setup"
$name=(Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL').$i
$edition=$instance.Edition
$version=$instance.Version
$label = switch ($version) {
"14.0.3076.1" {"SQL Server 2017 CU14"; break}
"14.0.3006.16" {"SQL Server 2017 CU1"; break}
"14.0.2000.63" {"SQL Server 2017 RTM with Security Update KB4057122"; break}
"14.0.1000.169" {"SQL Server 2017 (vNext) RTM"; break}
"13.0.1601.5" {"SQL Server 2016 RTM"; break}
"12.0.5207.0" {"SQL Server 2014 SP2 with Security Update KB4019093"; break}
"12.2.5000.0" {"SQL Server 2014 SP2"; break}
default {"Unknown Version"; break}
}
$bootStrap=switch ($version.Substring(0,2)) {
"14" {"C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017"; break}
"13" {"C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\SQLServer2016"; break}
"12" {"C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014"; break}
"11" {"C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012"; break}
default {"Unknown"; break}
}
$bin=switch ($version.Substring(0,2)) {
"14" {"C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn"; break}
"13" {"C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn"; break}
"12" {"C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn"; break}
"11" {"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn"; break}
default {"Unknown"; break}
}
$backupDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer" -Name 'BackupDirectory'
$dataDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\Setup" -Name 'SQLDataRoot'
$masterDataDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer\Parameters" -Name 'SQLArg0' | ForEach-Object {$_.Substring(2)}
$masterLogDirectory=Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$name\MSSQLServer\Parameters" -Name 'SQLArg2' | ForEach-Object {$_.Substring(2)}
$result=[pscustomobject]@{
version=$version
name=$name
edition=$edition
label=$label
backupDirectory=$backupDirectory
dataDirectory=$dataDirectory
masterDataDirectory=$masterDataDirectory
masterLogDirectory=$masterLogDirectory
bin=$bin
bootStrap=$bootStrap
}
$results+=$result
}
return $results
}
PS C:\Users\sqlAdmin> getSqlInfo
bin : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn
bootStrap : C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\SQL2017
version : 14.0.1000.169
name : MSSQL14.MSSQLSERVER
edition : Standard Edition
label : SQL Server 2017 (vNext) RTM
backupDirectory : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup
dataDirectory : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL
masterDataDirectory : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\master.mdf
masterLogDirectory : C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
PowerShell: Get SQL Job History
$computername='sql01'
function getSqlJobHistory($sqlServerName){
try{
if(!(get-module SqlServer)){
Install-Module -Name SqlServer
}
Import-Module -Name SqlServer
$sqlServerInstance=Get-SqlInstance -ServerInstance $sqlServerName
$sqlAgent=Get-SqlAgent -ServerInstance $sqlServerInstance.Name
$sqlJobs=$sqlAgent|Get-SqlAgentJob|Get-SqlAgentJobSchedule|?{$_.IsEnabled}
$sqlJobs|select-object Name,ActiveStartTimeOfDay,FrequencyTypes,FrequencySubDayTypes|ft -autosize
}catch{
write-warning $_
return $false
}
}
getSqlJobHistory $computername
PS C:\Users\sqladmin1> getSqlJobHistory sql01
Name ActiveStartTimeOfDay FrequencyTypes FrequencySubDayTypes
---- -------------------- -------------- --------------------
Check Database Integrity.Subplan_1 03:00:00 Weekly Once
Differential Database Backups 22:00:00 Daily Hour
Daily 10PM 22:00:00 Daily Once
Beginning of Month 00:00:00 Monthly Once
Schedule 00:30:00 Daily Once
syspolicy_purge_history_schedule 02:00:00 Daily Once
Schedule1 00:00:00 Daily Second
PowerShell: Get SQL Server Backup Statuses
$computername='sql01'
function getSqlBackupInfo ($sqlInstanceName=$env:computername, $dbName){
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$location=if($sqlInstanceName.Contains("`\")){
"SQLSERVER:\SQL\$sqlInstanceName\Databases"
}else{
"SQLSERVER:\SQL\$sqlInstanceName\DEFAULT\Databases"
}
function getPacificTime($time){
if($time){
[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId($time,'Pacific Standard Time').tostring("MM-dd-yyyy-HH-mm")+'_PST'
}else{
[System.TimeZoneInfo]::ConvertTimeBySystemTimeZoneId((Get-Date),'Pacific Standard Time').tostring("MM-dd-yyyy-HH-mm")+'_PST'
}
}
$displayFormat=@{Label='dbName';Expression={$_.Name}},
@{Label='lastFull';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {'NA'}
ELSE {getPacificTime $_.LastBackupDate}}},
@{Label='lastDifferential';Expression={IF ($_.LastDifferentialBackupDate -eq "01/01/0001 00:00:00") {'N/A'}
ELSE {getPacificTime $_.LastDifferentialBackupDate}}},
# @{Label='mostRecentBackupType';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {'N/A'}
# ELSEIF ($_.LastBackupDate -gt $_.LastDifferentialBackupDate) {'FULL'}
# ELSE {'DIFF'}}},
# @{Label='daysSinceLastBackup';Expression={IF ($_.LastBackupDate -eq "01/01/0001 00:00:00") {"Never Backed Up!"}
# ELSEIF ($_.LastDifferentialBackupDate -gt $_.LastBackupDate) {((Get-Date) – $_.LastDifferentialBackupDate).Days}
# ELSE {((Get-Date)-$_.LastBackupDate).Days}}},
@{Label='dbLocation';Expression={$_.PrimaryFilePath}}
if ($dbName){
ls -force $location | where-object {$_.Name -eq $DBName; $_.Refresh()} |ft $displayFormat
}else{
ls -force $location | where-object {$_.Name -notin 'tempdb','master','model','msdb'; $_.Refresh()} |ft $displayFormat
}
}
getSqlBackupInfo $computername
Use PowerShell to Set Microsoft SQL Database Owner
$owner='CAP\SQL Admins'
$databaseName='TestDb'
$sqlServer=$env:computername
function setDbOwner{
param(
$principle=$env:USERDOMAIN+'\Domain Admins',
$databaseName='TestDB',
$sqlServer
)
function includeSqlTools{
$ErrorActionPreference='stop'
try{
$trustedPsgallery=(Get-PSRepository PSGallery).InstallationPolicy -eq 'Trusted'
if(!$trustedPsgallery){
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
}
if(!(Get-Module sqlserver)){
Install-Module sqlserver -Confirm:$False
}
if(!(Get-Module dbatools)){
Install-Module dbatools -Confirm:$False
}
Import-Module sqlserver
Import-Module dbatools
return $true
}catch{
write-warning $_
return $false
}
}
try{
if(!(includeSqlTools)){
write-warning "Cannot proceed with SQL Tools"
return $false
}
$server=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
$db=New-Object Microsoft.SqlServer.Management.Smo.Database
$db=$server.Databases.Item($databaseName)
$db.SetOwner($principle, $TRUE)
$db.Alter()
return $true
}catch{
Write-Warning
return $false
}
}
setDbOwner $owner $databaseName $sqlServer
Use PowerShell to Grant SysAdmin Role to Certain Users
$principle=$env:USERDOMAIN+'\Domain Admins'
$sqlServer=$env:computername
function includeSqlTools{
$ErrorActionPreference='stop'
try{
$trustedPsgallery=(Get-PSRepository PSGallery).InstallationPolicy -eq 'Trusted'
if(!$trustedPsgallery){
Set-PSRepository -Name PSGallery -InstallationPolicy Trusted
}
if(!(Get-Module sqlserver)){
Install-Module sqlserver -Confirm:$False
}
if(!(Get-Module dbatools)){
Install-Module dbatools -Confirm:$False
}
Import-Module sqlserver
Import-Module dbatools
return $true
}catch{
write-warning $_
return $false
}
}
function grantSysadmin($principle,$sqlServer=$env:computername){
if(!(includeSqlTools)){
write-warning "Unable to proceed without SQL Tools"
return $false
}
$sqlPortOpen=test-netconnection $sqlServer -port 1433 -informationlevel quiet
$result=.{if($sqlPortOpen){
try{
$server=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlServer
$sysadmins=$server.Roles|?{$_.Name -eq 'sysadmin'}
$sysadmins.AddMember($principle)
write-host "Current sysadmin principles:`r`n$(($sysadmins.EnumMemberNames()|out-string).trim())"
return $true
}catch{
Write-Warning
return $false
}
}else{
# Workaround for firewall issues blocking SQL port between jump host and SQL Server
invoke-command -computername $sqlServer {
param($principle,$includeSqlTools)
$sqlTools=[scriptblock]::create($sqlTools).invoke()
if(!$sqlTools){
write-warning "Unable to proceed without SQL Tools"
return $false
}
try{
$server=New-Object Microsoft.SqlServer.Management.Smo.Server("(localhost)")
$sysadmins=$server.Roles|?{$_.Name -eq 'sysadmin'}
$sysadmins.AddMember($principle)
write-host "Current sysadmin principles:`r`n$(($sysadmins.EnumMemberNames()|out-string).trim())"
return $true
}catch{
write-warning $_
return $false
}
} -Args $principle,${function:includeSqlTools}
}
}
return $result
}
grantSysadmin $principle $sqlServer
ODBC Driver 32-bit & 64-bit
The 32-bit version:
Run: odbcad32.exe > check the list of installed 32-bit drivers as illustrated below (sample only)
The 64-bit version:
Run: odbcad64.exe > click on Add > select an appropriate DB driver (example below)
click Finish > Input additional connection info > OK > close the ODBC Data Source Administrator when done
Â
How To Move WordPress Site To Kubernetes Cluster
a. Create backups of source files and database
- Logon to Current Hosting Provider to make backups
- Files:
- Assuming cPanel:
- Login to cPanel
- Click on 'File Manager'
- Select public_html or the directory containing WordPress files
- Select Compress from the top-right menu
- Select 'Bzip2ed Tar Archive' (better compression than Gzip)
- Click 'Compress File(s)' and wait for the process to finish
- Right-click the newly generated public_html.tar.bz2 from cPanel File Manager > select Download
- Find the file in a default download directory (e.g. /home/$(whoami)/Downloads/public_html.tar.bz2)
- Database:
- Assuming cPanel with phpMyAdmin
- Click 'phpMyAdmin' from the 'DATABASES' control group
- Click 'Export'
- Set Export method = Quick, Format = Custom
- Click Go
- Find the *.sql file being downloaded into a default download directory (e.g. /home/$(whoami)/Downloads/localhost.sql)
b. Install Bitnami WordPress in a Kubernetes Cluster
# Add helm chart if not already available
helm repo add bitnami https://charts.bitnami.com/bitnami
# Install WordPress with Dynamic NFS Provisioning
# Documentation: https://hub.kubeapps.com/charts/bitnami/wordpress/10.0.1
# Set variables
appName=kimconnectblog
domainName=blog.kimconnect.com
wordpressusername=kimconnect
wordpressPassword=SOMEPASSWORDHERE
rootPassword=SOMEPASSWORDHERE2
storageClass=nfs-client
# Install
helm install $appName bitnami/wordpress \
--set persistence.accessMode=ReadWriteMany,persistence.storageClass=nfs-client \
--set mariadb.primary.persistence.storageClass=nfs-client \
--set wordpressUsername=$wordpressusername,wordpressPassword=$wordpressPassword \
--set mariadb.auth.rootPassword=$rootPassword \
--set mariadb.auth.password=$rootPassword \
--set ingress.enabled=true,ingress.hostname=$domainName
# Patch the deployed ingress with an existing SSL cert
# Assuming the $appName-cert has already been generated
appName=kimconnectblog
domainName=blog.kimconnect.com
certName=$appName-cert
serviceName=$appName-wordpress
servicePort=80
cat <<EOF > $appName-patch.yaml
spec:
tls:
- hosts:
- $domainName
secretName: $certName
rules:
- host: $domainName
http:
paths:
- path: /
pathType: Prefix
backend:
service:
name: $serviceName
port:
number: $servicePort
EOF
kubectl patch ingress/$appName-wordpress -p "$(cat $appName-patch.yaml)"
c. Import files and database onto new hosting server
- Database:
- Access DB server and import sql dump
podName=kimconnectblog-mariadb-0
kubectl exec --stdin --tty $podName -- /bin/bash
rootPassword=SOMEPASSWORD
echo "show databases;" | mysql -u root -p$rootPassword
MariaDB [(none)]> show databases;exit;
+--------------------+
| Database |
+--------------------+
| bitnami_wordpress |
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
5 rows in set (0.009 sec)
oldDb=kimconne_blog
sqlDump=/bitnami/mariadb/data/kimconnect.sql
mysql -uroot -p$rootPassword test < $sqlDump
grantUser=bn_wordpress # this is the default Bitnami WordPress user
echo "GRANT ALL PRIVILEGES ON $oldDb.* TO $grantUser;" | mysql -uroot -p$rootPassword
#echo "create database $databaseName;" | mysql -uroot -p$rootPassword
#mysql -uroot -p$rootPassword $oldDb -sNe 'show tables' | while read table; do mysql -uroot -p$rootPassword -sNe "RENAME TABLE $oldDb.$table TO $newDb.$table"; done
#echo "create user kimconne_blog@localhost;grant all privileges on kimconne_blog.* to 'kimconne_blog';"| mysql -uroot -p$rootPassword
#ALTER USER 'kimconne_blog'@'localhost' IDENTIFIED BY 'SOMEPASSWORDHERE';
- Files:
- Assuming nfs:
nfsShare=k8s
nfsServer=10.10.10.5
sharePath=/volume1/$nfsShare
mountPoint=/mnt/$nfsShare
sudo mkdir $mountPoint
sudo mount -t nfs $nfsServer:$sharePath $mountPoint # Test mounting
sudo mount | grep $nfsShare # validate mount
# Assuming Kubernetes NFS
# sudo mv /home/$(whoami)/Downloads/localhost.sql $mountPoint/path_to_default-data-sitename-mariadb/data/localhost.sql
# sudo mv /home/$(whoami)/Downloads/public_html.tar.bz2 $mountPoint/public_html.tar.bz2
bz2File=/mnt/k8s/kimconnectblog/public_html.tar.bz2
containerPath=/mnt/k8s/default-kimconnectblog-wordpress-pvc-9f1dd4bd-81f3-489f-9b76-bf70f4fd291c/wordpress/wp-content
tar -xf $bz2File -C $containerPath
cd $containerPath
mv public_html/wp-content wp-content
vim wp-config.php # edit wp config to match the imported database and its prefix
Microsoft SQL: Login failed for user ‘sa’. (.Net SqlClient Data Provider)
Error:
===================================
Cannot connect to SQL-SERVER
===================================
Login failed for user 'sa'. (.Net SqlClient Data Provider)
Resolution:
1. Ensure that the ‘sa’ account is enabled: security > right click user sa > Properties > Status > Enabled > save
2. Enable SQL Server & Windows authentication: right click on the server > Security > select radio button next to ‘SQL and Windows Mode’ > restart SQL or the OS
How to Get Table Sizes of a Database in Microsoft SQL Server
Obtain Table Sizes of One or Multiple Names
-- Get sizes of multiple tables
use DATABASENAME
GO
IF OBJECT_ID('tempdb..#SpaceUsed') IS NOT NULL
DROP TABLE #SpaceUsed
CREATE TABLE #SpaceUsed (
TableName sysname
,NumRows BIGINT
,ReservedSpace VARCHAR(50)
,DataSpace VARCHAR(50)
,IndexSize VARCHAR(50)
,UnusedSpace VARCHAR(50)
)
DECLARE @str VARCHAR(500)
SET @str = 'exec sp_spaceused ''?'''
INSERT INTO #SpaceUsed
EXEC sp_msforeachtable @command1=@str
SELECT TableName, NumRows,
CONVERT(numeric(18,0),REPLACE(ReservedSpace,' KB','')) / 1024 as ReservedSpaceMB,
CONVERT(numeric(18,0),REPLACE(DataSpace,' KB','')) / 1024 as DataSpaceMB,
CONVERT(numeric(18,0),REPLACE(IndexSize,' KB','')) / 1024 as IndexSpaceMB,
CONVERT(numeric(18,0),REPLACE(UnusedSpace,' KB','')) / 1024 as UnusedSpaceMB
FROM #SpaceUsed
-- WHERE TableName IN ('[dbo].[Table1]','[dbo].[Table2]')
ORDER BY TableName asc
Obtain Table Sizes
use DATABASENAME
GO
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows,
--SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
--SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
--(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB,
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UnusedSpaceMB DESC, t.Name
Continued: https://kimconnect.com/sql-creating-a-storage-report-of-databases-residing-within-server/
Microsoft SQL Server: How to Use T SQL to Move Transaction Log Files
Automated Script:
# moveDbStorage.ps1
# Version 0.0.1
# This version is intended for a local MS SQL Server
# Requirements:
# - Integrationed authentication has been enabled within local SQL Server
# - Each database is expected to have been configured with 1 db file and 1 transaction logs file. Deviation to this will be handled in next script version.
$intendedDbLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
$intendedDbLogLocation='D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA'
# $intendedDbBackupLocation='Z:\Backups' # this iteration doesn't deal with backups
function moveDbStorage($databaseName,$intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation){
$erroractionpreference='stop'
function confirmation($content,$testValue="I confirm",$maxAttempts=3){
$confirmed=$false;
$attempts=0;
$content|write-host
write-host "Please review this content for accuracy.`r`n"
while ($attempts -le $maxAttempts){
if($attempts++ -ge $maxAttempts){
write-host "A maximum number of attempts have reached. No confirmations received!`r`n"
break;
}
$userInput = Read-Host -Prompt "Please type in this value => $testValue <= to confirm. Input CANCEL to skip this item";
if ($userInput.ToLower() -eq $testValue.ToLower()){
$confirmed=$true;
write-host "Confirmed!`r`n";
break;
}elseif($userInput -like 'cancel'){
write-host 'Cancel command received.'
$confirmed=$false
break
}else{
cls;
$content|write-host
write-host "Attempt number $attempts of $maxAttempts`: $userInput does not match $testValue. Try again or Input CANCEL to skip this item`r`n"
}
}
return $confirmed;
}
$results=[hashtable]@{}
try{
# Ensure that the server has SQL PowerShell tools
if(!(Get-Module -ListAvailable -Name 'sqlps' -ea SilentlyContinue)){
[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12
$nugetInstalled=Get-PackageProvider nuget
if(!$nugetInstalled){Install-PackageProvider -Name Nuget -RequiredVersion 2.8.5.201 -Force}
$trustPSGallery=(Get-psrepository -Name 'PSGallery').InstallationPolicy
If($trustPSGallery -ne 'Trusted'){
Set-PSRepository -Name 'PSGallery' -InstallationPolicy Trusted
}
Install-Module PSWindowsUpdate -Confirm:$false -Force
}
# Setting correct ACLs on provided storage locations
$sqlInstance='MSSQLSERVER' # Assming default instance
$sqlRunas=(Get-WMIObject Win32_Service -Filter "Name='$sqlInstance'").StartName
foreach ($path in @($intendedDbLocation,$intendedDbLogLocation,$intendedDbBackupLocation)){
write-host "Setting correct permissions on $path"
if(!(Test-Path $path)){
$null=New-Item -ItemType Directory -Force -Path $path
}
try{
write-host "Granting $env:username and Administrators full access to $path..."
$acl=Get-ACL $path
$originalOwnerAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($acl.Owner,"FullControl","Allow")
$administratorsAccess=New-Object System.Security.AccessControl.FileSystemAccessRule('Administrators',"FullControl","Allow")
#$thisUserAccess = New-Object System.Security.AccessControl.FileSystemAccessRule($env:username,"FullControl","Allow")
$sqlAccess=New-Object System.Security.AccessControl.FileSystemAccessRule($sqlRunas,"FullControl","Allow")
$acl.AddAccessRule($originalOwnerAccess)
$acl.AddAccessRule($administratorsAccess)
#$acl.AddAccessRule($thisUserAccess)
$sqlHasFullPermissions=$acl.Access|?{$_.IdentityReference -eq $sqlRunas -and $_.FileSystemRights -eq 'FullControl'}
if(!$sqlHasFullPermissions){
$acl.AddAccessRule($sqlAccess)
}
Set-Acl $path $acl
}catch{
Write-warning $_
}
}
# Get default locations
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
# $sqlServer=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $env:computername
# $defaultBackupDirectory=$sqlServer.Settings.BackupDirectory
# $defaultDataDirectory=$sqlServer.Settings.DefaultFile
# $defaultLogDirectory=$sqlServer.Settings.DefaultLog
# Change Default backup directory
# if($defaultBackupDirectory -ne $intendedDbBackupLocation){
# write-host "Setting default backup location to $dbBackupLocation"
# $sqlServer.Properties["BackupDirectory"].Value = $dbBackupLocation
# $sqlServer.Alter()
# }
# Change database storage locations
$databases=Get-SqlDatabase -ServerInstance $env:computername
# $nonSystemDatabases=$databases|?{$_.Name -notin @('master','model','msdb','tempdb')}|sort -property Size
$nonSystemDatabases=$databases|?{$_.IsSystemObject -ne $true}|sort -property Size
Clear-Host
$confirmedAll=confirmation 'RUN ALL CHANGES AUTOMATICALLY'
foreach($database in $nonSystemDatabases){
#$database=$nonSystemDatabases[0]
$dbName=$database.Name
$tsql="exec sp_helpdb @dbname='$dbName'"
$dbProfile=Invoke-Sqlcmd -Query $tsql -ServerInstance $env:computername
if($null -eq $dbProfile){
write-warning "$dbName status is currently unknown"
$results[$dbName]="$(get-date) : status unknown - no changes"
}else{
$filePaths=$dbProfile.GetEnumerator()|%{$_.filename}
$dbFile=$filePaths[1]
$dbLogFile=$filePaths[2]
$intendedDbFile=Join-Path -Path $intendedDbLocation -ChildPath $($dbName+'.mdf')
$intendedDbLogFile=Join-Path -Path $intendedDbLogLocation -ChildPath $($dbName+'_log.ldf')
$moveDbStorage=$dbFile -ne $intendedDbFile
$moveDbLogStorage=$dbLogFile -ne $intendedDbLogFile
if($moveDbStorage -or $moveDbLogStorage){
# Alternative method:
# $logicalFileNames=Invoke-Sqlcmd -Query "
# USE $dbName;
# GO
# SELECT file_id, name AS logical_name
# FROM sys.database_files"
# $dbLogicalName=$logicalFileNames.logical_name[0]
# $dbLogLogicalName=$logicalFileNames.logical_name[0]
# $tSqlAlterInPlace="
# Use MASTER
# GO
# ALTER DATABASE [$dbName] SET OFFLINE;
# GO
# ALTER DATABASE [$dbName]
# MODIFY FILE (NAME = '$dbLogicalName',
# FILENAME = '$intendedDbFile');
# GO
# ALTER DATABASE [$dbName]
# MODIFY FILE ( NAME = '$dbLogLogicalName',
# FILENAME = '$intendedDbLogFile');
# GO
# ALTER DATABASE AdventureWorks2014 SET ONLINE;
# GO"
# Invoke-Sqlcmd -Query $tSqlDetachDb
$confirmMessage="Set $dbName with these paths:`r`n$dbFile => $intendedDbFile`r`n$dbLogFile => $intendedDbLogFile"
$confirmed=if(!$confirmedAll){confirmation $confirmMessage}else{$true}
if($confirmed){
write-host "Detaching database $dbName..."
$timer=[System.Diagnostics.Stopwatch]::StartNew()
$startTime=get-date
$tSqlDetachDb="
Use MASTER
GO
ALTER DATABASE [$dbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
sp_detach_db @dbname = '$dbName', @skipchecks = 'true';
GO"
try{
Invoke-Sqlcmd -Query $tSqlDetachDb -EA Stop
if($moveDbStorage){
write-host "Moving $dbFile to $intendedDbFile..."
$null=move-item $dbFile $intendedDbFile
}
if($moveDbLogStorage){
write-host "Moving $dbLogFile to $intendedDbLogFile..."
$null=move-item $dbLogFile $intendedDbLogFile
}
$tSqlAttachDb="
Use MASTER
GO
sp_attach_DB '$dbName',
'$intendedDbFile',
'$intendedDbLogFile'
GO"
Invoke-Sqlcmd -Query $tSqlAttachDb
$elapsed=[math]::round($timer.Elapsed.TotalMinutes,2)
$endTime=get-date
$resultMessage="$dbName detached at $startTime and attached at $endTime - duration of $elapsed minutes."
write-host $resultMessage -foregroundcolor Green
$results[$dbName]="$endTime : $resultMessage"
}catch{
write-warning $_
}
}
}else{
$results[$dbName]="$(get-date) : no changes"
}
}
}
}catch{
Write-Warning $_
}
return $results
}
$results=moveDbStorage $databaseName $intendedDbLocation $intendedDbLogLocation $intendedDbBackupLocation
$results|write-host
Prior to making any changes to a SQL Server, one should gather information about the databases residing on such machines
-- Step 0: access the level of activities of all databases on SQL Server
WITH fs
AS
(
select database_id, type, size * 8.0/1048576 size
from sys.master_files
)
SELECT
name,
(select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeGB,
(select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeGB
FROM sys.databases db
ORDER BY LogFileSizeGB Desc
/*
name DataFileSizeGB LogFileSizeGB
------------- ------------- -------------
REPORTINGDB 1.298828125 1.713317871
DATABASE009 235.080322265 1.407531738
DATABASE010 6.718688964 1.359436035
DATABASE999 2.700012207 0.869995117
*/
How To Manually Move Transaction Log File of a Single Database
-- Step 1: get database size of a single database
USE REPORTINGDB;
SELECT
name,
size,
size * 8.0/1048576 'Size (GB)',
max_size
FROM sys.database_files;
/* Sample output:
name size Size (GB) max_size
------------ ------ --------- ---------
REPORTINGDBDat.mdf 155680 1.187744140 -1
REPORTINGDBLog.ldf 33592 0.256286621 268435456
*/
-- Step 2: get database and transaction log file locations
USE REPORTINGDB;
SELECT
name 'Logical Name',
physical_name 'File Location'
FROM sys.database_files;
/* Sample Output
Logical Name File Location
------------ ------------
REPORTINGDBDat.mdf D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf
REPORTINGDBLog.ldf D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf
*/
-- Step 3: set database to single user mode and detach database
-- WARNING: this will render database as OFFLINE !!!
Use MASTER
GO
ALTER DATABASE REPORTINGDB -- Set database to single user mode
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
sp_detach_db @dbname = 'REPORTINGDB', @skipchecks = 'true'; -- Detach the database
GO
-- Step 4: Move the files to new locations using PowerShell
-- move-item $dbFile $intendedDbFile
-- move-item $dbLogFile $intendedDbLogFile
-- Step 5: re-attach database with new log file location
USE master
GO
sp_attach_DB 'REPORTINGDB', -- Re-attach the database
'D:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBDat.mdf',
'E:\MSSQL10_50.DYNDB\MSSQL\DATA\REPORTINGDBLog.ldf'
GO
Microsoft SQL: Shrink vs Truncate
Shrink
The shrink command is to reduce the physical log file size of a database. That can be understood as de-fragmentation, file compaction, and resetting file physical pointers. There’s an inherent risk of data loss should this process fails.
Often databases that are setup with the simple recovery model have corresponding backup jobs that would run during non-business hours (e.g. 2AM) as this will automatically shrink log files. Hence, manual intervention may not be required for this design choice.
Other recovery models such as ‘FULL’ and ‘BULK-Logged’ will require special considerations. The former will store all transactions Data Definition Language (DDL) and Data Manipulation Language (DML) in transaction log files with a sequential order. These logs will not auto-truncate during CHECKPOINT operations. The latter, BULK-Logged is similar to FULL with the main difference in that certain bulk operations are minimally logged. Hence, specific point-in-time data cannot be instantaneously restored without retrieving certain log files in whole. Hence, shrink operations are often necessary for these types of recovery models.
Here’s a sample T-SQL to perform this task on a Full-Recovery-Model database:
Use [master]
Go
ALTER DATABASE [TestDb] SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE (TestDb, 1)
ALTER DATABASE [TestDb] SET RECOVERY FULL WITH NO_WAIT
GO
Truncate
The truncate command changes the status of one or more virtual log files (VLF) from active to inactive, which marks those files for overwriting or reuse. The truncate command can also be substituted with ‘NO_LOG’, which will purge a database’s transaction log. This may be interpreted as actual deletion. It’s notable that sql backup jobs will also automatically truncate log files. However, such process will not delete files unless ‘NO_LOG’ is specified after the backup job.
Here’s a sample T-SQL to perform this task manually:
Use [master]
Go
DBCC SHRINKFILE(TestDb_Log, 1)
BACKUP LOG TestDb WITH TRUNCATE_ONLY -- NO_LOG (to delete log file)
DBCC SHRINKFILE(TestDb_Log, 1)
GO
DBA Comment:
- Adequate disk space provisioning for log files are necessary for the health and longevity of SQL Servers. It’s recommended that log files be placed on a separate volume from the database files for better performance and administration.
- Assuming Simple Recovery models, daily database backup duration should be estimated to account for disk space truncation as that would occur automatically.
- Assuming Full or Bulk_Logged Recovery models, the ALTER DATABASE … SET RECOVERY SIMPLE command as precursor to the SHRINK command is necessary to reduce disk usage on a periodic basis. This sequence should only be executed after each BACKUP operations.
Microsoft Dynamics 365: System Administrator Profile Corruption Problem
Symptoms:
- Field Security Profile Does Not List Enabled Custom Fields
- Certain forms would show a lock symbol with masked contents ‘*****’
- Field Security enable and disable options cannot be saved without throwing errors
- Data management imports fail with error code 0x80040217
Although this issue has been known to affect Microsoft Dynamics 365, the cloud version of Microsoft Products as well as ‘Microsoft Dynamics 365 Customer Engagement’ or on-premise version, our blog shall focus on the latter as the former has been patched by Microsoft since October 2020.
This may occur after Microsoft Dynamics has been upgraded to version 9.0 from earlier versions, certain forms would show a lock symbol with masked contents ‘*****’ when viewed via the GUI web portal. Further more, these are the steps to reproduce and observe other symptoms of this issue, after login to CRM as SystemAdmin:
- Settings > Customizations > Customize the system > Components > Entities > Lead > Fields > change View = Custom > double-click on a customized field (e.g. ‘crm_internalnotes’)
A new window appears, Under the ‘General’ tab > any attempt to toggle to ‘disable’ or ‘enable’ the ‘Field Security’ will generate the ‘Field Security Profile Error’ code ‘-2147158771’. Sometimes, the radio button is even grayed-out so that no changes can be made to that field security - Settings > Customizations > Customize the system > Components > Field Security Profiles > double click on a a field (e.g. ‘common’) > field permissions > [custom field name] would not appear on the list as expected
- Settings > Data management > Imports > click on any item > see error code 0x80040217 (this has happened since the move)
Error Message:

Activity ID: [ACTIVITYGUID]
Timestamp: Sun Nov 01 2020 05:14:05 (Pacific Standard Time)
Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=9.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: Caller [SYSTEMUSERID] does not have full permissions on the attribute '[FIELDNAME]' to unsecure the attributeDetail:
<OrganizationServiceFault xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/xrm/2011/Contracts">
<ActivityId>[ACTIVITYGUID]</ActivityId>
<ErrorCode>-2147158771</ErrorCode>
<ErrorDetails xmlns:d2p1="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
<Message>Caller [SYSTEMUSERID] does not have full permissions on the attribute [ACTIVITYGUID] to unsecure the attribute</Message>
<Timestamp>2020-11-01</Timestamp>
<ExceptionRetriable>false</ExceptionRetriable>
<ExceptionSource i:nil="true" />
<InnerFault i:nil="true" />
<OriginalException i:nil="true" />
<TraceText i:nil="true" />
</OrganizationServiceFault>
Resolution:
Backup database and prepare to restore:
- Sample Backup T-SQL
BACKUP DATABASE TEST_MSCRM
TO DISK = '\\BackupServerUNCPath\TEST_MSCRM.bak' WITH COMPRESSION,COPY_ONLY
GO - Sample Restore T-SQL
USE master
GO
ALTER DATABASE TEST_MSCRM
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
RESTORE DATABASE [TEST_MSCRM] FROM DISK = N'\\BackupServerUNCPath\TEST_MSCRM.bak' WITH FILE = 1,
MOVE N'mscrm' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_MSCRM.mdf',
MOVE N'mscrm_log' TO 'D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\TEST_MSCRM.ldf',
NOUNLOAD, REPLACE, STATS = 5;
ALTER DATABASE [TEST_MSCRM] SET MULTI_USER
GO
Run this T-SQL:
Source: courtesy of my anonymous friend from Microsoft
USE TEST_MSCRM
GO
declare @testOnlyDoNotCommit int = 1; -- Change this value to 0 to apply the fix
DECLARE @SysAdminProfileId uniqueidentifier = '572329C1-A042-4E22-BE47-367C6374EA45'
DECLARE @SecuredAttributeMissingInSysAdminProfile TABLE(AttributeName nvarchar(100), ObjectTypeCode int, AttributeDependencyNodeId uniqueidentifier, CanBeSecuredForCreate bit, CanBeSecuredForRead bit, CanBeSecuredForUpdate bit)
DECLARE @NonSecuredAttributeInSysAdminProfile TABLE(FieldPermissionId uniqueidentifier, AttributeName nvarchar(100), ObjectTypeCode int)
-- Identify field permissions missing in System Administrator profile for secured attributes.
INSERT INTO @SecuredAttributeMissingInSysAdminProfile (AttributeName, ObjectTypeCode, AttributeDependencyNodeId, CanBeSecuredForCreate, CanBeSecuredForRead, CanBeSecuredForUpdate )
SELECT a.LogicalName, e.ObjectTypeCode, dn.DependencyNodeId, a.CanBeSecuredForCreate, a.CanBeSecuredForRead, a.CanBeSecuredForUpdate
FROM AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
LEFT OUTER JOIN FieldPermission fp ON (fp.EntityName = e.ObjectTypeCode AND fp.AttributeLogicalName = a.LogicalName AND FieldSecurityProfileId = @SysAdminProfileId)
LEFT JOIN DependencyNodeBase dn ON dn.ObjectId = a.AttributeId
WHERE a.IsSecured = 1 AND fp.AttributeLogicalName IS NULL
-- Identify invalid field permissions defined in System Administrator profile for non-secured attributes.
INSERT INTO @NonSecuredAttributeInSysAdminProfile (FieldPermissionId, AttributeName, ObjectTypeCode)
SELECT fp.FieldPermissionId, fp.AttributeLogicalName, fp.EntityName
FROM FieldPermission fp LEFT OUTER JOIN (
SELECT a.LogicalName, e.ObjectTypeCode
FROM AttributeLogicalView a INNER JOIN EntityLogicalView e ON a.EntityId = e.EntityId
WHERE a.IsSecured = 1
) ae ON (fp.EntityName = ae.ObjectTypeCode AND fp.AttributeLogicalName = ae.LogicalName)
WHERE FieldSecurityProfileId = @SysAdminProfileId AND ae.LogicalName IS NULL
IF EXISTS(SELECT 1 FROM @SecuredAttributeMissingInSysAdminProfile UNION SELECT 1 FROM @NonSecuredAttributeInSysAdminProfile)
BEGIN
SELECT FORMATMESSAGE('@testOnlyDoNotCommit = %d', @testOnlyDoNotCommit)
-- Output missing permissions.
SELECT 'Field permissions missing in System Administrator profile for secured attributes.'
SELECT * FROM @SecuredAttributeMissingInSysAdminProfile
-- Output invalid permissions.
SELECT 'Invalid field permissions defined in System Administrator profile for non-secured attributes.'
SELECT * FROM @NonSecuredAttributeInSysAdminProfile
BEGIN TRAN
BEGIN TRY
DECLARE @BasicSolutionId uniqueidentifier = '25A01723-9F63-4449-A3E0-046CC23A2902'
DECLARE @RecreatedFieldPermission TABLE(FieldPermissionId uniqueidentifier, AttributeLogicalName nvarchar(50), EntityName int, DependencyNodeId uniqueidentifier)
-- Create missing System Administrator field permissions.
INSERT INTO FieldPermissionBase (FieldPermissionIdUnique,
ComponentState,
SolutionId,
CanRead,
SupportingSolutionId,
CanCreate,
FieldSecurityProfileId,
EntityName,
FieldPermissionId,
IsManaged,
OverwriteTime,
CanUpdate,
AttributeLogicalName)
OUTPUT inserted.FieldPermissionId, inserted.AttributeLogicalName, inserted.EntityName, NEWID() INTO @RecreatedFieldPermission
SELECT NEWID(),
0,
@BasicSolutionId,
CASE WHEN a.CanBeSecuredForRead = 0 THEN 0 ELSE 4 END,
NULL,
CASE WHEN a.CanBeSecuredForCreate = 0 THEN 0 ELSE 4 END,
@SysAdminProfileId,
a.ObjectTypeCode,
NEWID(),
1,
0,
CASE WHEN a.CanBeSecuredForUpdate = 0 THEN 0 ELSE 4 END,
a.AttributeName
FROM @SecuredAttributeMissingInSysAdminProfile a
SELECT * FROM @RecreatedFieldPermission
-- Create dependency.
DECLARE @SystemAdminProfileDependencyNodeId uniqueidentifier
SELECT @SystemAdminProfileDependencyNodeId = DependencyNodeId FROM DependencyNodeBase WHERE ObjectId = @SysAdminProfileId
INSERT INTO DependencyNodeBase (BaseSolutionId, ObjectId, TopSolutionId, ParentId, IsSharedComponent, DependencyNodeId, ComponentType)
OUTPUT inserted.*
SELECT @BasicSolutionId, rfp.FieldPermissionId, @BasicSolutionId, '00000000-0000-0000-0000-000000000000', 0, rfp.DependencyNodeId, 71
FROM @RecreatedFieldPermission rfp
INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
OUTPUT inserted.*
SELECT rfp.DependencyNodeId, NEWID(), 1, a.AttributeDependencyNodeId
FROM @RecreatedFieldPermission rfp JOIN @SecuredAttributeMissingInSysAdminProfile a ON rfp.EntityName = a.ObjectTypeCode AND rfp.AttributeLogicalName = a.AttributeName
WHERE a.AttributeDependencyNodeId IS NOT NULL
INSERT INTO DependencyBase (DependentComponentNodeId, DependencyId, DependencyType, RequiredComponentNodeId)
OUTPUT inserted.*
SELECT rfp.DependencyNodeId, NEWID(), 1, @SystemAdminProfileDependencyNodeId
FROM @RecreatedFieldPermission rfp
WHERE @SystemAdminProfileDependencyNodeId IS NOT NULL
-- Delete invalid System Administrator field permissions.
DELETE FROM FieldPermissionBase
OUTPUT deleted.*
WHERE FieldPermissionId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
-- Delete invalid dependencies.
DELETE FROM DependencyBase
OUTPUT deleted.*
WHERE DependentComponentNodeId IN (
SELECT DependencyNodeId FROM DependencyNodeBase WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
)
DELETE FROM DependencyNodeBase
OUTPUT deleted.*
WHERE ObjectId IN (SELECT FieldPermissionId FROM @NonSecuredAttributeInSysAdminProfile)
IF (@testOnlyDoNotCommit = 1)
BEGIN
ROLLBACK TRAN
SELECT 'TRANSACTION ROLLEDBACK'
END
ELSE
BEGIN
-- Recalculate PrincipalAttributeAccessMap for System Administrator profile members.
DECLARE @principalUserIds EntityIdCollection
DECLARE @principalTeamIds EntityIdCollection
INSERT INTO @principalUserIds SELECT SystemUserId FROM SystemUserProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
INSERT INTO @principalTeamIds SELECT TeamId from TeamProfiles WHERE FieldSecurityProfileId = @SysAdminProfileId
EXEC p_PrincipalAttributeAccessMapReinitBulk @principalUserIds, @principalTeamIds, 1
COMMIT TRAN
SELECT 'TRANSACTION COMMITED'
END
END TRY
BEGIN CATCH
SELECT 'ERROR while executing script. Transaction rolled back. ' + ERROR_MESSAGE()
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK TRAN
SELECT 'TRANSACTION ROLLEDBACK'
END
END CATCH
END
ELSE
BEGIN
SELECT 'System administrator profile is healthy. Did not find any corruption.'
END
SQL: Microsoft Dynamics – Rebuilding a Database View Named ‘FilteredContact’
Why?
Update: currently, I’m unable to rebuild this complex view due to SQL constraints. Hence, this article is a story of a workaround – tracing the object (db table) where a certain view is based. Indexes can often be created on such tables to improve SQL performance.
Because this MS Dynamics db object cannot be indexed, leading to high CPU consumption during runtime. Here’s an example of an occasion when SQL Server has pegged CPU utilization at 100% for hours.
How?
- Read the ‘FilteredContact’ view build code to see that it’s based on the ‘Contacts’ view
- Read the ‘Contacts’ view to realize that it’s based on the ‘ContactBase’ table
- Add an index to ‘ContactBase’ table
Part 1: get the definitions views of ‘Filtered Contact’
--
-- Method 1: Obtaining definition (code) to create object
--
-- Object: 'FilteredContact' view
DECLARE @objectid int
DECLARE @code VARCHAR(MAX)
SET @objectid=(SELECT object_id FROM sys.objects
WHERE name = 'FilteredContact')
SET @code=(SELECT definition from sys.sql_modules
WHERE object_id = @objectid)
-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@code) / 8000) + 1
WHILE @count < @maxcount
BEGIN
print(substring(@code, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END
An attempt to alter an existing view
USE TEST_MSCRM
GO
--ALTER VIEW [dbo].[FilteredContact] WITH SCHEMABINDING
--AS
--SELECT ...;
Another attempt to recreate view
When trying to create an index on such view using a t-sql such as the one shown below:
-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO
This error message occurs:
Msg 1939, Level 16, State 1, Line 4
Cannot create index on view 'FilteredContact' because the view is not schema bound.
Completion time: 2018-11-04T18:58:37.6112165-08:00
Red letters are added strings to the @code output from prior
USE [TEST_MSCRM]
GO
DROP VIEW IF EXISTS dbo.[FilteredContact];
GO
--
/*create view dbo.[FilteredContact] (
[accountid],
[accountiddsc],
[accountidname],
[accountidyominame],
...
) withview_metadataWITH SCHEMABINDING AS
select
[Contact].[AccountId],
--[Contact].[AccountIdDsc]
0,
[Contact].[AccountIdName],
[Contact].[AccountIdYomiName],
[Contact].[AccountRoleCode],
AccountRoleCodePLTable.Value,
[Contact].[Address1_AddressId],
[Contact].[Address1_AddressTypeCode],
Address1_AddressTypeCodePLTable.Value,
...
*/
The idea to recreate a table or view with the Schema Binding option so it can then be indexed to optimize runtime querying CPU utilization. However, some complex tsql has inner joins that runs into the constraints of ‘object referencing itself.’
Part 2: read ‘Contacts’ view
--
-- Method 2: Obtaining definition (code)
--
-- Object: 'Contact' view
DECLARE @sql VARCHAR(MAX);
SELECT @sql = definition
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('Contact');
-- Print text blobs that are longer than 8000 chars
-- Be advised that there will be extra carriage returns
-- Hence printed code cannot be executed without fixing
DECLARE @count INT = 0
DECLARE @maxcount INT = (LEN(@sql) / 8000) + 1
WHILE @count < @maxcount
BEGIN
print(substring(@sql, @count * 8000, (@count + 1) * 8000))
SET @count = @count + 1
END
Error message to indicate ‘Contact’ view was based on ‘ContactBase’ table:
Msg 4512, Level 16, State 3, Procedure Contact, Line 437 [Batch Start Line 2]
Cannot schema bind view 'dbo.Contact' because name 'ContactBase' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
Part 3: Creating a full-text index
It’s often better to run a SQL Profiler Trace and Database Engine Tuning Advisor to automate this process. Hence, the below instructions can be used for targeted database objects with much more effort than following the instructions provided in the above link.
Sample codes to create an index
-- Creating an index
USE TEST_MSCRM
GO
CREATE UNIQUE CLUSTERED INDEX idx_FilteredContact
ON [dbo].[FilteredContact] (contactid,accountid);
GO
-- Create a nonclustered index on a table or view
CREATE INDEX ContactBase ON ContactBase ('EmployeeId');
-- Create a clustered index on a table and use a 3-part name for the table
CREATE CLUSTERED INDEX ContactBase ON TEST_MSCRM.dbo.ContactBase ('EmployeeId');
Navigate to TEST_MSCRM > Tables > right-click ‘dbo.ContactBase’ > Full-Text Index > Define Full-Text Index
click Next to create an index basing on cndx_PrimaryKey_Contact
Select ‘EmployeeId’ > click Next > Next > Next > Next > Finish
click Finish
USE [TEST_MSCRM]
GO
--DROP VIEW IF EXISTS [dbo].[FilteredContact];
--GO
--
-- report view for contact
--
CREATE view [dbo].[FilteredContact](
[accountid],
[accountiddsc],
[accountidname],
[accountidyominame],
[accountrolecode],
[accountrolecodename],
[address1_addressid],
[address1_addresstypecode],
[address1_addresstypecodename],
[address1_city] -- truncated for brevity
) WITH view_metadata AS --WITH SCHEMABINDING AS
select
[Contact].[AccountId],
-- Truncated for brevity
[Contact].[YomiMiddleName],
dbo.fn_GetNumberFormatString(t.CurrencyPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode),
dbo.fn_GetNumberFormatString(o.PricingDecimalPrecision, us.NumberGroupFormat, us.NegativeCurrencyFormatCode, 1, case o.CurrencyDisplayOption when 0 then t.CurrencySymbol when 1 then t.ISOCurrencyCode end, us.CurrencyFormatCode)
from Contact
join SystemUserBase u on (u.SystemUserId = dbo.fn_FindUserGuid() and u.IsDisabled = 0)
left join UserSettingsBase us on us.SystemUserId = u.SystemUserId
left join OrganizationBase o on u.OrganizationId = o.OrganizationId
left join TransactionCurrencyBase t on t.TransactionCurrencyId = [Contact].TransactionCurrencyId
left outer join StringMap [AccountRoleCodePLTable] on
([AccountRoleCodePLTable].AttributeName = 'accountrolecode'
and [AccountRoleCodePLTable].ObjectTypeCode = 2
and [AccountRoleCodePLTable].AttributeValue = [Contact].[AccountRoleCode]
and [AccountRoleCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address1_AddressTypeCodePLTable] on
([Address1_AddressTypeCodePLTable].AttributeName = 'address1_addresstypecode'
and [Address1_AddressTypeCodePLTable].ObjectTypeCode = 2
and [Address1_AddressTypeCodePLTable].AttributeValue = [Contact].[Address1_AddressTypeCode]
and [Address1_AddressTypeCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address1_FreightTermsCodePLTable] on
([Address1_FreightTermsCodePLTable].AttributeName = 'address1_freighttermscode'
and [Address1_FreightTermsCodePLTable].ObjectTypeCode = 2
and [Address1_FreightTermsCodePLTable].AttributeValue = [Contact].[Address1_FreightTermsCode]
and [Address1_FreightTermsCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address1_ShippingMethodCodePLTable] on
([Address1_ShippingMethodCodePLTable].AttributeName = 'address1_shippingmethodcode'
and [Address1_ShippingMethodCodePLTable].ObjectTypeCode = 2
and [Address1_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address1_ShippingMethodCode]
and [Address1_ShippingMethodCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address2_AddressTypeCodePLTable] on
([Address2_AddressTypeCodePLTable].AttributeName = 'address2_addresstypecode'
and [Address2_AddressTypeCodePLTable].ObjectTypeCode = 2
and [Address2_AddressTypeCodePLTable].AttributeValue = [Contact].[Address2_AddressTypeCode]
and [Address2_AddressTypeCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address2_FreightTermsCodePLTable] on
([Address2_FreightTermsCodePLTable].AttributeName = 'address2_freighttermscode'
and [Address2_FreightTermsCodePLTable].ObjectTypeCode = 2
and [Address2_FreightTermsCodePLTable].AttributeValue = [Contact].[Address2_FreightTermsCode]
and [Address2_FreightTermsCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address2_ShippingMethodCodePLTable] on
([Address2_ShippingMethodCodePLTable].AttributeName = 'address2_shippingmethodcode'
and [Address2_ShippingMethodCodePLTable].ObjectTypeCode = 2
and [Address2_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address2_ShippingMethodCode]
and [Address2_ShippingMethodCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address3_AddressTypeCodePLTable] on
([Address3_AddressTypeCodePLTable].AttributeName = 'address3_addresstypecode'
and [Address3_AddressTypeCodePLTable].ObjectTypeCode = 2
and [Address3_AddressTypeCodePLTable].AttributeValue = [Contact].[Address3_AddressTypeCode]
and [Address3_AddressTypeCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address3_FreightTermsCodePLTable] on
([Address3_FreightTermsCodePLTable].AttributeName = 'address3_freighttermscode'
and [Address3_FreightTermsCodePLTable].ObjectTypeCode = 2
and [Address3_FreightTermsCodePLTable].AttributeValue = [Contact].[Address3_FreightTermsCode]
and [Address3_FreightTermsCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [Address3_ShippingMethodCodePLTable] on
([Address3_ShippingMethodCodePLTable].AttributeName = 'address3_shippingmethodcode'
and [Address3_ShippingMethodCodePLTable].ObjectTypeCode = 2
and [Address3_ShippingMethodCodePLTable].AttributeValue = [Contact].[Address3_ShippingMethodCode]
and [Address3_ShippingMethodCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [CreditOnHoldPLTable] on
([CreditOnHoldPLTable].AttributeName = 'creditonhold'
and [CreditOnHoldPLTable].ObjectTypeCode = 2
and [CreditOnHoldPLTable].AttributeValue = [Contact].[CreditOnHold]
and [CreditOnHoldPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [CustomerSizeCodePLTable] on
([CustomerSizeCodePLTable].AttributeName = 'customersizecode'
and [CustomerSizeCodePLTable].ObjectTypeCode = 2
and [CustomerSizeCodePLTable].AttributeValue = [Contact].[CustomerSizeCode]
and [CustomerSizeCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [CustomerTypeCodePLTable] on
([CustomerTypeCodePLTable].AttributeName = 'customertypecode'
and [CustomerTypeCodePLTable].ObjectTypeCode = 2
and [CustomerTypeCodePLTable].AttributeValue = [Contact].[CustomerTypeCode]
and [CustomerTypeCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotBulkEMailPLTable] on
([DoNotBulkEMailPLTable].AttributeName = 'donotbulkemail'
and [DoNotBulkEMailPLTable].ObjectTypeCode = 2
and [DoNotBulkEMailPLTable].AttributeValue = [Contact].[DoNotBulkEMail]
and [DoNotBulkEMailPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotBulkPostalMailPLTable] on
([DoNotBulkPostalMailPLTable].AttributeName = 'donotbulkpostalmail'
and [DoNotBulkPostalMailPLTable].ObjectTypeCode = 2
and [DoNotBulkPostalMailPLTable].AttributeValue = [Contact].[DoNotBulkPostalMail]
and [DoNotBulkPostalMailPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotEMailPLTable] on
([DoNotEMailPLTable].AttributeName = 'donotemail'
and [DoNotEMailPLTable].ObjectTypeCode = 2
and [DoNotEMailPLTable].AttributeValue = [Contact].[DoNotEMail]
and [DoNotEMailPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotFaxPLTable] on
([DoNotFaxPLTable].AttributeName = 'donotfax'
and [DoNotFaxPLTable].ObjectTypeCode = 2
and [DoNotFaxPLTable].AttributeValue = [Contact].[DoNotFax]
and [DoNotFaxPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotPhonePLTable] on
([DoNotPhonePLTable].AttributeName = 'donotphone'
and [DoNotPhonePLTable].ObjectTypeCode = 2
and [DoNotPhonePLTable].AttributeValue = [Contact].[DoNotPhone]
and [DoNotPhonePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotPostalMailPLTable] on
([DoNotPostalMailPLTable].AttributeName = 'donotpostalmail'
and [DoNotPostalMailPLTable].ObjectTypeCode = 2
and [DoNotPostalMailPLTable].AttributeValue = [Contact].[DoNotPostalMail]
and [DoNotPostalMailPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [DoNotSendMMPLTable] on
([DoNotSendMMPLTable].AttributeName = 'donotsendmm'
and [DoNotSendMMPLTable].ObjectTypeCode = 2
and [DoNotSendMMPLTable].AttributeValue = [Contact].[DoNotSendMM]
and [DoNotSendMMPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [EducationCodePLTable] on
([EducationCodePLTable].AttributeName = 'educationcode'
and [EducationCodePLTable].ObjectTypeCode = 2
and [EducationCodePLTable].AttributeValue = [Contact].[EducationCode]
and [EducationCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [FamilyStatusCodePLTable] on
([FamilyStatusCodePLTable].AttributeName = 'familystatuscode'
and [FamilyStatusCodePLTable].ObjectTypeCode = 2
and [FamilyStatusCodePLTable].AttributeValue = [Contact].[FamilyStatusCode]
and [FamilyStatusCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [FollowEmailPLTable] on
([FollowEmailPLTable].AttributeName = 'followemail'
and [FollowEmailPLTable].ObjectTypeCode = 2
and [FollowEmailPLTable].AttributeValue = [Contact].[FollowEmail]
and [FollowEmailPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [GenderCodePLTable] on
([GenderCodePLTable].AttributeName = 'gendercode'
and [GenderCodePLTable].ObjectTypeCode = 2
and [GenderCodePLTable].AttributeValue = [Contact].[GenderCode]
and [GenderCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [HasChildrenCodePLTable] on
([HasChildrenCodePLTable].AttributeName = 'haschildrencode'
and [HasChildrenCodePLTable].ObjectTypeCode = 2
and [HasChildrenCodePLTable].AttributeValue = [Contact].[HasChildrenCode]
and [HasChildrenCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [IsBackofficeCustomerPLTable] on
([IsBackofficeCustomerPLTable].AttributeName = 'isbackofficecustomer'
and [IsBackofficeCustomerPLTable].ObjectTypeCode = 2
and [IsBackofficeCustomerPLTable].AttributeValue = [Contact].[IsBackofficeCustomer]
and [IsBackofficeCustomerPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [IsPrivatePLTable] on
([IsPrivatePLTable].AttributeName = 'isprivate'
and [IsPrivatePLTable].ObjectTypeCode = 2
and [IsPrivatePLTable].AttributeValue = [Contact].[IsPrivate]
and [IsPrivatePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [LeadSourceCodePLTable] on
([LeadSourceCodePLTable].AttributeName = 'leadsourcecode'
and [LeadSourceCodePLTable].ObjectTypeCode = 2
and [LeadSourceCodePLTable].AttributeValue = [Contact].[LeadSourceCode]
and [LeadSourceCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [MarketingOnlyPLTable] on
([MarketingOnlyPLTable].AttributeName = 'marketingonly'
and [MarketingOnlyPLTable].ObjectTypeCode = 2
and [MarketingOnlyPLTable].AttributeValue = [Contact].[MarketingOnly]
and [MarketingOnlyPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [MergedPLTable] on
([MergedPLTable].AttributeName = 'merged'
and [MergedPLTable].ObjectTypeCode = 2
and [MergedPLTable].AttributeValue = [Contact].[Merged]
and [MergedPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [msdyn_gdproptoutPLTable] on
([msdyn_gdproptoutPLTable].AttributeName = 'msdyn_gdproptout'
and [msdyn_gdproptoutPLTable].ObjectTypeCode = 2
and [msdyn_gdproptoutPLTable].AttributeValue = [Contact].[msdyn_gdproptout]
and [msdyn_gdproptoutPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [ParticipatesInWorkflowPLTable] on
([ParticipatesInWorkflowPLTable].AttributeName = 'participatesinworkflow'
and [ParticipatesInWorkflowPLTable].ObjectTypeCode = 2
and [ParticipatesInWorkflowPLTable].AttributeValue = [Contact].[ParticipatesInWorkflow]
and [ParticipatesInWorkflowPLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [PaymentTermsCodePLTable] on
([PaymentTermsCodePLTable].AttributeName = 'paymenttermscode'
and [PaymentTermsCodePLTable].ObjectTypeCode = 2
and [PaymentTermsCodePLTable].AttributeValue = [Contact].[PaymentTermsCode]
and [PaymentTermsCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [PreferredAppointmentDayCodePLTable] on
([PreferredAppointmentDayCodePLTable].AttributeName = 'preferredappointmentdaycode'
and [PreferredAppointmentDayCodePLTable].ObjectTypeCode = 2
and [PreferredAppointmentDayCodePLTable].AttributeValue = [Contact].[PreferredAppointmentDayCode]
and [PreferredAppointmentDayCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [PreferredAppointmentTimeCodePLTable] on
([PreferredAppointmentTimeCodePLTable].AttributeName = 'preferredappointmenttimecode'
and [PreferredAppointmentTimeCodePLTable].ObjectTypeCode = 2
and [PreferredAppointmentTimeCodePLTable].AttributeValue = [Contact].[PreferredAppointmentTimeCode]
and [PreferredAppointmentTimeCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [PreferredContactMethodCodePLTable] on
([PreferredContactMethodCodePLTable].AttributeName = 'preferredcontactmethodcode'
and [PreferredContactMethodCodePLTable].ObjectTypeCode = 2
and [PreferredContactMethodCodePLTable].AttributeValue = [Contact].[PreferredContactMethodCode]
and [PreferredContactMethodCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [ShippingMethodCodePLTable] on
([ShippingMethodCodePLTable].AttributeName = 'shippingmethodcode'
and [ShippingMethodCodePLTable].ObjectTypeCode = 2
and [ShippingMethodCodePLTable].AttributeValue = [Contact].[ShippingMethodCode]
and [ShippingMethodCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [StateCodePLTable] on
([StateCodePLTable].AttributeName = 'statecode'
and [StateCodePLTable].ObjectTypeCode = 2
and [StateCodePLTable].AttributeValue = [Contact].[StateCode]
and [StateCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [StatusCodePLTable] on
([StatusCodePLTable].AttributeName = 'statuscode'
and [StatusCodePLTable].ObjectTypeCode = 2
and [StatusCodePLTable].AttributeValue = [Contact].[StatusCode]
and [StatusCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
left outer join StringMap [TerritoryCodePLTable] on
([TerritoryCodePLTable].AttributeName = 'territorycode'
and [TerritoryCodePLTable].ObjectTypeCode = 2
and [TerritoryCodePLTable].AttributeValue = [Contact].[TerritoryCode]
and [TerritoryCodePLTable].LangId =
case us.UILanguageId
when 0 then o.LanguageCode
else us.UILanguageId
end)
cross join dbo.fn_GetMaxPrivilegeDepthMask(2) pdm
where
(
-- privilege check
pdm.PrivilegeDepthMask is not null and
(
-- Owner check
-- If the user has global access, then skip the ownership check
((pdm.PrivilegeDepthMask & 0x8) != 0) or
[Contact].OwnerId in
( -- returns only principals with Basic Read privilege for entity
select pem.PrincipalId from PrincipalEntityMap pem WITH (NOLOCK)
join SystemUserPrincipals sup WITH (NOLOCK) on pem.PrincipalId = sup.PrincipalId
where sup.SystemUserId = u.SystemUserId
and pem.ObjectTypeCode = 2
)
-- role based access
or
exists
(
select
1
where
(
-- deep/local security
(((pdm.PrivilegeDepthMask & 0x4) != 0) or ((pdm.PrivilegeDepthMask & 0x2) != 0)) and
[Contact].[OwningBusinessUnit] in (select BusinessUnitId from SystemUserBusinessUnitEntityMap WITH (NOLOCK) where SystemUserId = u.SystemUserId and ObjectTypeCode = 2)
)
or
(
-- global security
((pdm.PrivilegeDepthMask & 0x8) != 0) and
[Contact].[OwningBusinessUnit] is not null
)
)
-- object shared to the user
or
[Contact].[ContactId] in
(
select POA.ObjectId from PrincipalObjectAccess POA WITH (NOLOCK)
join SystemUserPrincipals sup WITH (NOLOCK) on POA.PrincipalId = sup.PrincipalId
where sup.SystemUserId = u.SystemUserId
and POA.ObjectTypeCode = 2
and ((POA.AccessRightsMask | POA.InheritedAccessRightsMask) & 1)=1
)
)
)
Sample code for ‘Contact’ view:
USE TEST_MSCRM
GO
--
-- base view for Contact
--
create view dbo.[Contact] (
-- logical attributes
[CreatedByYomiName],
[CreatedByName],
[OriginatingLeadIdYomiName],
[OriginatingLeadIdName],
[ModifiedByExternalPartyYomiName],
[ModifiedByExternalPartyName],
[CreatedByExternalPartyYomiName],
[CreatedByExternalPartyName],
[new_RetainedByYomiName],
[new_RetainedByName],
[DefaultPriceLevelIdName],
[SLAName],
[PreferredSystemUserIdName],
[PreferredSystemUserIdYomiName],
[TransactionCurrencyIdName],
[PreferredServiceIdName],
[SLAInvokedIdName],
-- linked address entities
[Address1_AddressTypeCode],
[Address1_City],
[Address1_Composite],
[Address1_Country],
[Address1_County],
[Address1_AddressId],
[Address1_Fax],
[Address1_FreightTermsCode],
[Address1_Latitude],
[Address1_Line1],
[Address1_Line2],
[Address1_Line3],
[Address1_Longitude],
[Address1_Name],
[Address1_PostalCode],
[Address1_PostOfficeBox],
[Address1_PrimaryContactName],
[Address1_ShippingMethodCode],
[Address1_StateOrProvince],
[Address1_Telephone1],
[Address1_Telephone2],
[Address1_Telephone3],
[Address1_UPSZone],
[Address1_UTCOffset],
[Address2_AddressTypeCode],
[Address2_City],
[Address2_Composite],
[Address2_Country],
[Address2_County],
[Address2_AddressId],
[Address2_Fax],
[Address2_FreightTermsCode],
[Address2_Latitude],
[Address2_Line1],
[Address2_Line2],
[Address2_Line3],
[Address2_Longitude],
[Address2_Name],
[Address2_PostalCode],
[Address2_PostOfficeBox],
[Address2_PrimaryContactName],
[Address2_ShippingMethodCode],
[Address2_StateOrProvince],
[Address2_Telephone1],
[Address2_Telephone2],
[Address2_Telephone3],
[Address2_UPSZone],
[Address2_UTCOffset],
[Address3_AddressTypeCode],
[Address3_City],
[Address3_Composite],
[Address3_Country],
[Address3_County],
[Address3_AddressId],
[Address3_Fax],
[Address3_FreightTermsCode],
[Address3_Latitude],
[Address3_Line1],
[Address3_Line2],
[Address3_Line3],
[Address3_Longitude],
[Address3_Name],
[Address3_PostalCode],
[Address3_PostOfficeBox],
[Address3_PrimaryContactName],
[Address3_ShippingMethodCode],
[Address3_StateOrProvince],
[Address3_Telephone1],
[Address3_Telephone2],
[Address3_Telephone3],
[Address3_UPSZone],
[Address3_UTCOffset],
-- ownership entries
OwnerId,
OwnerIdName,
OwnerIdYomiName,
OwnerIdDsc,
OwnerIdType,
OwningUser,
OwningTeam,
[AccountId],
[AccountIdName],
[AccountIdYomiName],
[ParentContactId],
[ParentContactIdName],
[ParentContactIdYomiName],
-- physical attributes
[ContactId],
[DefaultPriceLevelId],
[CustomerSizeCode],
[CustomerTypeCode],
[PreferredContactMethodCode],
[LeadSourceCode],
[OriginatingLeadId],
[OwningBusinessUnit],
[PaymentTermsCode],
[ShippingMethodCode],
[ParticipatesInWorkflow],
[IsBackofficeCustomer],
[Salutation],
[JobTitle],
[FirstName],
[Department],
[NickName],
[MiddleName],
[LastName],
[Suffix],
[YomiFirstName],
[FullName],
[YomiMiddleName],
[YomiLastName],
[Anniversary],
[BirthDate],
[GovernmentId],
[YomiFullName],
[Description],
[EmployeeId],
[GenderCode],
[AnnualIncome],
[HasChildrenCode],
[EducationCode],
[WebSiteUrl],
[FamilyStatusCode],
[FtpSiteUrl],
[EMailAddress1],
[SpousesName],
[AssistantName],
[EMailAddress2],
[AssistantPhone],
[EMailAddress3],
[DoNotPhone],
[ManagerName],
[ManagerPhone],
[DoNotFax],
[DoNotEMail],
[DoNotPostalMail],
[DoNotBulkEMail],
[DoNotBulkPostalMail],
[AccountRoleCode],
[TerritoryCode],
[IsPrivate],
[CreditLimit],
[CreatedOn],
[CreditOnHold],
[CreatedBy],
[ModifiedOn],
[ModifiedBy],
[NumberOfChildren],
[ChildrensNames],
[VersionNumber],
[MobilePhone],
[Pager],
[Telephone1],
[Telephone2],
[Telephone3],
[Fax],
[Aging30],
[StateCode],
[Aging60],
[StatusCode],
[Aging90],
[PreferredSystemUserId],
[PreferredServiceId],
[MasterId],
[PreferredAppointmentDayCode],
[PreferredAppointmentTimeCode],
[DoNotSendMM],
[ParentCustomerId],
[Merged],
[ExternalUserIdentifier],
[SubscriptionId],
[PreferredEquipmentId],
[LastUsedInCampaign],
[ParentCustomerIdName],
[ParentCustomerIdType],
[TransactionCurrencyId],
[OverriddenCreatedOn],
[ExchangeRate],
[ImportSequenceNumber],
[TimeZoneRuleVersionNumber],
[UTCConversionTimeZoneCode],
[AnnualIncome_Base],
[CreditLimit_Base],
[Aging60_Base],
[Aging90_Base],
[Aging30_Base],
[ParentCustomerIdYomiName],
[CreatedOnBehalfBy],
[ModifiedOnBehalfBy],
[IsAutoCreate],
[StageId],
[ProcessId],
[EntityImageId],
[TraversedPath],
[SLAId],
[SLAInvokedId],
[OnHoldTime],
[LastOnHoldTime],
[FollowEmail],
[TimeSpentByMeOnEmailAndMeetings],
[Business2],
[Callback],
[Company],
[Home2],
[CreatedByExternalParty],
[ModifiedByExternalParty],
[MarketingOnly],
[msdyn_gdproptout]
) with view_metadata as
select
-- logical attributes
[lk_contactbase_createdby].[YomiFullName],
[lk_contactbase_createdby].[FullName],
[contact_originating_lead].[YomiFullName],
[contact_originating_lead].[FullName],
[sla_contact].[Name],
-- linked address entities
[XXaddress1].[AddressTypeCode],
[XXaddress1].[City],
[XXaddress1].[Composite],
[XXaddress1].[Country],
[XXaddress1].[County],
[XXaddress1].[CustomerAddressId],
[XXaddress1].[Fax],
[XXaddress1].[FreightTermsCode],
[XXaddress1].[Latitude],
[XXaddress1].[Line1],
[XXaddress1].[Line2],
[XXaddress1].[Line3],
[XXaddress1].[Longitude],
[XXaddress1].[Name],
[XXaddress1].[PostalCode],
[XXaddress1].[PostOfficeBox],
[XXaddress1].[PrimaryContactName],
[XXaddress1].[ShippingMethodCode],
[XXaddress1].[StateOrProvince],
[XXaddress1].[Telephone1],
[XXaddress1].[Telephone2],
[XXaddress1].[Telephone3],
[XXaddress1].[UPSZone],
[XXaddress1].[UTCOffset],
[XXaddress2].[AddressTypeCode],
[XXaddress2].[City],
[XXaddress2].[Composite],
[XXaddress2].[Country],
[XXaddress2].[County],
[XXaddress2].[CustomerAddressId],
[XXaddress2].[Fax],
[XXaddress2].[FreightTermsCode],
[XXaddress2].[Latitude],
[XXaddress2].[Line1],
[XXaddress2].[Line2],
[XXaddress2].[Line3],
[XXaddress2].[Longitude],
[XXaddress2].[Name],
[XXaddress2].[PostalCode],
[XXaddress2].[PostOfficeBox],
[XXaddress2].[PrimaryContactName],
[XXaddress2].[ShippingMethodCode],
[XXaddress2].[StateOrProvince],
[XXaddress2].[Telephone1],
[XXaddress2].[Telephone2],
[XXaddress2].[Telephone3],
[XXaddress2].[UPSZone],
[XXaddress2].[UTCOffset],
[XXaddress3].[AddressTypeCode],
[XXaddress3].[City],
[XXaddress3].[Composite],
[XXaddress3].[Country],
[XXaddress3].[County],
[XXaddress3].[CustomerAddressId],
[XXaddress3].[Fax],
[XXaddress3].[FreightTermsCode],
[XXaddress3].[Latitude],
[XXaddress3].[Line1],
[XXaddress3].[Line2],
[XXaddress3].[Line3],
[XXaddress3].[Longitude],
[XXaddress3].[Name],
[XXaddress3].[PostalCode],
[XXaddress3].[PostOfficeBox],
[XXaddress3].[PrimaryContactName],
[XXaddress3].[ShippingMethodCode],
[XXaddress3].[StateOrProvince],
[XXaddress3].[Telephone1],
[XXaddress3].[Telephone2],
[XXaddress3].[Telephone3],
[XXaddress3].[UPSZone],
[XXaddress3].[UTCOffset],
-- ownership entries
OwnerId = [ContactBase].OwnerId,
OwnerName = XXowner.Name,
OwnerYomiName = XXowner.YomiName,
OwnerDsc = 0, -- DSC is removed, stub it to 0
OwnerIdType = XXowner.OwnerIdType,
OwningUser = case
when XXowner.OwnerIdType= 8 then XXowner.OwnerId
else null
end,
OwningTeam = case
when XXowner.OwnerIdType= 9 then XXowner.OwnerId
else null
end,
[AccountId] = case
when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
else NULL
end,
[AccountIdName] = case
when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
else NULL
end,
[AccountIdYomiName] = case
when [ContactBase].[ParentCustomerIdType] = 1 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
else NULL
end,
[ParentContactId] = case
when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerId]
else NULL
end,
[ParentContactIdName] = case
when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdName]
else NULL
end,
[ParentContactIdYomiName] = case
when [ContactBase].[ParentCustomerIdType] = 2 AND [ContactBase].[ParentCustomerId] IS NOT NULL then [ContactBase].[ParentCustomerIdYomiName]
else NULL
end,
-- physical attribute
[ContactBase].[ContactId],
[ContactBase].[DefaultPriceLevelId],
[ContactBase].[CustomerSizeCode],
[ContactBase].[CustomerTypeCode],
[ContactBase].[PreferredContactMethodCode],
[ContactBase].[LeadSourceCode],
[ContactBase].[OriginatingLeadId],
[ContactBase].[OwningBusinessUnit],
[ContactBase].[PaymentTermsCode],
[ContactBase].[ShippingMethodCode],
[ContactBase].[ParticipatesInWorkflow],
[ContactBase].[IsBackofficeCustomer],
[ContactBase].[Salutation],
[ContactBase].[JobTitle],
[ContactBase].[FirstName],
[ContactBase].[Department],
[ContactBase].[NickName],
[ContactBase].[MiddleName],
[ContactBase].[LastName],
[ContactBase].[Suffix],
[ContactBase].[YomiFirstName],
[ContactBase].[FullName],
[ContactBase].[YomiMiddleName],
[ContactBase].[YomiLastName],
[ContactBase].[Anniversary],
[ContactBase].[BirthDate],
[ContactBase].[GovernmentId],
[ContactBase].[YomiFullName],
[ContactBase].[Description],
[ContactBase].[EmployeeId],
[ContactBase].[GenderCode],
[ContactBase].[AnnualIncome],
[ContactBase].[HasChildrenCode],
[ContactBase].[EducationCode],
[ContactBase].[WebSiteUrl],
[ContactBase].[FamilyStatusCode],
[ContactBase].[FtpSiteUrl],
[ContactBase].[EMailAddress1],
[ContactBase].[SpousesName],
[ContactBase].[AssistantName],
[ContactBase].[EMailAddress2],
[ContactBase].[AssistantPhone],
[ContactBase].[EMailAddress3],
[ContactBase].[DoNotPhone],
[ContactBase].[ManagerName],
[ContactBase].[ManagerPhone],
[ContactBase].[DoNotFax],
[ContactBase].[DoNotEMail],
[ContactBase].[DoNotPostalMail],
[ContactBase].[DoNotBulkEMail],
[ContactBase].[DoNotBulkPostalMail],
[ContactBase].[AccountRoleCode],
[ContactBase].[TerritoryCode],
[ContactBase].[IsPrivate],
[ContactBase].[CreditLimit],
[ContactBase].[CreatedOn],
[ContactBase].[CreditOnHold],
[ContactBase].[CreatedBy],
[ContactBase].[ModifiedOn],
[ContactBase].[ModifiedBy],
[ContactBase].[NumberOfChildren],
[ContactBase].[ChildrensNames],
[ContactBase].[VersionNumber],
[ContactBase].[MobilePhone],
[ContactBase].[Pager],
[ContactBase].[Telephone1],
[ContactBase].[Telephone2],
[ContactBase].[Telephone3],
[ContactBase].[Fax],
[ContactBase].[Aging30],
[ContactBase].[StateCode],
[ContactBase].[Aging60],
[ContactBase].[StatusCode],
[ContactBase].[Aging90],
[ContactBase].[PreferredSystemUserId],
[ContactBase].[PreferredServiceId],
[ContactBase].[MasterId],
[ContactBase].[PreferredAppointmentDayCode],
[ContactBase].[PreferredAppointmentTimeCode],
[ContactBase].[DoNotSendMM],
[ContactBase].[ParentCustomerId],
[ContactBase].[Merged],
[ContactBase].[ExternalUserIdentifier],
[ContactBase].[SubscriptionId],
[ContactBase].[PreferredEquipmentId],
[ContactBase].[LastUsedInCampaign],
[ContactBase].[ParentCustomerIdName],
[ContactBase].[ParentCustomerIdType],
[ContactBase].[TransactionCurrencyId],
[ContactBase].[OverriddenCreatedOn],
[ContactBase].[ExchangeRate],
[ContactBase].[ImportSequenceNumber],
[ContactBase].[TimeZoneRuleVersionNumber],
[ContactBase].[UTCConversionTimeZoneCode],
[ContactBase].[AnnualIncome_Base],
[ContactBase].[CreditLimit_Base],
[ContactBase].[Aging60_Base],
[ContactBase].[Aging90_Base],
[ContactBase].[Aging30_Base],
[ContactBase].[ParentCustomerIdYomiName],
[ContactBase].[CreatedOnBehalfBy],
[ContactBase].[ModifiedOnBehalfBy],
[ContactBase].[IsAutoCreate],
[ContactBase].[StageId],
[ContactBase].[ProcessId],
[ContactBase].[EntityImageId],
[ContactBase].[TraversedPath],
[ContactBase].[SLAId],
[ContactBase].[SLAInvokedId],
[ContactBase].[OnHoldTime],
[ContactBase].[LastOnHoldTime],
[ContactBase].[FollowEmail],
[ContactBase].[TimeSpentByMeOnEmailAndMeetings],
[ContactBase].[Business2],
[ContactBase].[Callback],
[ContactBase].[Company],
[ContactBase].[Home2],
[ContactBase].[CreatedByExternalParty],
[ContactBase].[ModifiedByExternalParty],
[ContactBase].[MarketingOnly],
[ContactBase].[msdyn_gdproptout]
from [ContactBase]
left join [CustomerAddressBase] XXaddress1 on ([ContactBase].[ContactId] = XXaddress1.ParentId and XXaddress1.AddressNumber = 1)
left join [CustomerAddressBase] XXaddress2 on ([ContactBase].[ContactId] = XXaddress2.ParentId and XXaddress2.AddressNumber = 2)
left join [CustomerAddressBase] XXaddress3 on ([ContactBase].[ContactId] = XXaddress3.ParentId and XXaddress3.AddressNumber = 3)
left join [ContactBase] [contact_master_contact] on ([ContactBase].[MasterId] = [contact_master_contact].[ContactId])
left join [LeadBase] [contact_originating_lead] on ([ContactBase].[OriginatingLeadId] = [contact_originating_lead].[LeadId])
left join [EquipmentBase] [equipment_contacts] on ([ContactBase].[PreferredEquipmentId] = [equipment_contacts].[EquipmentId])
left join [ImageDescriptor] [lk_contact_entityimage] on ([ContactBase].[EntityImageId] = [lk_contact_entityimage].[ImageDescriptorId])
left join [SystemUserBase] [lk_contactbase_createdby] with(nolock) on ([ContactBase].[CreatedBy] = [lk_contactbase_createdby].[SystemUserId])
left join [SystemUserBase] [lk_contactbase_createdonbehalfby] with(nolock) on ([ContactBase].[CreatedOnBehalfBy] = [lk_contactbase_createdonbehalfby].[SystemUserId])
left join [SystemUserBase] [lk_contactbase_modifiedby] with(nolock) on ([ContactBase].[ModifiedBy] = [lk_contactbase_modifiedby].[SystemUserId])
left join [SystemUserBase] [lk_contactbase_modifiedonbehalfby] with(nolock) on ([ContactBase].[ModifiedOnBehalfBy] = [lk_contactbase_modifiedonbehalfby].[SystemUserId])
left join [ExternalPartyBase] [lk_externalparty_contact_createdby] on ([ContactBase].[CreatedByExternalParty] = [lk_externalparty_contact_createdby].[ExternalPartyId])
left join [ExternalPartyBase] [lk_externalparty_contact_modifiedby] on ([ContactBase].[ModifiedByExternalParty] = [lk_externalparty_contact_modifiedby].[ExternalPartyId])
left join [SLABase] [manualsla_contact] on ([ContactBase].[SLAId] = [manualsla_contact].[SLAId] and [manualsla_contact].OverwriteTime = 0 and [manualsla_contact].ComponentState = 0)
left join [SystemUserBase] [new_systemuser_contact_RetainedBy] with(nolock) on ([ContactBase].[new_RetainedBy] = [new_systemuser_contact_RetainedBy].[SystemUserId])
left join [SLABase] [sla_contact] on ([ContactBase].[SLAInvokedId] = [sla_contact].[SLAId] and [sla_contact].OverwriteTime = 0 and [sla_contact].ComponentState = 0)
left join [SystemUserBase] [system_user_contacts] with(nolock) on ([ContactBase].[PreferredSystemUserId] = [system_user_contacts].[SystemUserId])
left join [TransactionCurrencyBase] [transactioncurrency_contact] on ([ContactBase].[TransactionCurrencyId] = [transactioncurrency_contact].[TransactionCurrencyId])
left join OwnerBase XXowner with(nolock) on ([ContactBase].OwnerId = XXowner.OwnerId)