January 18, 2017 at 12:37 am
hello all.
I have two Same Data base in structure not in Data( as Test1,Test2) and one Data base with different structure as Test3.
in addition I Have 3 Tables (Users,Roles,Groups) in Test1,2,3 .Now I need to sync this 3 Tables in Test3 from Test1,2.
in addition Test1,3 is on server1 and Test2 is on Server2.
Which the best way can help me?
thanks<
January 18, 2017 at 2:34 am
elhamazizi5456 - Wednesday, January 18, 2017 12:37 AMhello all.
I have two Same Data base in structure not in Data( as Test1,Test2) and one Data base with different structure as Test3.
in addition I Have 3 Tables (Users,Roles,Groups) in Test1,2,3 .Now I need to sync this 3 Tables in Test3 from Test1,2.
in addition Test1,3 is on server1 and Test2 is on Server2.
Which the best way can help me?
thanks<
Is this a one-off exercise, or a regular occurrence? Are the table definitions for the three tables identical on all three servers? Please post DDL for the tables, including constraints, and perhaps a few rows of sample data for each.
John
January 18, 2017 at 3:08 am
John Mitchell-245523 - Wednesday, January 18, 2017 2:34 AMelhamazizi5456 - Wednesday, January 18, 2017 12:37 AMhello all.
I have two Same Data base in structure not in Data( as Test1,Test2) and one Data base with different structure as Test3.
in addition I Have 3 Tables (Users,Roles,Groups) in Test1,2,3 .Now I need to sync this 3 Tables in Test3 from Test1,2.
in addition Test1,3 is on server1 and Test2 is on Server2.
Which the best way can help me?
thanks<Is this a one-off exercise, or a regular occurrence? Are the table definitions for the three tables identical on all three servers? Please post DDL for the tables, including constraints, and perhaps a few rows of sample data for each.
John
hi thank you for your reply
It is a regular occurrence and The table definitions for this tables are the same in test1,2 and different in Test3.
this code in test1,test2:
CREATE TABLE [Office].[Users](
[Id] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[UserName] [NVARCHAR](150) NOT NULL,
[Password] [NVARCHAR](100) NOT NULL,
[Enabled] [BIT] NOT NULL CONSTRAINT [DF_Users_Enabled] DEFAULT ((1)),
[ChangePasswordOnFirstLogin] [BIT] NOT NULL CONSTRAINT [DF_Users_ChangePasswordOnFirstLogin] DEFAULT ((1)),
[PasswordComplexity] [BIT] NOT NULL CONSTRAINT [DF_Users_PasswordComplexity] DEFAULT ((0)),
[DeveloperAccount] [BIT] NOT NULL CONSTRAINT [DF_Users_DeveloperAccount] DEFAULT ((0)),
[Ticket] [VARCHAR](100) NULL,
[DefaultActorId] [INT] NULL,
[LastLoginTime] [DATETIME2](1) NULL,
[LastLogoutTime] [DATETIME2](1) NULL,
[CreationDate] [DATETIME2](1) NOT NULL CONSTRAINT [DF_UsersInfo_CreationDate] DEFAULT (GETDATE()),
[FirstName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NULL,
[NationalCode] [VARCHAR](10) NULL,
[Sex] [NVARCHAR](20) NULL,
[BirthDate] [DATE] NULL,
[BirthState] [INT] NULL,
[BirthCity] [INT] NULL,
[IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
[IsLocked] [BIT] NOT NULL CONSTRAINT [DF_Users_IsLocked] DEFAULT ((0)),
[LockTime] [DATETIME] NULL,
[LoginRetries] [INT] NOT NULL CONSTRAINT [DF_Users_LoginRetries] DEFAULT ((0)),
CONSTRAINT [PK_UsersInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
this code in test3:
CREATE TABLE [dbo].[Users](
[User_ID] [INT] IDENTITY(1,1) NOT NULL,
[FirstName] [NVARCHAR](100) NULL,
[LastName] [NVARCHAR](100) NULL,
[UserName] [NVARCHAR](100) NULL,
[Password] [NVARCHAR](100) NULL,
[NewPassword] [NVARCHAR](100) NULL,
[IsActive] [BIT] NOT NULL CONSTRAINT [DF_Users_IsActive] DEFAULT ((1)),
[IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
[IsConfirm] [BIT] NULL CONSTRAINT [DF_Users_IsConfirm] DEFAULT ((0)),
[LastLoginTime] [DATETIME] NULL,
[Gender] [BIT] NULL,
[MarriedStatus] [BIT] NULL,
[BirthDate] [DATETIME] NULL,
[CountryNo] [SMALLINT] NULL,
[LanguageNo] [TINYINT] NULL,
[Address] [NVARCHAR](250) NULL,
[E_Mail] [NVARCHAR](100) NULL,
[PhotoPath] [NVARCHAR](200) NULL,
[CreationDate] [DATETIME] NULL CONSTRAINT [DF_Users_CreationDate] DEFAULT (GETDATE()),
[EditionDate] [DATETIME] NULL,
[AcceptXML] [NTEXT] NULL,
[LastRefreshTime] [DATETIME] NULL CONSTRAINT [DF_Users_LastRefreshTime] DEFAULT (GETDATE()),
[LastChangePassDate] [DATETIME] NULL,
[NativeID] [NVARCHAR](200) NULL CONSTRAINT [DF_Users_NativeID] DEFAULT (''),
[PersonnelID] [NVARCHAR](200) NULL,
[CRC] [CHAR](40) NULL,
[ActiveSessionID] [VARCHAR](200) NULL,
[SystemProfile] [XML] NULL,
[IPLocation] [VARCHAR](20) NULL,
[DepartmentID] [INT] NOT NULL CONSTRAINT [DF_Users_DepartmentID] DEFAULT ((-1)),
[Phone] [VARCHAR](20) NULL,
[Mobile] [VARCHAR](20) NULL,
[UserType] [INT] NULL,
[Title] [NVARCHAR](100) NULL,
[Description] [NVARCHAR](2048) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[User_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
It should be noted that Test1 , test3 database are on Server1 and Test2 DataBase is on server 2.
January 18, 2017 at 3:29 am
elhamazizi5456 - Wednesday, January 18, 2017 3:08 AMJohn Mitchell-245523 - Wednesday, January 18, 2017 2:34 AMelhamazizi5456 - Wednesday, January 18, 2017 12:37 AMhello all.
I have two Same Data base in structure not in Data( as Test1,Test2) and one Data base with different structure as Test3.
in addition I Have 3 Tables (Users,Roles,Groups) in Test1,2,3 .Now I need to sync this 3 Tables in Test3 from Test1,2.
in addition Test1,3 is on server1 and Test2 is on Server2.
Which the best way can help me?
thanks<Is this a one-off exercise, or a regular occurrence? Are the table definitions for the three tables identical on all three servers? Please post DDL for the tables, including constraints, and perhaps a few rows of sample data for each.
John
hi thank you for your reply
It is a regular occurrence and The table definitions for this tables are the same in test1,2 and different in Test3.
this code in test1,test2:
CREATE TABLE [Office].[Users](
[Id] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[UserName] [NVARCHAR](150) NOT NULL,
[Password] [NVARCHAR](100) NOT NULL,
[Enabled] [BIT] NOT NULL CONSTRAINT [DF_Users_Enabled] DEFAULT ((1)),
[ChangePasswordOnFirstLogin] [BIT] NOT NULL CONSTRAINT [DF_Users_ChangePasswordOnFirstLogin] DEFAULT ((1)),
[PasswordComplexity] [BIT] NOT NULL CONSTRAINT [DF_Users_PasswordComplexity] DEFAULT ((0)),
[DeveloperAccount] [BIT] NOT NULL CONSTRAINT [DF_Users_DeveloperAccount] DEFAULT ((0)),
[Ticket] [VARCHAR](100) NULL,
[DefaultActorId] [INT] NULL,
[LastLoginTime] [DATETIME2](1) NULL,
[LastLogoutTime] [DATETIME2](1) NULL,
[CreationDate] [DATETIME2](1) NOT NULL CONSTRAINT [DF_UsersInfo_CreationDate] DEFAULT (GETDATE()),
[FirstName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NULL,
[NationalCode] [VARCHAR](10) NULL,
[Sex] [NVARCHAR](20) NULL,
[BirthDate] [DATE] NULL,
[BirthState] [INT] NULL,
[BirthCity] [INT] NULL,
[IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
[IsLocked] [BIT] NOT NULL CONSTRAINT [DF_Users_IsLocked] DEFAULT ((0)),
[LockTime] [DATETIME] NULL,
[LoginRetries] [INT] NOT NULL CONSTRAINT [DF_Users_LoginRetries] DEFAULT ((0)),
CONSTRAINT [PK_UsersInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]this code in test3:
CREATE TABLE [dbo].[Users](
[User_ID] [INT] IDENTITY(1,1) NOT NULL,
[FirstName] [NVARCHAR](100) NULL,
[LastName] [NVARCHAR](100) NULL,
[UserName] [NVARCHAR](100) NULL,
[Password] [NVARCHAR](100) NULL,
[NewPassword] [NVARCHAR](100) NULL,
[IsActive] [BIT] NOT NULL CONSTRAINT [DF_Users_IsActive] DEFAULT ((1)),
[IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
[IsConfirm] [BIT] NULL CONSTRAINT [DF_Users_IsConfirm] DEFAULT ((0)),
[LastLoginTime] [DATETIME] NULL,
[Gender] [BIT] NULL,
[MarriedStatus] [BIT] NULL,
[BirthDate] [DATETIME] NULL,
[CountryNo] [SMALLINT] NULL,
[LanguageNo] [TINYINT] NULL,
[Address] [NVARCHAR](250) NULL,
[E_Mail] [NVARCHAR](100) NULL,
[PhotoPath] [NVARCHAR](200) NULL,
[CreationDate] [DATETIME] NULL CONSTRAINT [DF_Users_CreationDate] DEFAULT (GETDATE()),
[EditionDate] [DATETIME] NULL,
[AcceptXML] [NTEXT] NULL,
[LastRefreshTime] [DATETIME] NULL CONSTRAINT [DF_Users_LastRefreshTime] DEFAULT (GETDATE()),
[LastChangePassDate] [DATETIME] NULL,
[NativeID] [NVARCHAR](200) NULL CONSTRAINT [DF_Users_NativeID] DEFAULT (''),
[PersonnelID] [NVARCHAR](200) NULL,
[CRC] [CHAR](40) NULL,
[ActiveSessionID] [VARCHAR](200) NULL,
[SystemProfile] [XML] NULL,
[IPLocation] [VARCHAR](20) NULL,
[DepartmentID] [INT] NOT NULL CONSTRAINT [DF_Users_DepartmentID] DEFAULT ((-1)),
[Phone] [VARCHAR](20) NULL,
[Mobile] [VARCHAR](20) NULL,
[UserType] [INT] NULL,
[Title] [NVARCHAR](100) NULL,
[Description] [NVARCHAR](2048) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[User_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
It should be noted that Test1 , test3 database are on Server1 and Test2 DataBase is on server 2.
That's only one of the tables, but OK, let's start with that. Your source table has considerably fewer columns than your destination. Have you decided how you're going to map the columns between the two tables, and what values you're going to put in the destination columns that don't map? Are there any foreign key constraints? Is there already any data in the destination table? Do you want the destination table to have the same values of User_ID as the values of Id in the source tables? Do you even need the User_ID column at all, given that there appear to be no foreign key constraints? Are any values of Id duplicated between Test1 and Test2? Are any identical users defined in both Test1 and Test2? Do you really store passwords in plain text? What is each database actually used for?
You might consider going back to the drawing board and redesigning the whole thing, because it looks as if you're trying to force the proverbial square peg into a round hole.
John
January 18, 2017 at 3:48 am
John Mitchell-245523 - Wednesday, January 18, 2017 3:29 AMelhamazizi5456 - Wednesday, January 18, 2017 3:08 AMJohn Mitchell-245523 - Wednesday, January 18, 2017 2:34 AMelhamazizi5456 - Wednesday, January 18, 2017 12:37 AMhello all.
I have two Same Data base in structure not in Data( as Test1,Test2) and one Data base with different structure as Test3.
in addition I Have 3 Tables (Users,Roles,Groups) in Test1,2,3 .Now I need to sync this 3 Tables in Test3 from Test1,2.
in addition Test1,3 is on server1 and Test2 is on Server2.
Which the best way can help me?
thanks<Is this a one-off exercise, or a regular occurrence? Are the table definitions for the three tables identical on all three servers? Please post DDL for the tables, including constraints, and perhaps a few rows of sample data for each.
John
hi thank you for your reply
It is a regular occurrence and The table definitions for this tables are the same in test1,2 and different in Test3.
this code in test1,test2:
CREATE TABLE [Office].[Users](
[Id] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[UserName] [NVARCHAR](150) NOT NULL,
[Password] [NVARCHAR](100) NOT NULL,
[Enabled] [BIT] NOT NULL CONSTRAINT [DF_Users_Enabled] DEFAULT ((1)),
[ChangePasswordOnFirstLogin] [BIT] NOT NULL CONSTRAINT [DF_Users_ChangePasswordOnFirstLogin] DEFAULT ((1)),
[PasswordComplexity] [BIT] NOT NULL CONSTRAINT [DF_Users_PasswordComplexity] DEFAULT ((0)),
[DeveloperAccount] [BIT] NOT NULL CONSTRAINT [DF_Users_DeveloperAccount] DEFAULT ((0)),
[Ticket] [VARCHAR](100) NULL,
[DefaultActorId] [INT] NULL,
[LastLoginTime] [DATETIME2](1) NULL,
[LastLogoutTime] [DATETIME2](1) NULL,
[CreationDate] [DATETIME2](1) NOT NULL CONSTRAINT [DF_UsersInfo_CreationDate] DEFAULT (GETDATE()),
[FirstName] [NVARCHAR](50) NULL,
[LastName] [NVARCHAR](50) NULL,
[NationalCode] [VARCHAR](10) NULL,
[Sex] [NVARCHAR](20) NULL,
[BirthDate] [DATE] NULL,
[BirthState] [INT] NULL,
[BirthCity] [INT] NULL,
[IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
[IsLocked] [BIT] NOT NULL CONSTRAINT [DF_Users_IsLocked] DEFAULT ((0)),
[LockTime] [DATETIME] NULL,
[LoginRetries] [INT] NOT NULL CONSTRAINT [DF_Users_LoginRetries] DEFAULT ((0)),
CONSTRAINT [PK_UsersInfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]this code in test3:
CREATE TABLE [dbo].[Users](
[User_ID] [INT] IDENTITY(1,1) NOT NULL,
[FirstName] [NVARCHAR](100) NULL,
[LastName] [NVARCHAR](100) NULL,
[UserName] [NVARCHAR](100) NULL,
[Password] [NVARCHAR](100) NULL,
[NewPassword] [NVARCHAR](100) NULL,
[IsActive] [BIT] NOT NULL CONSTRAINT [DF_Users_IsActive] DEFAULT ((1)),
[IsOnline] [BIT] NOT NULL CONSTRAINT [DF_Users_IsOnline] DEFAULT ((0)),
[IsConfirm] [BIT] NULL CONSTRAINT [DF_Users_IsConfirm] DEFAULT ((0)),
[LastLoginTime] [DATETIME] NULL,
[Gender] [BIT] NULL,
[MarriedStatus] [BIT] NULL,
[BirthDate] [DATETIME] NULL,
[CountryNo] [SMALLINT] NULL,
[LanguageNo] [TINYINT] NULL,
[Address] [NVARCHAR](250) NULL,
[E_Mail] [NVARCHAR](100) NULL,
[PhotoPath] [NVARCHAR](200) NULL,
[CreationDate] [DATETIME] NULL CONSTRAINT [DF_Users_CreationDate] DEFAULT (GETDATE()),
[EditionDate] [DATETIME] NULL,
[AcceptXML] [NTEXT] NULL,
[LastRefreshTime] [DATETIME] NULL CONSTRAINT [DF_Users_LastRefreshTime] DEFAULT (GETDATE()),
[LastChangePassDate] [DATETIME] NULL,
[NativeID] [NVARCHAR](200) NULL CONSTRAINT [DF_Users_NativeID] DEFAULT (''),
[PersonnelID] [NVARCHAR](200) NULL,
[CRC] [CHAR](40) NULL,
[ActiveSessionID] [VARCHAR](200) NULL,
[SystemProfile] [XML] NULL,
[IPLocation] [VARCHAR](20) NULL,
[DepartmentID] [INT] NOT NULL CONSTRAINT [DF_Users_DepartmentID] DEFAULT ((-1)),
[Phone] [VARCHAR](20) NULL,
[Mobile] [VARCHAR](20) NULL,
[UserType] [INT] NULL,
[Title] [NVARCHAR](100) NULL,
[Description] [NVARCHAR](2048) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[User_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO
It should be noted that Test1 , test3 database are on Server1 and Test2 DataBase is on server 2.That's only one of the tables, but OK, let's start with that. Your source table has considerably fewer columns than your destination. Have you decided how you're going to map the columns between the two tables, and what values you're going to put in the destination columns that don't map? Are there any foreign key constraints? Is there already any data in the destination table? Do you want the destination table to have the same values of User_ID as the values of Id in the source tables? Do you even need the User_ID column at all, given that there appear to be no foreign key constraints? Are any values of Id duplicated between Test1 and Test2? Are any identical users defined in both Test1 and Test2? Do you really store passwords in plain text? What is each database actually used for?
You might consider going back to the drawing board and redesigning the whole thing, because it looks as if you're trying to force the proverbial square peg into a round hole.
John
I use this store procedure for sync users,roles,groups first time and next times I use for example type=8 of sp:
alter PROCEDURE [dbo].[IcanSp_CreateAndDeleteUser]
(
@UserName nvarchar(100),
@FirstName nvarchar(100),
@LastName nvarchar(100),
@IsActive bit,
@RoleID int,
@GroupID int,
@ListOfRoleID nvarchar(2000),
@UserID int,
@Password nvarchar(200),
@ListOfUserID nvarchar(2000),
@ServerIp varchar(20),
@Type int
)
AS
BEGIN
DECLARE @DepartmentID int,
@OrganizationRoleID int,
@InnerUserID int,
@InnerGroupID int,
@PublicGroupID int,
@InnerRoleID int,
@PublicRoleID int,
@Sql nvarchar(max),
@Count int,
@i int,
@Item nvarchar(200),
@intErrorCode int
SELECT @DepartmentID=[Department_ID] FROM dbo.[Departments] where [IsReference]=1
SELECT TOP 1 @OrganizationRoleID=OrganizationRole_ID from OrganizationRoles
------@UserType=0 Users @UserType=1 Admin
if @Type=1----Type=1 SyncUsers
Begin
DELETE FROM [Users] where [User_ID] not in (select UserIDI from UsersMapping where UsersMapping.[SystemID] = @ServerIp)
INSERT INTO [Users] ([FirstName]
,[LastName]
,[UserName]
,[Password]
,[NewPassword]
,[IsActive]
,[IsOnline]
,[IsConfirm]
,[LastLoginTime]
,[Gender]
,[MarriedStatus]
,[BirthDate]
,[CountryNo]
,[LanguageNo]
,[Address]
,[E_Mail]
,[PhotoPath]
,[CreationDate]
,[EditionDate]
,[AcceptXML]
,[LastRefreshTime]
,[LastChangePassDate]
,[NativeID]
,[PersonnelID]
,[CRC]
,[ActiveSessionID]
,[SystemProfile]
,[IPLocation]
,[DepartmentID]
,[Phone]
,[Mobile]
,[UserType])
SELECT [FirstName] as [FirstName]
,[LastName]+' '+@ServerIp as [LastName]
,[UserName]+RIGHT(@ServerIp , CHARINDEX ('.' ,REVERSE(@ServerIp))-1) as [UserName]
,[Password] as [Password]
,null as [NewPassword]
,[Enabled] as [IsActive]
,[IsOnline] as [IsOnline]
,0 as [IsConfirm]
,[LastLoginTime] as [LastLoginTime]
,(case [Sex] when 'مرد' then 1 when 'زن' then 0 end)as [Gender]
,0 as [MarriedStatus]
,[BirthDate] [BirthDate]
,[BirthState] as [CountryNo]
,[BirthCity] as [LanguageNo]
,null as [Address]
,null as [E_Mail]
,null as [PhotoPath]
,[CreationDate] as [CreationDate]
,GETDATE() as [EditionDate]
,null as [AcceptXML]
,GETDATE() as [LastRefreshTime]
,GETDATE() as [LastChangePassDate]
,[NationalCode] as [NativeID]
,[Id] as [PersonnelID]
,null as [CRC]
,null as [ActiveSessionID]
,'<SystemProfile>
<CardTable>
<Reminder TimeOut="300" isActive="1" />
<Recycle RemovePeriodTime="60" />
<Message RemovePeriodTime="60" />
<Outbox>
<ConsiderNow authorizedCount="" />
<Considered authorizedCount="" />
<NotConsidered authorizedCount="" />
<Reject authorizedCount="" />
</Outbox>
</CardTable>
<Page>
<DocFlowHistoryView default="Graph" />
<SendService viewInOutbox="1" searchItem="role" searchOption="include" />
<Theme default="silver" />
</Page>
<SecurityPolicies>
<UserAccountStructure minPassLen="-1" maxPassLen="-1" complexRule="-1" />
<ChangeAccountInFirstLogin value="-1" />
<MaxUserAccountAge value="-1" />
<PasswordExpirationAge value="-1" />
</SecurityPolicies>
</SystemProfile>' as [SystemProfile]
,@ServerIp as IPLocation
, @DepartmentID as [DepartmentID]
,null as [Phone]
,null as [Mobile]
,0 as [UserType]
FROM OPENQUERY (LINK120,'select * from [Bpms_Core].[Office].[Users]')
End
-- Insert to Log for type 1
INSERT INTO UsersMapping
-- SELECT [User_ID], PersonnelID, @ServerIp FROM Users WHERE PersonnelID NOT IN
-- (SELECT UserIDB FROM UsersMapping ) and IPLocation not in (select SystemID from UsersMapping)
SELECT [User_ID], PersonnelID,IPLocation FROM Users WHERE PersonnelID NOT IN
(SELECT UserIDB FROM UsersMapping where SystemID =@ServerIp)
and IPLocation =@ServerIp
----Type=2 Sync Roles with user
if @Type=2
Begin
IF EXISTS (SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'dbo.FK_Roles_Ex_Rights_Roles')
AND parent_object_id = OBJECT_ID(N'dbo.Roles')
)
ALTER TABLE Roles_Ex_Rights DROP
CONSTRAINT FK_Roles_Ex_Rights_Roles
--DELETE FROM [Roles] where Role_ID not in (select RoleIDI from RolesMapping)
INSERT INTO [Roles] ([OrganizationRoleID]
,[RoleName]
,[UserID]
,[CardTableStatus]
,[Code]
,[IsActive]
,[ParentID]
,[DepartmentID]
,[LastRefreshTime]
,[IsDefForCardTable]
,[OriginRoleID])
select @OrganizationRoleID as [OrganizationRoleID]
,[Name]+' '+@ServerIp as [RoleName]
,[UserId] as [UserID]
,1 as [CardTableStatus]
,[Code] as [Code]
,r.[Enabled] as [IsActive]
,[Bpms_Core].[Office].[GetSuperior]([RoleId]) as [ParentID]
,@DepartmentID as [DepartmentId]
,r.[CreationDate] as [LastRefreshTime]
,null as [IsDefForCardTable]
, r.[Id] as [OriginRoleID]
from OPENQUERY (LINK120,'select * from [Bpms_Core].[Office].[Actors]') ac,
OPENQUERY (LINK120, 'select * from [Bpms_Core].[Office].[Users]') u,
OPENQUERY (LINK120,'select * from [Bpms_Core].[Office].[Roles]') r
where u.[Id]=ac.[UserId] and r.[Id]=ac.[RoleId]
--where r.[Enabled] =1
Insert Into RolesMapping
select Role_ID,OriginRoleID,@ServerIp from [dbo].[Roles]
WHERE Role_ID not in (select RoleIDI from RolesMapping)
--WHERE OriginRoleID NOT IN
-- (SELECT RoleIDB FROM RolesMapping where SystemID=@ServerIp)
IF NOT Exists(select * from [dbo].[Groups] where GroupName like N'كاربران عمومي')
begin
INSERT INTO [dbo].[Groups]
VALUES
(N'كاربران عمومي',GetDate(),1,1,'2020-07-24 13:55:54.000',16777215,16777215,16777215,16777215,16777215,16777215,16777215,0,0,0)
select @PublicGroupID=SCOPE_IDENTITY()
end
select @PublicGroupID = Group_ID from [dbo].[Groups] where GroupName like N'كاربران عمومي'
IF NOT Exists(select * from [dbo].[Groups_Rights] where RightID = 127 and [RightID] = @PublicGroupID)
begin
INSERT INTO [dbo].[Groups_Rights]
VALUES
(@PublicGroupID,127,1,null)----ورود به سيستم
end
IF NOT Exists(select * from [dbo].[Groups_Rights] where RightID = 10142 and [RightID] = @PublicGroupID)
begin
INSERT INTO [dbo].[Groups_Rights]
VALUES
(@PublicGroupID,10142,1,null)-----دسترسي به امكانات عمومي
end
IF NOT Exists(select * from [dbo].[Groups_Rights] where RightID = 10227 and [RightID] = @PublicGroupID)
begin
INSERT INTO [dbo].[Groups_Rights]
VALUES
(@PublicGroupID,10227,1,null)-----مشاهده ي ليست گزارشات
end
IF NOT Exists(select * from [dbo].[Groups_Rights] where RightID = 10230 and [RightID] = @PublicGroupID)
begin
INSERT INTO [dbo].[Groups_Rights]
VALUES
(@PublicGroupID,10230,1,null)-----دسترسی به موتور تولید گزارش
end
INSERT INTO [dbo].[Groups_Roles]
SELECT @PublicGroupID,[Role_ID],1,null from [dbo].[Roles]
select @user-id=User_ID fROM [dbo].[Users] where [UserName] like '%ican%'
update Roles
set ParentID=-1
where UserID=@UserID
End
--------Type=3 Sync Roles without user
--------Type=4 Sync Groups
if @Type=4
Begin
DELETE FROM [Groups] where GroupName not like 'generalusers' and Group_ID not in (select GroupIDI from GroupsMapping)
INSERT INTO [Groups]
([GroupName]
,[CreationDate]
,[AllLocationAccess]
,[AllTimesAccess]
,[ExpirationDate]
,[Saturday]
,[Sunday]
,[Monday]
,[Tuesday]
,[Wednesday]
,[Thursday]
,[Friday]
,[Priority]
,[UsedForSend])
Select
[Name]+' '+@ServerIp as [GroupName]
,[CreationDate] as [CreationDate]
,1 as [AllLocationAccess]
,1 as [AllTimesAccess]
,'2020-07-24 13:55:54.000' as [ExpirationDate]
,0 as [Saturday]
,0 as [Sunday]
,0 as [Monday]
,0 as [Tuesday]
,0 as [Wednesday]
,0 as [Thursday]
,0 as [Friday]
,Id as [Priority]
,0 as [UsedForSend]
------Change Linke server name
from OPENQUERY (LINK120,'SELECT * FROM [Bpms_Core].[Office].[Groups]')
INSERT INTO GroupsMapping
select Group_ID,[Priority],@ServerIp from [Groups] where Group_ID not in
(select GroupIDI from GroupsMapping)
End
------Type=8 Create User And updae Roles
if @Type=8 and not exists(select * from Users where UserName like @UserName and FirstName like @FirstName and LastName like @LastName)
Begin
BEGIN TRAN
set @Sql='INSERT INTO [dbo].[Users] ([FirstName]
,[LastName]
,[UserName]
,[Password]
,[NewPassword]
,[IsActive]
,[IsOnline]
,[IsConfirm]
,[LastLoginTime]
,[Gender]
,[MarriedStatus]
,[BirthDate]
,[CountryNo]
,[LanguageNo]
,[Address]
,[E_Mail]
,[PhotoPath]
,[CreationDate]
,[EditionDate]
,[AcceptXML]
,[LastRefreshTime]
,[LastChangePassDate]
,[NativeID]
,[PersonnelID]
,[CRC]
,[ActiveSessionID]
,[SystemProfile]
,[IPLocation]
,[DepartmentID]
,[Phone]
,[Mobile]
,[UserType])
SELECT [FirstName] as [FirstName]
,[LastName]+'' ''+'''+@ServerIp+''' as [LastName]
,[UserName] as [UserName]
,[Password] as [Password]
,null as [NewPassword]
,[Enabled] as [IsActive]
,[IsOnline] as [IsOnline]
,0 as [IsConfirm]
,[LastLoginTime] as [LastLoginTime]
,(case [Sex] when ''مرد'' then 1 when ''زن'' then 0 end)as [Gender]
,0 as [MarriedStatus]
,[BirthDate] [BirthDate]
,[BirthState] as [CountryNo]
,[BirthCity] as [LanguageNo]
,null as [Address]
,null as [E_Mail]
,null as [PhotoPath]
,[CreationDate] as [CreationDate]
,GETDATE() as [EditionDate]
,null as [AcceptXML]
,GETDATE() as [LastRefreshTime]
,GETDATE() as [LastChangePassDate]
,[NationalCode] as [NativeID]
,[Id] as [PersonnelID]
,null as [CRC]
,null as [ActiveSessionID]
,''<SystemProfile>
<CardTable>
<Reminder TimeOut="300" isActive="1" />
<Recycle RemovePeriodTime="60" />
<Message RemovePeriodTime="60" />
<Outbox>
<ConsiderNow authorizedCount="" />
<Considered authorizedCount="" />
<NotConsidered authorizedCount="" />
<Reject authorizedCount="" />
</Outbox>
</CardTable>
<Page>
<DocFlowHistoryView default="Graph" />
<SendService viewInOutbox="1" searchItem="role" searchOption="include" />
<Theme default="silver" />
</Page>
<SecurityPolicies>
<UserAccountStructure minPassLen="-1" maxPassLen="-1" complexRule="-1" />
<ChangeAccountInFirstLogin value="-1" />
<MaxUserAccountAge value="-1" />
<PasswordExpirationAge value="-1" />
</SecurityPolicies>
</SystemProfile>'' as [SystemProfile]
,'''+@ServerIp+''' as IPLocation
,'+cast(@DepartmentID as nvarchar(10))+' as [DepartmentID]
,null as [Phone]
,null as [Mobile]
,0 as [UserType] '
set @sql=@sql+' FROM OPENQUERY (LINK120,''select * from [Bpms_Core].[Office].[Users]
where UserName like N''''%'+@UserName+'%'''' and FirstName like N''''%'+@FirstName+'%'''' and LastName like N''''%'+@LastName+'%'''''')'
select @sql
exec sp_executesql @sql
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM2
select @InnerUserID=SCOPE_IDENTITY()
INSERT INTO UsersMapping
SELECT [User_ID], PersonnelID, @ServerIp FROM Users WHERE User_ID=@InnerUserID
select @Count=COUNT(*) from dbo.IcanSplit(@ListOfRoleID,',')
set @i=1
while (@i<=@Count)
begin
select @Item=items from dbo.IcanSplit(@ListOfRoleID,',') where id=@i
select @InnerRoleID=RoleIDI from RolesMapping where RoleIDB=cast(@Item as int)
update Roles
set UserID=@InnerUserID
where Role_ID=@InnerRoleID
SELECT @intErrorCode = @@ERROR
IF (@intErrorCode <> 0) GOTO PROBLEM2
set @i+=1
end
COMMIT TRAN
PROBLEM2:
IF (@intErrorCode <> 0)
BEGIN
PRINT 'Unexpected error occurred!'
ROLLBACK TRAN
END
End
end
I have Usermapping and rolesmapping and groupmapping like this:
create TABLE [dbo].[UsersMapping](
[UserIDTest1] [int] NOT NULL,
[UserIDTest3] [int] NOT NULL,
[ServerIp] [varchar](20) NOT NULL,
CONSTRAINT [PK_UserMapping] PRIMARY KEY CLUSTERED
(
[UserIDTest1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
and also use this trigger for add new roles:
ALTER TRIGGER Test2.[Office].[AfterInsertUser]
ON [Office].[Users]
AFTER INSERT
AS
BEGIN
DECLARE
@UserName nvarchar(100),
@FirstName nvarchar(100),
@LastName nvarchar(100),
@RoleID int,
@GroupID int,
@ListOfRoleID nvarchar(2000),
@UserID int,
@Password nvarchar(200),
@ListOfUserID nvarchar(2000)
SELECT
@UserID = Id,
@UserName = UserName,
@FirstName = FirstName,
@LastName = LastName,
@Password = Password
FROM inserted
SELECT @ListOfRoleID = COALESCE(@ListOfRoleID + ',', '') + convert(nvarchar(20),RoleId) from [Office].[ActorsView]
WHERE UserId = @user-id
EXECUTE linkserver.test3.[dbo].[IcanSp_CreateAndDeleteUser]
@UserName,
@FirstName,
@LastName,
null,
null ,
@ListOfRoleID,
@UserID,
@Password,
null,
8
END
I don't any problem for sync table for type=1,2,4,main my problem is executing TRIGGER [Office].[AfterInsertUser]
ON [Office].[Users]
I want your help for this problem.
thanks
January 18, 2017 at 4:03 am
elhamazizi5456 - Wednesday, January 18, 2017 3:48 AMmain my problem is executing TRIGGER [Office].[AfterInsertUser]
ON [Office].[Users]
What precisely is the problem? Error message? Unexpected results? I'm more confused than ever now, because you haven't made it clear which objects are in which databases. But I can see two problems with the trigger. First, it expects only one row to be inserted. You need to rewrite it so that it handles cases where multiple rows are inserted. This will involve rewriting the stored procedure, since that also only handles a single row. Second, the trigger calls a stored procedure which inserts more rows into the same table, which will cause the trigger to fire again, which will call the same stored procedure again, which will insert more rows into the same table... I'm sure you get the picture.
John
January 18, 2017 at 4:36 am
John Mitchell-245523 - Wednesday, January 18, 2017 4:03 AMelhamazizi5456 - Wednesday, January 18, 2017 3:48 AMmain my problem is executing TRIGGER [Office].[AfterInsertUser]
ON [Office].[Users]What precisely is the problem? Error message? Unexpected results? I'm more confused than ever now, because you haven't made it clear which objects are in which databases. But I can see two problems with the trigger. First, it expects only one row to be inserted. You need to rewrite it so that it handles cases where multiple rows are inserted. This will involve rewriting the stored procedure, since that also only handles a single row. Second, the trigger calls a stored procedure which inserts more rows into the same table, which will cause the trigger to fire again, which will call the same stored procedure again, which will insert more rows into the same table... I'm sure you get the picture.
John
My problem occurs when new user is inserted in table users in test2 database that is on server2.
my procedure is in test3 database on server1.
my trigger is on users table in test2 database on server2 that call my storeprocedure.
In this trigger is called my procedure and my problem is calling linkserver twice and this trigger can not perform and doesn't get any error.
January 18, 2017 at 4:48 am
elhamazizi5456 - Wednesday, January 18, 2017 4:36 AMIn this trigger is called my procedure and my problem is calling linkserver twice and this trigger can not perform and doesn't get any error.
You mean the problem is that you want it to call the linked server twice but it doesn't, or that you don't want it to call it twice but it does? With no error message, what are the signs that something is wrong?
The second issue that I identified with your trigger doesn't apply now that I understand that it's inserting to a table on a different server. But you still need to change the trigger so that it handles multiple rows.
John
January 18, 2017 at 4:56 am
trigger is my solution for my project but does not work.Now I need to suggest best way instead of this trigger that I call link server Twice.
January 18, 2017 at 5:00 am
John
January 18, 2017 at 5:09 am
John Mitchell-245523 - Wednesday, January 18, 2017 5:00 AMWhy do you need to call the linked server twice?John
I don't know how can I insert new user from test2 to test3.
because I have test3 on server2 and test2 on server1 and I should have my sp in test3.
this calling twice is needed for this case.I know this solution is not correct and I want to you help me with best way.
January 18, 2017 at 5:20 am
elhamazizi5456 - Wednesday, January 18, 2017 5:09 AMI don't know how can I insert new user from test2 to test3.
because I have test3 on server2 and test2 on server1 and I should have my sp in test3.
this calling twice is needed for this case.I know this solution is not correct and I want to you help me with best way.
I still don't understand why you need to call it twice. Is it because you need the trigger to copy the new user into test1 and test3?
John
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply