Identity Broker Forum
Welcome to the community forum for Identity Broker.
Browse the knowledge base, ask questions directly to the product group, or leverage the community to get answers. Leave ideas for new features and vote for the features or bug fixes you want most.
No similar topics found.
0
Completed
Add log/entity page numbers to top of page
When viewing logs in IdB, sometimes want to go to last page and then scroll back a page at a time. To do this, we click on the previous button/link at the bottom of the page. When the log contains multi-line items, that link/button moves on the screen, so it is not possible to hold the mouse in one place and repeatedly click. If the page number links and previous/next links could be at the top of the list they would remain in one place making this scrolling easier.
Thanks
#first world problem
#lazy consultant
Edit: Look at adding the scrollbar as well, as this can be hidden with large amounts of data.
0
Completed
Improve exception message for missing agent on startup
On startup the following error is displayed:
Unify.Framework.UnifyServiceInitializeException: The given key was not present i n the dictionary. ---> System.Collections.Generic.KeyNotFoundException: The give n key was not present in the dictionary. at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at Unify.Product.IdentityBroker.AgentEngine.InitializeFromXml(XDocument xml) at Unify.Framework.UnifyEngine`1.Initialize() --- End of inner exception stack trace --- at Unify.Framework.UnifyEngine`1.Initialize() at Unify.Service.Connect.Debug.ProgramProxy.Execute()
The error is a result of the connector not having been installed. Consider a better message to be displayed to users.
0
Completed
Set Time Offset transformation with days
Unable to set the time offset transformation with "days". Able to only set hour,min,seconds.
See attached screenshot
settimeoffset_transformation.PNG
0
Completed
Identity Broker support for consolidating multiple instances of same connected system
The CSODBB student solution uses a "SAS2IDM" branded instance of the SQL Connector (IdB 3.*) to present consolidated student records to FIM in a read-only manner. The data is a consolidated SQL 2008 database managed by a custom "SAS2IDM" application which merges data replicated from 43 schools in the Diocese, which I believe was written for CSODBB by a 3rd party consultant .
I would like to earmark the SAS2IDM application for replacement by a new style of connector designed to connect to each of the 43 school instances directly, allowing consolidation to a single adapter in a "like-for-like" swap out initially, but ultimately to allow write-back to the SAS application (if there's a business case for this). Before we can propose such a model, a feasibility assessment would need to be commissioned either by CSODBB, or by UNIFY as a pre-sales idea.
If you're happy for this to proceed simply to the point of an initial assessment, I have already broached this idea with Shane previously and now with Tony Sheehy. I would like to get this into the sales pipeline for CSODBB down the track, as the value proposition would be in terms of reduced maintenance overhead and superior solution performance. The estimate is therefore just to cover an initial feasibility assessment by someone in the UNIFY PG.
Answer
anonymous
7 years ago
Hi Bob,
I was able to find the other issue relating to this on CSODBB-122, and my answer wasn't very helpful, sorry.
The problem with this issue is that it would require quite a large architectural change to be able to union two or more connectors into the one adapter. There would also be the requirement that the key would have to be unique across both systems for this to work (although it sounds like it would work for your scenario). Adds would be an issue, and without a good design wouldn't work (which connector should it be added to?).
Would a solution using a similar way of thinking to Richard Courtenay on CSODBB-122 work?
0
Fixed
SharePoint 2013 - Schema Discovery Issues
Richard Green 9 years ago
in UNIFYBroker/Microsoft SharePoint
•
updated by anonymous 8 years ago •
2
Hi Gents,
I've Started testing the existing SharePoint connector for compatibility with SharePoint 2013.
So far I've installed the 2010 web service into SharePoint and confirmed it's install via the install guide. https://unifysolutions.jira.com/wiki/display/IDBSP40/Installing+the+SharePoint+2010+WCF+Service
I've also installed the connector and performed a successful connection test with the existing agent.
The first issue I've encountered is around the schema discovery components. On attempting scheme discovery, the following error is thrown:
The schema for 'SPTest Connector' connector was not updated for the following reason: System.ServiceModel.FaultException`1[System.ServiceModel.ExceptionDetail]: Value cannot be null. Parameter name: serviceContext (Fault Detail is equal to An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is: System.ArgumentNullException: Value cannot be null. Parameter name: serviceContext at Microsoft.Office.Server.UserProfiles.ProfileManagerBase..ctor(SPServiceContext serviceContext) at Microsoft.Office.Server.UserProfiles.ProfileManagerBase..ctor(SPServiceContext serviceContext, Boolean ignorePrivacy) at Microsoft.Office.Server.UserProfiles.UserProfileManager..ctor(SPServiceContext serviceContext, Boolean IgnoreUserPrivacy, Boolean backwardCompatible) at Microsoft.Office.Server.UserProfiles.UserProfileManager..ctor(SPServiceContext serviceContext) at Unify.Connectors.SharePoint.SharePoint2010WCFService.UNIFYIdentityBrokerService.SharePoint2010UserProfileService.GetSchema() at SyncInvokeGetSchema(Object , Object[] , Object[] ) at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) at System.ServiceModel.Dispatcher.DispatchOperation...).
I've discussed this with Shane, and will be submitting a Source Control request for access to the SharePoint connector repository. In the absence of Adam, I will be investigating this and any other issues that arise from integrating with the new version of SharePoint.
0
Answered
Changes to gappsSharedContactConnector and Calendar Connector
Hi
Has the gappsSharedContactConnector changed?
I believe previously we had to put in
Env:
Username:
Password:
while creating the connector. Now it does not seem to be the case?
Just wanted to confirm. Its better for us if it was removed. Easier to maintain..
If that is the case.. can we do the same for calendar connector? We still have to mention domain there.. and if it can pickup from agent file, we won't have to maintain three separate Unify.Product.IdentityBroker.ConnectorEnginePlugInKey.extensibility.config.xml files for DEV / QAT / PROD (due to static domain fields)
But if calendar connector requires it for some reason then we can let it be.
0
Fixed
Values for a binary data type attribute are not returned (by confirming import) when exported to Identity Broker from ILM 2007 FP1
Bob Bradley 13 years ago
in UNIFYBroker/Microsoft Identity Manager
•
updated by anonymous 8 years ago •
7
Values exported successfully to the "objectSid" attribute (binary data type) of the person object from ILM are not being returned in a subsequent delta import, nor are they returning in a full import either.
The purpose of using this data type is that it is the native data type for the AD objectSid (unique directory identifier), ensuring a guaranteed match via a direct join rule and attribute flow.
I have implemented a work-around using the "objectGUID" attribute (also binary data type in AD and in the ILM metaverse, but a guid data type in Identity Broker. The downside of this work-around is that rules extension logic is required to convert byte arrays to guids, via string data types in the ILM MA CS. This is quite convoluted and while it works, the use of objectSid would be far preferable (simpler to maintain and troubleshoot).
I first witnessed this behaviour (which I believe to be a bug) in Identity Broker 3.0.0, and upgrading to 3.0.5 has made no difference. Please escalate this to the UNIFY PG for prioritisation.
0
Answered
There is no primary object class on this image
André van der Westhuizen 9 years ago
in UNIFYBroker/Microsoft Identity Manager
•
updated by anonymous 8 years ago •
21
I have created an MA for the telephone file list and when I run an export to update the telephone number I receive the following error:
The management agent controller encountered an unexpected error. "BAIL: MMS(3740): d:\bt\37281\private\source\miis\ma\extensible\export.cpp(2809): 0x8023030d (There is no primary object class on this image.) BAIL: MMS(3740): d:\bt\37281\private\source\miis\ma\extensible\export.cpp(2046): 0x8023030d (There is no primary object class on this image.) BAIL: MMS(3740): d:\bt\37281\private\source\miis\ma\extensible\export.cpp(521): 0x8023030d (There is no primary object class on this image.) BAIL: MMS(3740): d:\bt\37281\private\source\miis\cntrler\cntrler.cpp(9812): 0x80230808 (The management agent run was terminated as there were unspecified management agent errors.) BAIL: MMS(3740): d:\bt\37281\private\source\miis\cntrler\cntrler.cpp(8533): 0x80230808 (The management agent run was terminated as there were unspecified management agent errors.) Forefront Identity Manager 4.1.3599.0"
Herewith the update to the object:
<?xml version="1.0" encoding="UTF-16"?> <mmsml xmlns="http://www.microsoft.com/mms/mmsml/v2" step-type="export"> <directory-entries> <delta operation="update" dn="CN=4,OU=telephone,DC=IdentityBroker"> <anchor encoding="base64">SAAAAEMATgA9ADQALABPAFUAPQB0AGUAbABlAHAAaABvAG4AZQAsAEQAQwA9AEkAZABlAG4AdABpAHQAeQBCAHIAbwBrAGUAcgAAAA==</anchor> <attr name="Mobile" operation="update" type="string" multivalued="false"> <value operation="delete">04159876523</value> <value operation="add">612-555-0100</value> </attr> </delta> </directory-entries> </mmsml>
There is no events in the Identity Broker log files.
screenshot-1.png
screenshot-2.png
screenshot-3.png
Unify.Framework.Collections.dll
Unify.IdentityBroker.Adapter.dll
UnifyLog20150213.csv
UnifyLog20150215 - 1st dll.csv
UnifyLog20150215 - 2nd dll.csv
UnifyLog20150215 - 3rd dll.csv
UnifyLog20150216 - 4th DLL.csv
UnifyLog20150216 - adapterfile.csv
UnifyLog20150216 - Exclusion of collection.csv
UnifyLog20150216 - new collection.csv
0
Fixed
Failed to do a DB upgrade from version 3 to 5
Have created the IdB3 Database from the SQL Script DofD has supplied for there DB.
1. Implement the IdB3 DB script in SQL and create the DB “UnifyIdBroker”;
2. Run an installation of the IdB5 with Database upgrade option;
3. Receive the following error:
4. The installation rollback and I receive the following error in the Application Event Log:
Log Name: Application
Source: MsiInstaller
Date: 9/22/2015 3:01:35 PM
Event ID: 10005
Task Category: None
Level: Error
Keywords: Classic
User: FIMTEST\svc-fimadmin
Computer: SVR-App1.FIMTest.local
Description:
Product: UNIFY Identity Broker Service v5.0.2 RTM x64 -- Error 26204. Error -2147217900: failed to execute SQL string, error detail: Could not drop constraint. See previous errors., SQL key: Script.486dba2a_a6cb_4a23_ba7f_4b179c82a0f8 SQL string: DECLARE @v303 BIT
DECLARE @v50 BIT
SET @v303 = 0
SET @v50 = 0
--NOTE: All versions that require upgrade must be set.
IF (SELECT COUNT(*) FROM sys.columns WHERE name = 'StoredValueCollectionKey') = 0
BEGIN
SET @v303 = 1
SET @v50 = 1
END
IF (SELECT COUNT(*) FROM sys.objects WHERE name = 'SetRequired') = 0
BEGIN
SET @v50 = 1
END
--Clean up--
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ChangesSelect')
BEGIN
EXEC('DROP TABLE ChangesSelect')
END
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'EntityValueSelect')
BEGIN
EXEC('DROP TABLE EntityValueSelect')
END
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'EntitySelect')
BEGIN
EXEC('DROP TABLE EntitySelect')
END
--Upgrades--
IF @v303 = 1
BEGIN
ALTER TABLE [dbo].[StoredValueCollection] DROP CONSTRAINT [PK_StoredValueCollection]
DROP INDEX Entity._dta_index_Entity_c_7_149575571__K1
DROP INDEX Entity.IX_Entity_ObjectClass_PartitionId
DROP INDEX EntityValue._dta_index_EntityValue_7_181575685__K13_K12_K2_1_3_4_5_6_8_9_11
DROP INDEX EntityValue._dta_index_EntityValue_c_7_181575685__K2
DROP INDEX EntityValue.IX_EntityValue_IntValue
DROP INDEX EntityValue.IX_EntityValue_PartitionId
DROP INDEX EntityValue.IX_EntityValue_StringValue
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_CollectionKey')
BEGIN
EXEC('ALTER TABLE [CollectionKey] DROP CONSTRAINT [PK_CollectionKey]')
END
EXEC('ALTER TABLE [dbo].[CollectionKey] ADD CONSTRAINT [PK_CollectionKey] PRIMARY KEY CLUSTERED ([CollectionKeyId] ASC)')
EXEC('sp_rename ''StoredValueCollection'', ''StoredValueCollection_bak''')
CREATE TABLE [dbo].[StoredValueCollection](
[StoredValueCollectionKey] [bigint] IDENTITY(1,1) NOT NULL,
[StoredValueCollectionId] [uniqueidentifier] NOT NULL,
[Values] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_StoredValueCollection] PRIMARY KEY NONCLUSTERED
(
[StoredValueCollectionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
EXEC('INSERT INTO StoredValueCollection (
[StoredValueCollectionId],
[Values]
)
SELECT
[StoredValueCollectionId],
[Values]
FROM StoredValueCollection_bak
UPDATE StoredValueCollection SET [Values] = REPLACE([Values], ''/Unify.Framework"'', ''/Unify.Framework.StoredValues"'')')
--Update IdentityBroker ValueTypes
UPDATE EntityValue
SET ValueType = 0
WHERE ValueType = 128
UPDATE EntityValue
SET ValueType = POWER(2, (ValueType))
WHERE ValueType < 128
UPDATE EntityValue
SET ValueType = POWER(2, (ValueType - 128)) + 1
WHERE ValueType > 128 AND ValueType < 256
EXEC('CREATE CLUSTERED INDEX [IX_StoredValueCollectionKey] ON [dbo].[StoredValueCollection]
(
[StoredValueCollectionKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
END
IF @v50 = 1
BEGIN
IF (SELECT COUNT(*) FROM sys.views WHERE name = 'AdapterEntityChanges') > 0
BEGIN
EXEC('DROP VIEW AdapterEntityChanges')
END
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_Changes_Identity')
BEGIN
EXEC('ALTER TABLE [Changes] DROP CONSTRAINT [PK_Changes_Identity]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_EntityValue_Entity')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [FK_EntityValue_Entity]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_EntityValue_CollectionKey')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [FK_EntityValue_CollectionKey]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_Entity_ObjectClass')
BEGIN
EXEC('ALTER TABLE [Entity] DROP CONSTRAINT [FK_Entity_ObjectClass]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_Entity_Partition')
BEGIN
EXEC('ALTER TABLE [Entity] DROP CONSTRAINT [FK_Entity_Partition]')
END
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_Entity')
BEGIN
EXEC('ALTER TABLE [Entity] DROP CONSTRAINT [PK_Entity]')
END
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_EntityValue')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [PK_EntityValue]')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EntityValueKey')
BEGIN
EXEC('DROP INDEX EntityValue.IX_EntityValueKey')
END
IF EXISTS (SELECT * FROM sys.default_constraints WHERE name = 'DF_EntityValue_ValueOrder')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [DF_EntityValue_ValueOrder]')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Entity_PartitionId')
BEGIN
EXEC('DROP INDEX Entity.IX_Entity_PartitionId')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EntityValue_EntityId_PartitionId')
BEGIN
EXEC('DROP INDEX EntityValue.IX_EntityValue_EntityId_PartitionId')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EntityValue_ValueTypes')
BEGIN
EXEC('DROP INDEX EntityValue.IX_EntityValue_ValueTypes')
END
EXEC('sp_rename ''Entity'', ''Entity_bak''')
EXEC('CREATE TABLE [dbo].[Entity](
[EntityKey] [bigint] IDENTITY(1,1) NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[CreatedTime] [datetime2] NULL,
[ModifiedTime] [datetime2] NULL,
[ContainerId] [uniqueidentifier] NULL,
[ObjectClassId] [int] NULL,
[DN] [nvarchar](400) NULL,
CONSTRAINT [PK_Entity] PRIMARY KEY NONCLUSTERED
(
[EntityId] ASC,
[PartitionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)')
EXEC('INSERT INTO Entity (
[EntityId],
[PartitionId],
[CreatedTime],
[ModifiedTime],
[ObjectClassId],
[DN]
)
SELECT
[EntityId],
[PartitionId],
GETDATE(),
GETDATE(),
[ObjectClassId],
[DN] [nvarchar]
FROM Entity_bak')
EXEC('sp_rename ''EntityValue'', ''EntityValue_bak''')
EXEC('CREATE TABLE [dbo].[EntityValue](
[EntityValueKey] [bigint] IDENTITY(1,1) NOT NULL,
[EntityValueId] [uniqueidentifier] NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[ValueType] [int] NOT NULL,
[BigIntValue] [bigint] NULL,
[BitValue] [bit] NULL,
[FloatValue] [float] NULL,
[ImageValue] [varbinary](max) NULL,
[IntValue] [int] NULL,
[StringValue] [nvarchar](max) NULL,
[StringHash] [int] NULL,
[UniqueIdentifierValue] [uniqueidentifier] NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[CollectionKeyId] [int] NOT NULL,
[ValueOrder] [int] NOT NULL,
[DecimalValue] [decimal](38, 12) NULL,
CONSTRAINT [PK_EntityValue] PRIMARY KEY NONCLUSTERED
(
[EntityValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))')
EXEC('INSERT INTO EntityValue (
[EntityValueId],
[EntityId],
[ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[ImageValue],
[IntValue],
[StringValue],
[StringHash],
[UniqueIdentifierValue],
[PartitionId],
[CollectionKeyId],
[ValueOrder],
[DecimalValue]
)
SELECT
[EntityValueId],
[EntityId],
[ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[ImageValue],
[IntValue],
[StringValue],
CAST(HASHBYTES(''MD5'', [StringValue]) AS INT),
[UniqueIdentifierValue],
[PartitionId],
[CollectionKeyId],
[ValueOrder],
[DecimalValue]
FROM EntityValue_bak')
EXEC('DROP TABLE Entity_bak')
EXEC('DROP TABLE EntityValue_bak')
EXEC('sp_rename ''Changes'', ''Changes_bak''')
CREATE TABLE [Changes](
[ChangesKey] [bigint] IDENTITY(1, 1),
[EntityId] [uniqueidentifier] NOT NULL,
[AdapterId] [uniqueidentifier] NOT NULL,
[ChangeTimestamp] [bigint] NOT NULL,
CONSTRAINT [PK_Changes_Identity] PRIMARY KEY CLUSTERED
(
[ChangesKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
EXEC('INSERT INTO [Changes] (
[EntityId],
[AdapterId],
[ChangeTimestamp])
SELECT
[EntityId],
[AdapterId],
[ChangeTimestamp]
FROM Changes_bak')
EXEC('DROP TABLE Changes_bak')
EXEC('CREATE UNIQUE CLUSTERED INDEX [IX_EntityKey] ON [dbo].[Entity]
(
[EntityKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_ContainerId] ON [dbo].[Entity]
(
[ContainerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_CreatedTime] ON [dbo].[Entity]
(
[CreatedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_ModifiedTime] ON [dbo].[Entity]
(
[ModifiedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('ALTER TABLE [dbo].[Entity] WITH CHECK ADD CONSTRAINT [FK_Entity_Partition] FOREIGN KEY([PartitionId])
REFERENCES [dbo].[Partition] ([PartitionId])
ON UPDATE CASCADE
ON DELETE CASCADE')
EXEC('ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_Partition]')
EXEC('ALTER TABLE [dbo].[Entity] WITH CHECK ADD CONSTRAINT [FK_Entity_ObjectClass] FOREIGN KEY([ObjectClassId])
REFERENCES [dbo].[ObjectClass] ([ObjectClassId])')
EXEC('ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_ObjectClass]')
EXEC('CREATE UNIQUE CLUSTERED INDEX [IX_EntityValueKey] ON [dbo].[EntityValue]
(
[EntityValueKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('ALTER TABLE [dbo].[EntityValue] ADD CONSTRAINT [DF_EntityValue_ValueOrder] DEFAULT ((0)) FOR [ValueOrder]')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_PartitionId] ON [dbo].[Entity]
(
[PartitionId] ASC,
[EntityId] ASC
)
INCLUDE ( [ObjectClassId],
[DN]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)')
EXEC('CREATE NONCLUSTERED INDEX [IX_EntityValue_EntityId_PartitionId] ON [dbo].[EntityValue]
(
[PartitionId] ASC,
[EntityId] ASC
)
INCLUDE ( [CollectionKeyId]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)')
EXEC('CREATE NONCLUSTERED INDEX [IX_EntityValue_ValueTypes] ON [dbo].[EntityValue]
(
[CollectionKeyId] ASC,
[PartitionId] ASC,
[EntityId] ASC,
[EntityValueId] ASC
)
INCLUDE ( [ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[IntValue],
[StringValue],
[UniqueIdentifierValue],
[ValueOrder],
[DecimalValue]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)')
EXEC('CREATE NONCLUSTERED INDEX [IX_EntityValue_StringHash] ON [dbo].[EntityValue]
(
[StringHash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('ALTER TABLE [dbo].[EntityValue] WITH CHECK ADD CONSTRAINT [FK_EntityValue_CollectionKey] FOREIGN KEY([CollectionKeyId])
REFERENCES [dbo].[CollectionKey] ([CollectionKeyId])')
EXEC('ALTER TABLE [dbo].[EntityValue] CHECK CONSTRAINT [FK_EntityValue_CollectionKey]')
EXEC('ALTER TABLE [dbo].[EntityValue] WITH CHECK ADD CONSTRAINT [FK_EntityValue_Entity] FOREIGN KEY([EntityId], [PartitionId])
REFERENCES [dbo].[Entity] ([EntityId], [PartitionId])
ON UPDATE CASCADE
ON DELETE CASCADE')
EXEC('ALTER TABLE [dbo].[EntityValue] CHECK CONSTRAINT [FK_EntityValue_Entity]')
CREATE TABLE [ChangeLog](
[ChangeLogKey] [bigint] IDENTITY(1, 1),
[AdapterId] [uniqueidentifier] NOT NULL,
[ChangeType] [tinyint] NOT NULL,
[ChangeTimestamp] [datetime2] NOT NULL,
[TargetDistinguishedName] [nvarchar](400) NOT NULL,
[NewDistinguishedName] [nvarchar](400) NULL,
[Changes] [nvarchar](MAX) NULL,
CONSTRAINT [PK_ChangeLog_Identity] PRIMARY KEY CLUSTERED
(
[ChangeLogKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
CREATE NONCLUSTERED INDEX [IX_ChangeLog_AdapterId] ON [dbo].[ChangeLog]
(
[AdapterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE [dbo].[ChangeLog] WITH CHECK ADD CONSTRAINT [FK_ChangeLog_Partition] FOREIGN KEY([AdapterId])
REFERENCES [dbo].[Partition] ([PartitionId])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [dbo].[ChangeLog] CHECK CONSTRAINT [FK_ChangeLog_Partition]
IF (SELECT COUNT(*) FROM sys.objects WHERE name = 'Container') > 0
BEGIN
EXEC('DROP TABLE Container')
END
CREATE TABLE [dbo].[Container](
[Id] [hierarchyid] NOT NULL,
[ContainerId] [uniqueidentifier] NOT NULL,
[DistinguishedName] [nvarchar](400) NOT NULL,
[CreatedTime] [datetime2] NOT NULL,
[ModifiedTime] [datetime2] NOT NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[Required] [bit] NOT NULL,
CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Container_ContainerId] ON [dbo].[Container]
(
[ContainerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Container_DistinguishedName] ON [dbo].[Container]
(
[DistinguishedName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_Container_CreatedTime] ON [dbo].[Container]
(
[CreatedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_Container_ModifiedTime] ON [dbo].[Container]
(
[ModifiedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE [dbo].[Entity] WITH CHECK ADD CONSTRAINT [FK_Entity_Container] FOREIGN KEY([ContainerId])
REFERENCES [dbo].[Container] ([ContainerId])
ON DELETE NO ACTION
ON UPDATE NO ACTION
ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_Container]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertEntityValueSelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertEntityValueSelectType')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertChangesSelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertChangesSelectType')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertEntitySelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertEntitySelectType')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertContainerSelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertContainerSelectType')
END
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'EntityValueSelectType')
BEGIN
EXEC('DROP TYPE EntityValueSelectType')
END
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'EntitySelectType')
BEGIN
EXEC('DROP TYPE EntitySelectType')
END
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'ContainerSelectType')
BEGIN
EXEC('DROP TYPE ContainerSelectType')
END
CREATE TYPE [dbo].[EntityValueSelectType] AS TABLE
(
ValueType int NOT NULL,
BigIntValue bigint NULL,
BitValue bit NULL,
FloatValue float NULL,
ImageValue varbinary(MAX) NULL,
IntValue int NULL,
StringValue nvarchar(MAX) NULL,
StringHash int NULL,
UniqueIdentifierValue uniqueidentifier NULL,
EntityValueSelectId uniqueidentifier NOT NULL,
ValueSequence int NOT NULL,
ColumnSequence int NOT NULL,
DecimalValue decimal(38,12) NULL
)
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'ChangesSelectType')
BEGIN
EXEC('DROP TYPE ChangesSelectType')
END
CREATE TYPE [dbo].[ChangesSelectType] AS TABLE
(
[ChangesSelectId] [uniqueidentifier] NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[AdapterId] [uniqueidentifier] NOT NULL,
[ChangeTimestamp] [bigint] NOT NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertEntityValueSelectType]
(@EntityValueSelect as EntityValueSelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #EntityValueSelect (ValueType, BigIntValue, BitValue, FloatValue, ImageValue, IntValue, StringValue, StringHash, UniqueIdentifierValue, EntityValueSelectId, ValueSequence, ColumnSequence, DecimalValue) SELECT ValueType, BigIntValue, BitValue, FloatValue, ImageValue, IntValue, StringValue, StringHash, UniqueIdentifierValue, EntityValueSelectId, ValueSequence, ColumnSequence, DecimalValue FROM @thisEntityValueSelect'', N''@thisEntityValueSelect EntityValueSelectType READONLY'', @EntityValueSelect')
EXEC('CREATE PROCEDURE [dbo].[InsertChangesSelectType]
(@ChangesSelect as ChangesSelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #ChangesSelect (ChangesSelectId, EntityId, AdapterId, ChangeTimestamp) SELECT ChangesSelectId, EntityId, AdapterId, ChangeTimestamp FROM @thisChangesSelect'', N''@thisChangesSelect ChangesSelectType READONLY'', @ChangesSelect')
CREATE TYPE [dbo].[EntitySelectType] AS TABLE
(
[EntitySelectGroupId] uniqueidentifier NOT NULL,
[EntityId] uniqueidentifier NULL,
[PartitionId] uniqueidentifier NULL,
[ContainerId] uniqueidentifier NULL,
[DN] nvarchar(400) NULL,
[ObjectClassId] int NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertEntitySelectType]
(@EntitySelect as EntitySelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #EntitySelect (EntitySelectGroupId, EntityId, PartitionId, ContainerId, DN, ObjectClassId) SELECT EntitySelectGroupId, EntityId, PartitionId, ContainerId, DN, ObjectClassId FROM @thisEntitySelect'', N''@thisEntitySelect EntitySelectType READONLY'', @EntitySelect')
CREATE TYPE [dbo].[ContainerSelectType] AS TABLE
(
[ContainerSelectId] bigint NOT NULL,
[ContainerSelectGroupId] uniqueidentifier NOT NULL,
[ContainerId] uniqueidentifier NULL,
[DistinguishedName] nvarchar(400) NULL,
[PartitionId] uniqueidentifier NULL,
[Required] bit NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertContainerSelectType]
(@ContainerSelect as ContainerSelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #ContainerSelect (ContainerSelectGroupId, ContainerId, DistinguishedName, PartitionId, Required) SELECT ContainerSelectGroupId, ContainerId, DistinguishedName, PartitionId, Required FROM @thisContainerSelect'', N''@thisContainerSelect ContainerSelectType READONLY'', @ContainerSelect')
CREATE TYPE [dbo].[ContainerInsertType] AS TABLE
(
[ContainerId] [uniqueidentifier] NOT NULL,
[DistinguishedName] [nvarchar](400) NOT NULL,
[Level] [int] NOT NULL,
[ParentDistinguishedName] [nvarchar](400) NOT NULL,
[CreatedTime] [datetime2] NOT NULL,
[ModifiedTime] [datetime2] NOT NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[Required] [bit] NOT NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertContainerInsertType]
(@ContainerInsert as ContainerInsertType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #ContainerInsert (ContainerId, DistinguishedName, Level, ParentDistinguishedName, CreatedTime, ModifiedTime, PartitionId, Required) SELECT ContainerId, DistinguishedName, Level, ParentDistinguishedName, CreatedTime, ModifiedTime, PartitionId, Required FROM @thisContainerInsert'', N''@thisContainerInsert ContainerInsertType READONLY'', @ContainerInsert')
EXEC('CREATE PROCEDURE [dbo].[AddChildContainer]
(@ParentDistinguishedName nvarchar(400), @ContainerId uniqueidentifier, @DistinguishedName nvarchar(400), @CreatedTime datetime2, @ModifiedTime datetime2, @PartitionId uniqueidentifier, @Required bit)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Parent hierarchyid;
DECLARE @LastChild hierarchyid;
SELECT @Parent = [Id] FROM [Container] WHERE [DistinguishedName] = @ParentDistinguishedName
SELECT @LastChild = MAX([Id]) FROM [Container] WHERE [Id].GetAncestor(1) = @Parent
INSERT INTO [dbo].[Container] ([Id], [ContainerId], [DistinguishedName], [CreatedTime], [ModifiedTime], [PartitionId], [Required])
VALUES (@Parent.GetDescendant(@LastChild, NULL), @ContainerId, @DistinguishedName, @CreatedTime, @ModifiedTime, @PartitionId, @Required)
END')
EXEC('CREATE PROCEDURE [dbo].[GetContainersBaseLevel] (@DistinguishedName nvarchar(400)) AS
BEGIN
SET NOCOUNT ON;
SELECT [Id].ToString() AS [Path], [Id].GetLevel() AS [Level], [ContainerId], [DistinguishedName] FROM [Container] WHERE [DistinguishedName] = @DistinguishedName
END')
EXEC('CREATE PROCEDURE [dbo].[GetContainersSubTree] (@ParentDistinguishedName nvarchar(400)) AS
BEGIN
SET NOCOUNT ON;
DECLARE @ParentId hierarchyid;
SELECT @ParentId = [Id] FROM [Container] WHERE [DistinguishedName] = @ParentDistinguishedName
SELECT [Id].ToString() AS [Path], [Id].GetLevel() AS [Level], [ContainerId], [DistinguishedName] FROM [Container] WHERE [Id].IsDescendantOf(@ParentId) = 1
END')
EXEC('CREATE PROCEDURE [dbo].[GetContainersOneLevel] (@ParentDistinguishedName nvarchar(400)) AS
BEGIN
SET NOCOUNT ON;
DECLARE @ParentId hierarchyid;
SELECT @ParentId = [Id] FROM [Container] WHERE [DistinguishedName] = @ParentDistinguishedName
SELECT [Id].ToString() AS [Path], [Id].GetLevel() AS [Level], [ContainerId], [DistinguishedName] FROM [Container] WHERE [Id].IsDescendantOf(@ParentId) = 1 AND [Id].GetLevel() <= @ParentId.GetLevel() + 1
END')
EXEC('CREATE PROCEDURE [dbo].[SetRequired] (@DistinguishedName nvarchar(400), @Required bit) AS
BEGIN
UPDATE [dbo].[Container] SET [Required] = @Required WHERE [DistinguishedName] = @DistinguishedName
END')
END
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="MsiInstaller" />
<EventID Qualifiers="0">10005</EventID>
<Level>2</Level>
<Task>0</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2015-09-22T05:01:35.000000000Z" />
<EventRecordID>10649</EventRecordID>
<Channel>Application</Channel>
<Computer>SVR-App1.FIMTest.local</Computer>
<Security UserID="S-1-5-21-3689916871-2134841125-3862386503-1107" />
</System>
<EventData>
<Data>Product: UNIFY Identity Broker Service v5.0.2 RTM x64 -- Error 26204. Error -2147217900: failed to execute SQL string, error detail: Could not drop constraint. See previous errors., SQL key: Script.486dba2a_a6cb_4a23_ba7f_4b179c82a0f8 SQL string: DECLARE @v303 BIT
DECLARE @v50 BIT
SET @v303 = 0
SET @v50 = 0
--NOTE: All versions that require upgrade must be set.
IF (SELECT COUNT(*) FROM sys.columns WHERE name = 'StoredValueCollectionKey') = 0
BEGIN
SET @v303 = 1
SET @v50 = 1
END
IF (SELECT COUNT(*) FROM sys.objects WHERE name = 'SetRequired') = 0
BEGIN
SET @v50 = 1
END
--Clean up--
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'ChangesSelect')
BEGIN
EXEC('DROP TABLE ChangesSelect')
END
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'EntityValueSelect')
BEGIN
EXEC('DROP TABLE EntityValueSelect')
END
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'EntitySelect')
BEGIN
EXEC('DROP TABLE EntitySelect')
END
--Upgrades--
IF @v303 = 1
BEGIN
ALTER TABLE [dbo].[StoredValueCollection] DROP CONSTRAINT [PK_StoredValueCollection]
DROP INDEX Entity._dta_index_Entity_c_7_149575571__K1
DROP INDEX Entity.IX_Entity_ObjectClass_PartitionId
DROP INDEX EntityValue._dta_index_EntityValue_7_181575685__K13_K12_K2_1_3_4_5_6_8_9_11
DROP INDEX EntityValue._dta_index_EntityValue_c_7_181575685__K2
DROP INDEX EntityValue.IX_EntityValue_IntValue
DROP INDEX EntityValue.IX_EntityValue_PartitionId
DROP INDEX EntityValue.IX_EntityValue_StringValue
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_CollectionKey')
BEGIN
EXEC('ALTER TABLE [CollectionKey] DROP CONSTRAINT [PK_CollectionKey]')
END
EXEC('ALTER TABLE [dbo].[CollectionKey] ADD CONSTRAINT [PK_CollectionKey] PRIMARY KEY CLUSTERED ([CollectionKeyId] ASC)')
EXEC('sp_rename ''StoredValueCollection'', ''StoredValueCollection_bak''')
CREATE TABLE [dbo].[StoredValueCollection](
[StoredValueCollectionKey] [bigint] IDENTITY(1,1) NOT NULL,
[StoredValueCollectionId] [uniqueidentifier] NOT NULL,
[Values] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_StoredValueCollection] PRIMARY KEY NONCLUSTERED
(
[StoredValueCollectionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
EXEC('INSERT INTO StoredValueCollection (
[StoredValueCollectionId],
[Values]
)
SELECT
[StoredValueCollectionId],
[Values]
FROM StoredValueCollection_bak
UPDATE StoredValueCollection SET [Values] = REPLACE([Values], ''/Unify.Framework"'', ''/Unify.Framework.StoredValues"'')')
--Update IdentityBroker ValueTypes
UPDATE EntityValue
SET ValueType = 0
WHERE ValueType = 128
UPDATE EntityValue
SET ValueType = POWER(2, (ValueType))
WHERE ValueType < 128
UPDATE EntityValue
SET ValueType = POWER(2, (ValueType - 128)) + 1
WHERE ValueType > 128 AND ValueType < 256
EXEC('CREATE CLUSTERED INDEX [IX_StoredValueCollectionKey] ON [dbo].[StoredValueCollection]
(
[StoredValueCollectionKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
END
IF @v50 = 1
BEGIN
IF (SELECT COUNT(*) FROM sys.views WHERE name = 'AdapterEntityChanges') > 0
BEGIN
EXEC('DROP VIEW AdapterEntityChanges')
END
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_Changes_Identity')
BEGIN
EXEC('ALTER TABLE [Changes] DROP CONSTRAINT [PK_Changes_Identity]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_EntityValue_Entity')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [FK_EntityValue_Entity]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_EntityValue_CollectionKey')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [FK_EntityValue_CollectionKey]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_Entity_ObjectClass')
BEGIN
EXEC('ALTER TABLE [Entity] DROP CONSTRAINT [FK_Entity_ObjectClass]')
END
IF EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_Entity_Partition')
BEGIN
EXEC('ALTER TABLE [Entity] DROP CONSTRAINT [FK_Entity_Partition]')
END
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_Entity')
BEGIN
EXEC('ALTER TABLE [Entity] DROP CONSTRAINT [PK_Entity]')
END
IF EXISTS (SELECT * FROM sys.key_constraints WHERE name = 'PK_EntityValue')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [PK_EntityValue]')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EntityValueKey')
BEGIN
EXEC('DROP INDEX EntityValue.IX_EntityValueKey')
END
IF EXISTS (SELECT * FROM sys.default_constraints WHERE name = 'DF_EntityValue_ValueOrder')
BEGIN
EXEC('ALTER TABLE [EntityValue] DROP CONSTRAINT [DF_EntityValue_ValueOrder]')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_Entity_PartitionId')
BEGIN
EXEC('DROP INDEX Entity.IX_Entity_PartitionId')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EntityValue_EntityId_PartitionId')
BEGIN
EXEC('DROP INDEX EntityValue.IX_EntityValue_EntityId_PartitionId')
END
IF EXISTS (SELECT * FROM sys.indexes WHERE name = 'IX_EntityValue_ValueTypes')
BEGIN
EXEC('DROP INDEX EntityValue.IX_EntityValue_ValueTypes')
END
EXEC('sp_rename ''Entity'', ''Entity_bak''')
EXEC('CREATE TABLE [dbo].[Entity](
[EntityKey] [bigint] IDENTITY(1,1) NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[CreatedTime] [datetime2] NULL,
[ModifiedTime] [datetime2] NULL,
[ContainerId] [uniqueidentifier] NULL,
[ObjectClassId] [int] NULL,
[DN] [nvarchar](400) NULL,
CONSTRAINT [PK_Entity] PRIMARY KEY NONCLUSTERED
(
[EntityId] ASC,
[PartitionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)')
EXEC('INSERT INTO Entity (
[EntityId],
[PartitionId],
[CreatedTime],
[ModifiedTime],
[ObjectClassId],
[DN]
)
SELECT
[EntityId],
[PartitionId],
GETDATE(),
GETDATE(),
[ObjectClassId],
[DN] [nvarchar]
FROM Entity_bak')
EXEC('sp_rename ''EntityValue'', ''EntityValue_bak''')
EXEC('CREATE TABLE [dbo].[EntityValue](
[EntityValueKey] [bigint] IDENTITY(1,1) NOT NULL,
[EntityValueId] [uniqueidentifier] NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[ValueType] [int] NOT NULL,
[BigIntValue] [bigint] NULL,
[BitValue] [bit] NULL,
[FloatValue] [float] NULL,
[ImageValue] [varbinary](max) NULL,
[IntValue] [int] NULL,
[StringValue] [nvarchar](max) NULL,
[StringHash] [int] NULL,
[UniqueIdentifierValue] [uniqueidentifier] NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[CollectionKeyId] [int] NOT NULL,
[ValueOrder] [int] NOT NULL,
[DecimalValue] [decimal](38, 12) NULL,
CONSTRAINT [PK_EntityValue] PRIMARY KEY NONCLUSTERED
(
[EntityValueId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON))')
EXEC('INSERT INTO EntityValue (
[EntityValueId],
[EntityId],
[ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[ImageValue],
[IntValue],
[StringValue],
[StringHash],
[UniqueIdentifierValue],
[PartitionId],
[CollectionKeyId],
[ValueOrder],
[DecimalValue]
)
SELECT
[EntityValueId],
[EntityId],
[ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[ImageValue],
[IntValue],
[StringValue],
CAST(HASHBYTES(''MD5'', [StringValue]) AS INT),
[UniqueIdentifierValue],
[PartitionId],
[CollectionKeyId],
[ValueOrder],
[DecimalValue]
FROM EntityValue_bak')
EXEC('DROP TABLE Entity_bak')
EXEC('DROP TABLE EntityValue_bak')
EXEC('sp_rename ''Changes'', ''Changes_bak''')
CREATE TABLE [Changes](
[ChangesKey] [bigint] IDENTITY(1, 1),
[EntityId] [uniqueidentifier] NOT NULL,
[AdapterId] [uniqueidentifier] NOT NULL,
[ChangeTimestamp] [bigint] NOT NULL,
CONSTRAINT [PK_Changes_Identity] PRIMARY KEY CLUSTERED
(
[ChangesKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
EXEC('INSERT INTO [Changes] (
[EntityId],
[AdapterId],
[ChangeTimestamp])
SELECT
[EntityId],
[AdapterId],
[ChangeTimestamp]
FROM Changes_bak')
EXEC('DROP TABLE Changes_bak')
EXEC('CREATE UNIQUE CLUSTERED INDEX [IX_EntityKey] ON [dbo].[Entity]
(
[EntityKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_ContainerId] ON [dbo].[Entity]
(
[ContainerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_CreatedTime] ON [dbo].[Entity]
(
[CreatedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_ModifiedTime] ON [dbo].[Entity]
(
[ModifiedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('ALTER TABLE [dbo].[Entity] WITH CHECK ADD CONSTRAINT [FK_Entity_Partition] FOREIGN KEY([PartitionId])
REFERENCES [dbo].[Partition] ([PartitionId])
ON UPDATE CASCADE
ON DELETE CASCADE')
EXEC('ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_Partition]')
EXEC('ALTER TABLE [dbo].[Entity] WITH CHECK ADD CONSTRAINT [FK_Entity_ObjectClass] FOREIGN KEY([ObjectClassId])
REFERENCES [dbo].[ObjectClass] ([ObjectClassId])')
EXEC('ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_ObjectClass]')
EXEC('CREATE UNIQUE CLUSTERED INDEX [IX_EntityValueKey] ON [dbo].[EntityValue]
(
[EntityValueKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('ALTER TABLE [dbo].[EntityValue] ADD CONSTRAINT [DF_EntityValue_ValueOrder] DEFAULT ((0)) FOR [ValueOrder]')
EXEC('CREATE NONCLUSTERED INDEX [IX_Entity_PartitionId] ON [dbo].[Entity]
(
[PartitionId] ASC,
[EntityId] ASC
)
INCLUDE ( [ObjectClassId],
[DN]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)')
EXEC('CREATE NONCLUSTERED INDEX [IX_EntityValue_EntityId_PartitionId] ON [dbo].[EntityValue]
(
[PartitionId] ASC,
[EntityId] ASC
)
INCLUDE ( [CollectionKeyId]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)')
EXEC('CREATE NONCLUSTERED INDEX [IX_EntityValue_ValueTypes] ON [dbo].[EntityValue]
(
[CollectionKeyId] ASC,
[PartitionId] ASC,
[EntityId] ASC,
[EntityValueId] ASC
)
INCLUDE ( [ValueType],
[BigIntValue],
[BitValue],
[FloatValue],
[IntValue],
[StringValue],
[UniqueIdentifierValue],
[ValueOrder],
[DecimalValue]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)')
EXEC('CREATE NONCLUSTERED INDEX [IX_EntityValue_StringHash] ON [dbo].[EntityValue]
(
[StringHash] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)')
EXEC('ALTER TABLE [dbo].[EntityValue] WITH CHECK ADD CONSTRAINT [FK_EntityValue_CollectionKey] FOREIGN KEY([CollectionKeyId])
REFERENCES [dbo].[CollectionKey] ([CollectionKeyId])')
EXEC('ALTER TABLE [dbo].[EntityValue] CHECK CONSTRAINT [FK_EntityValue_CollectionKey]')
EXEC('ALTER TABLE [dbo].[EntityValue] WITH CHECK ADD CONSTRAINT [FK_EntityValue_Entity] FOREIGN KEY([EntityId], [PartitionId])
REFERENCES [dbo].[Entity] ([EntityId], [PartitionId])
ON UPDATE CASCADE
ON DELETE CASCADE')
EXEC('ALTER TABLE [dbo].[EntityValue] CHECK CONSTRAINT [FK_EntityValue_Entity]')
CREATE TABLE [ChangeLog](
[ChangeLogKey] [bigint] IDENTITY(1, 1),
[AdapterId] [uniqueidentifier] NOT NULL,
[ChangeType] [tinyint] NOT NULL,
[ChangeTimestamp] [datetime2] NOT NULL,
[TargetDistinguishedName] [nvarchar](400) NOT NULL,
[NewDistinguishedName] [nvarchar](400) NULL,
[Changes] [nvarchar](MAX) NULL,
CONSTRAINT [PK_ChangeLog_Identity] PRIMARY KEY CLUSTERED
(
[ChangeLogKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
CREATE NONCLUSTERED INDEX [IX_ChangeLog_AdapterId] ON [dbo].[ChangeLog]
(
[AdapterId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE [dbo].[ChangeLog] WITH CHECK ADD CONSTRAINT [FK_ChangeLog_Partition] FOREIGN KEY([AdapterId])
REFERENCES [dbo].[Partition] ([PartitionId])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [dbo].[ChangeLog] CHECK CONSTRAINT [FK_ChangeLog_Partition]
IF (SELECT COUNT(*) FROM sys.objects WHERE name = 'Container') > 0
BEGIN
EXEC('DROP TABLE Container')
END
CREATE TABLE [dbo].[Container](
[Id] [hierarchyid] NOT NULL,
[ContainerId] [uniqueidentifier] NOT NULL,
[DistinguishedName] [nvarchar](400) NOT NULL,
[CreatedTime] [datetime2] NOT NULL,
[ModifiedTime] [datetime2] NOT NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[Required] [bit] NOT NULL,
CONSTRAINT [PK_Container] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Container_ContainerId] ON [dbo].[Container]
(
[ContainerId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE UNIQUE NONCLUSTERED INDEX [IX_Container_DistinguishedName] ON [dbo].[Container]
(
[DistinguishedName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_Container_CreatedTime] ON [dbo].[Container]
(
[CreatedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
CREATE NONCLUSTERED INDEX [IX_Container_ModifiedTime] ON [dbo].[Container]
(
[ModifiedTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ALTER TABLE [dbo].[Entity] WITH CHECK ADD CONSTRAINT [FK_Entity_Container] FOREIGN KEY([ContainerId])
REFERENCES [dbo].[Container] ([ContainerId])
ON DELETE NO ACTION
ON UPDATE NO ACTION
ALTER TABLE [dbo].[Entity] CHECK CONSTRAINT [FK_Entity_Container]
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertEntityValueSelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertEntityValueSelectType')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertChangesSelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertChangesSelectType')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertEntitySelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertEntitySelectType')
END
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('InsertContainerSelectType'))
BEGIN
EXEC('DROP PROCEDURE InsertContainerSelectType')
END
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'EntityValueSelectType')
BEGIN
EXEC('DROP TYPE EntityValueSelectType')
END
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'EntitySelectType')
BEGIN
EXEC('DROP TYPE EntitySelectType')
END
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'ContainerSelectType')
BEGIN
EXEC('DROP TYPE ContainerSelectType')
END
CREATE TYPE [dbo].[EntityValueSelectType] AS TABLE
(
ValueType int NOT NULL,
BigIntValue bigint NULL,
BitValue bit NULL,
FloatValue float NULL,
ImageValue varbinary(MAX) NULL,
IntValue int NULL,
StringValue nvarchar(MAX) NULL,
StringHash int NULL,
UniqueIdentifierValue uniqueidentifier NULL,
EntityValueSelectId uniqueidentifier NOT NULL,
ValueSequence int NOT NULL,
ColumnSequence int NOT NULL,
DecimalValue decimal(38,12) NULL
)
IF EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name = 'ChangesSelectType')
BEGIN
EXEC('DROP TYPE ChangesSelectType')
END
CREATE TYPE [dbo].[ChangesSelectType] AS TABLE
(
[ChangesSelectId] [uniqueidentifier] NOT NULL,
[EntityId] [uniqueidentifier] NOT NULL,
[AdapterId] [uniqueidentifier] NOT NULL,
[ChangeTimestamp] [bigint] NOT NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertEntityValueSelectType]
(@EntityValueSelect as EntityValueSelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #EntityValueSelect (ValueType, BigIntValue, BitValue, FloatValue, ImageValue, IntValue, StringValue, StringHash, UniqueIdentifierValue, EntityValueSelectId, ValueSequence, ColumnSequence, DecimalValue) SELECT ValueType, BigIntValue, BitValue, FloatValue, ImageValue, IntValue, StringValue, StringHash, UniqueIdentifierValue, EntityValueSelectId, ValueSequence, ColumnSequence, DecimalValue FROM @thisEntityValueSelect'', N''@thisEntityValueSelect EntityValueSelectType READONLY'', @EntityValueSelect')
EXEC('CREATE PROCEDURE [dbo].[InsertChangesSelectType]
(@ChangesSelect as ChangesSelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #ChangesSelect (ChangesSelectId, EntityId, AdapterId, ChangeTimestamp) SELECT ChangesSelectId, EntityId, AdapterId, ChangeTimestamp FROM @thisChangesSelect'', N''@thisChangesSelect ChangesSelectType READONLY'', @ChangesSelect')
CREATE TYPE [dbo].[EntitySelectType] AS TABLE
(
[EntitySelectGroupId] uniqueidentifier NOT NULL,
[EntityId] uniqueidentifier NULL,
[PartitionId] uniqueidentifier NULL,
[ContainerId] uniqueidentifier NULL,
[DN] nvarchar(400) NULL,
[ObjectClassId] int NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertEntitySelectType]
(@EntitySelect as EntitySelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #EntitySelect (EntitySelectGroupId, EntityId, PartitionId, ContainerId, DN, ObjectClassId) SELECT EntitySelectGroupId, EntityId, PartitionId, ContainerId, DN, ObjectClassId FROM @thisEntitySelect'', N''@thisEntitySelect EntitySelectType READONLY'', @EntitySelect')
CREATE TYPE [dbo].[ContainerSelectType] AS TABLE
(
[ContainerSelectId] bigint NOT NULL,
[ContainerSelectGroupId] uniqueidentifier NOT NULL,
[ContainerId] uniqueidentifier NULL,
[DistinguishedName] nvarchar(400) NULL,
[PartitionId] uniqueidentifier NULL,
[Required] bit NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertContainerSelectType]
(@ContainerSelect as ContainerSelectType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #ContainerSelect (ContainerSelectGroupId, ContainerId, DistinguishedName, PartitionId, Required) SELECT ContainerSelectGroupId, ContainerId, DistinguishedName, PartitionId, Required FROM @thisContainerSelect'', N''@thisContainerSelect ContainerSelectType READONLY'', @ContainerSelect')
CREATE TYPE [dbo].[ContainerInsertType] AS TABLE
(
[ContainerId] [uniqueidentifier] NOT NULL,
[DistinguishedName] [nvarchar](400) NOT NULL,
[Level] [int] NOT NULL,
[ParentDistinguishedName] [nvarchar](400) NOT NULL,
[CreatedTime] [datetime2] NOT NULL,
[ModifiedTime] [datetime2] NOT NULL,
[PartitionId] [uniqueidentifier] NOT NULL,
[Required] [bit] NOT NULL
)
EXEC('CREATE PROCEDURE [dbo].[InsertContainerInsertType]
(@ContainerInsert as ContainerInsertType READONLY)
AS
EXECUTE sp_executesql N''INSERT INTO #ContainerInsert (ContainerId, DistinguishedName, Level, ParentDistinguishedName, CreatedTime, ModifiedTime, PartitionId, Required) SELECT ContainerId, DistinguishedName, Level, ParentDistinguishedName, CreatedTime, ModifiedTime, PartitionId, Required FROM @thisContainerInsert'', N''@thisContainerInsert ContainerInsertType READONLY'', @ContainerInsert')
EXEC('CREATE PROCEDURE [dbo].[AddChildContainer]
(@ParentDistinguishedName nvarchar(400), @ContainerId uniqueidentifier, @DistinguishedName nvarchar(400), @CreatedTime datetime2, @ModifiedTime datetime2, @PartitionId uniqueidentifier, @Required bit)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Parent hierarchyid;
DECLARE @LastChild hierarchyid;
SELECT @Parent = [Id] FROM [Container] WHERE [DistinguishedName] = @ParentDistinguishedName
SELECT @LastChild = MAX([Id]) FROM [Container] WHERE [Id].GetAncestor(1) = @Parent
INSERT INTO [dbo].[Container] ([Id], [ContainerId], [DistinguishedName], [CreatedTime], [ModifiedTime], [PartitionId], [Required])
VALUES (@Parent.GetDescendant(@LastChild, NULL), @ContainerId, @DistinguishedName, @CreatedTime, @ModifiedTime, @PartitionId, @Required)
END')
EXEC('CREATE PROCEDURE [dbo].[GetContainersBaseLevel] (@DistinguishedName nvarchar(400)) AS
BEGIN
SET NOCOUNT ON;
SELECT [Id].ToString() AS [Path], [Id].GetLevel() AS [Level], [ContainerId], [DistinguishedName] FROM [Container] WHERE [DistinguishedName] = @DistinguishedName
END')
EXEC('CREATE PROCEDURE [dbo].[GetContainersSubTree] (@ParentDistinguishedName nvarchar(400)) AS
BEGIN
SET NOCOUNT ON;
DECLARE @ParentId hierarchyid;
SELECT @ParentId = [Id] FROM [Container] WHERE [DistinguishedName] = @ParentDistinguishedName
SELECT [Id].ToString() AS [Path], [Id].GetLevel() AS [Level], [ContainerId], [DistinguishedName] FROM [Container] WHERE [Id].IsDescendantOf(@ParentId) = 1
END')
EXEC('CREATE PROCEDURE [dbo].[GetContainersOneLevel] (@ParentDistinguishedName nvarchar(400)) AS
BEGIN
SET NOCOUNT ON;
DECLARE @ParentId hierarchyid;
SELECT @ParentId = [Id] FROM [Container] WHERE [DistinguishedName] = @ParentDistinguishedName
SELECT [Id].ToString() AS [Path], [Id].GetLevel() AS [Level], [ContainerId], [DistinguishedName] FROM [Container] WHERE [Id].IsDescendantOf(@ParentId) = 1 AND [Id].GetLevel() <= @ParentId.GetLevel() + 1
END')
EXEC('CREATE PROCEDURE [dbo].[SetRequired] (@DistinguishedName nvarchar(400), @Required bit) AS
BEGIN
UPDATE [dbo].[Container] SET [Required] = @Required WHERE [DistinguishedName] = @DistinguishedName
END')
END</Data>
<Data>(NULL)</Data>
<Data>(NULL)</Data>
<Data>(NULL)</Data>
<Data>(NULL)</Data>
<Data>(NULL)</Data>
<Data>
</Data>
<Binary>7B32334231463730342D383638372D343543352D383830412D4341363730333541413532467D</Binary>
</EventData>
</Event>
IdentityBroker Upgrade.sql
image002.png
image-2015-09-22-15-06-35-793.png
screenshot-1.png
UnifyIdBroker.sql
0
Answered
Creating IdB 5 ECMA2 receive error: "Could not load file or assembly 'Microsoft.MetadirectoryServicesEx, Version=4.1.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
André van der Westhuizen 9 years ago
in UNIFYBroker/Microsoft Identity Manager
•
updated by anonymous 8 years ago •
5
I have installed the UNIFY Identity Broker for Microsoft FIM v5.0.2 RTM x64.msi and have copy the Unify.IdentityBroker.FIMAdapter.dll to the extension directory.
I'm creating the ECMA 2 MA to be used with Unify.IdentityBroker.FIMAdapter.dll, as per https://unifysolutions.jira.com/wiki/display/IDBFIM50/Management+Agent I receive the following error:
I receive the following error in the event viewer:
Log Name: Application
Source: FIMSynchronizationService
Date: 8/25/2015 4:00:04 PM
Event ID: 6300
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: SVR-FIM.FIMTest.local
Description:
The server encountered an unexpected error:
"Could not load file or assembly 'Microsoft.MetadirectoryServicesEx, Version=4.1.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
at System.Reflection.RuntimeAssembly.GetExportedTypes(RuntimeAssembly assembly, ObjectHandleOnStack retTypes)
at System.Reflection.RuntimeAssembly.GetExportedTypes()
at Microsoft.MetadirectoryServices.Impl.ScriptHost.InitializeWorker(InitializeArguments pArgs)
InnerException=>
none
"
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="FIMSynchronizationService" />
<EventID Qualifiers="49152">6300</EventID>
<Level>2</Level>
<Task>3</Task>
<Keywords>0x80000000000000</Keywords>
<TimeCreated SystemTime="2015-08-25T06:00:04.000000000Z" />
<EventRecordID>14368</EventRecordID>
<Channel>Application</Channel>
<Computer>SVR-FIM.FIMTest.local</Computer>
<Security />
</System>
<EventData>
<Data>Could not load file or assembly 'Microsoft.MetadirectoryServicesEx, Version=4.1.3.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
at System.Reflection.RuntimeAssembly.GetExportedTypes(RuntimeAssembly assembly, ObjectHandleOnStack retTypes)
at System.Reflection.RuntimeAssembly.GetExportedTypes()
at Microsoft.MetadirectoryServices.Impl.ScriptHost.InitializeWorker(InitializeArguments pArgs)
InnerException=>
none
</Data>
</EventData>
</Event>
image.png
image1.PNG
miiserver.exe.config
Customer support service by UserEcho
