Reporting stops working after upgrading from Service Manager 2012 SP1 to R2
DROP TABLE #PKFixQueries
;WITH FactName
AS (
SELECT w.WarehouseEntityName
FROM etl.WarehouseEntity w
JOIN etl.WarehouseEntityType t ON w.WarehouseEntityTypeId = t.WarehouseEntityTypeId
WHERE t.WarehouseEntityTypeName = 'Fact'
),FactList
AS (
SELECT PartitionName, p.WarehouseEntityName
FROM etl.TablePartition p
JOIN FactName f ON p.WarehouseEntityName = f.WarehouseEntityName
)
, FactWithPK
AS (
SELECT f.WarehouseEntityName, f.PartitionName, b.CONSTRAINT_NAME, a.COLUMN_NAME
FROM FactList f
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE a ON f.PartitionName = a.TABLE_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS b ON a.CONSTRAINT_NAME = b.CONSTRAINT_NAME AND b.CONSTRAINT_TYPE = 'Primary key'
)
, FactWithDefaultOrNoPK
AS (
SELECT DISTINCT f.WarehouseEntityName, f.PartitionName
, 'PK_' + f.WarehouseEntityName AS DefaultPKConstraint
, 'PK_' + f.PartitionName AS NewPKConstraint
FROM FactList f
LEFT JOIN FactWithPK pkf ON pkf.WarehouseEntityName = f.WarehouseEntityName AND pkf.PartitionName = f.PartitionName
WHERE pkf.WarehouseEntityName IS NULL OR pkf.CONSTRAINT_NAME = 'PK_' + f.WarehouseEntityName
)
, FactPKList
AS (
SELECT DISTINCT f.WarehouseEntityName, f.COLUMN_NAME
FROM FactWithPK f
)
, FactPKListStr
AS (
SELECT DISTINCT f1.WarehouseEntityName, F.COLUMN_NAME AS PKList
FROM FactPKList f1
CROSS APPLY (
SELECT '[' + COLUMN_NAME + '],'
FROM FactPKList f2
WHERE f2.WarehouseEntityName = f1.WarehouseEntityName
ORDER BY COLUMN_NAME
FOR XML PATH('')
) AS F (COLUMN_NAME)
)
SELECT f.PartitionName,
'----------------------------- [' + f.PartitionName + '] -----------------------------' + CHAR(13) +
'IF OBJECT_ID(''[' + f.DefaultPKConstraint + ']'') IS NOT NULL' + CHAR(13) +
'BEGIN' + CHAR(13) +
' ALTER TABLE [dbo].[' + f.PartitionName + '] DROP CONSTRAINT [' + f.DefaultPKConstraint + ']' + CHAR(13) +
'END' + CHAR(13) + CHAR(13) +
'IF OBJECT_ID(''[' + f.NewPKConstraint + ']'') IS NULL' + CHAR(13) +
'BEGIN' + CHAR(13) +
' ALTER TABLE [dbo].[' + f.PartitionName + '] ADD CONSTRAINT [' + f.NewPKConstraint + '] PRIMARY KEY NONCLUSTERED (' + SUBSTRING(pk.PKList, 1, LEN(pk.PKList) -1) + ')' + CHAR(13) +
'END' AS Query
INTO #PKFixQueries
FROM FactWithDefaultOrNoPK f
JOIN FactPKListStr pk ON pk.WarehouseEntityName = f.WarehouseEntityName
DECLARE @PartitionName NVARCHAR(MAX), @Query NVARCHAR(MAX)
WHILE EXISTS (SELECT 1 FROM #PKFixQueries)
BEGIN
SELECT TOP 1
@PartitionName = PartitionName,
@Query = Query
FROM #PKFixQueries
PRINT @Query
EXEC(@Query)
DELETE #PKFixQueries
WHERE PartitionName = @PartitionName
END
Missing Scroll Bar SCSM 2012 Portal
body.v4master {
height:inherit;
width:inherit;
overflow:visible!important; //!important to override the original theme.
}
body #s4-workspace {
overflow-y:auto !important;
overflow-x:auto !important;
height:auto !important;
}
</style>
Service Manager - Subscription templates sends e-mail in wrong language
Import-Module smlets ; Add-PSSnapIn SMCmdletSnapIn ;
#Delete the existing data from the CSV Clear-Content -Path "E:SMAdminCSVUsers.csv"
#retrieve all users from the CMDB and cycle through them
$users = (get-scsmclass Microsoft.AD.User$ | get-scsmobject)
foreach ($user in $users)
{
$UserName = $user.UserName #get the username, used to identify the person
$Domain = $user.Domain #get the domain, this is required for a CSV import
$ID = $user.ID #get the users guid, we will use this to create the ID for the settings object $setPref = 'Pref.' #used as a prefix for the localsetting object id
$setID = $setPref,$ID
$setID = [string]::join('',$setID) #combine the users guid and Pref. to make the id for localesettings
if ($user.City -like "Shanghai") {$timezone = 'China Standard Time'}
elseif ($user.City -like "Dhaka") {$timezone = 'Central Asia Standard Time'}
else {$timezone = 'W. Europe Standard Time'}
$locale = '1033' #setting the locale, in this case it's English (United States)
$data = $UserName, $Domain, $setID, $timezone, $locale #put it all into an array
[string]::join(',',$data) | ForEach-Object {Add-Content -Value $_ -Path "E:SMAdminCSVUsers.csv"}; #convert the array to a string and append to the csv
}
#once all users have been added to the csv, import it into the CMDB using the xml mapping file.
Import-SCSMInstance -DataFileName "E:SMAdminCSVUsers.csv" -FormatFileName "E:SMAdminCSVUserImport.xml"
#Remove any automatically created localization settings, the script has create new ones for everybody
get-scsmclass System.UserPreference.Localization$ | get-scsmobject -filter {DisplayName -notlike "%-%"} | remove-scsmobject -force
Remove-PSSnapIn SMCmdLetSnapIn;
Remove-Module smlets;
