最近接到任务来研究这个问题,但无法理解为什么它不起作用。这是别人在我之前创建的,我不是一个uber经验丰富的dba。无论如何,问题是,一些用户的详细信息没有得到更新,在我们的联系目录和其他根本不存在。我做了一些挖掘,发现了一个sp,它使用ldap查询来点击ad并将用户拉回来。这些用户都存在于广告中,看不到他们为什么不能通过的任何其他问题。proc语法的一部分是openquery,它使用adsi作为链接服务器,我知道这有1000行的限制。我将张贴下面的程序代码,如果有人能看到任何明显的,请启发我。
USE [Web_Repository]
GO
/******Object: StoredProcedure [dbo].[get_activedirectory] Script Date: 08/03/2020 11:04:57******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[get_activedirectory]
as
--GET ACTIVE DIRECTORY USERS OF ENABLED ACOUNTS
declare @i integer, @accname varchar(1000), @sql varchar(8000)
if exists (select * from sysobjects where name = 'activedirectory_temp' ) Drop Table activedirectory_temp
CREATE TABLE [activedirectory_temp] (
[sn] [varchar] (256) NULL ,
[GivenName] [varchar] (256) NULL ,
[displayName] varchar (256) NULL,
[Department] [varchar] (256) NULL ,
[Title] [varchar] (256) NULL ,
[SAMAccountName] [varchar] (256) NULL ,
[Company] [varchar] (256) NULL ,
[mail] [varchar] (256) NULL ,
[physicalDeliveryOfficeName] [varchar] (256) NULL ,
[enabled] bit NULL,
[wWWHomePage] [varchar] (256) NULL
) ON [PRIMARY]
set @sql = 'insert into dbname.dbo.activedirectory_temp select top 901 sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName,0 enabled, wWWHomePage from openquery (ADSI,''SELECT sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName, wWWHomePage FROM ''''LDAP://DC=Group,DC=Net'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND (UserAccountControl = ''''512'''' OR UserAccountControl = ''''640'''' or company = ''''zeroc'''' or SAMAccountName = ''''137499'''' or SAMAccountName = ''''157067'''' or SAMAccountName = ''''WebIE10test'''') ORDER BY SAMAccountName'')'
set @sql = replace(@sql,'dbname',db_name())
exec(@sql)
set @i = @@rowcount
while @i <> 0
begin
set @accname = (select max(SAMAccountName) from activedirectory_temp)
--OU=Group Less Restricted
set @sql = 'insert into dbname.dbo.activedirectory_temp select top 901 sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName,0 enabled, wWWHomePage from openquery (ADSI,''SELECT sn, GivenName, displayName, Department, Title, SAMAccountName, Company, mail, physicalDeliveryOfficeName, wWWHomePage FROM ''''LDAP://DC=Group,DC=Net'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''' AND (UserAccountControl = ''''512'''' OR UserAccountControl = ''''640'''' or company = ''''zeroc'''' or SAMAccountName = ''''137499'''' or SAMAccountName = ''''157067'''' or SAMAccountName = ''''WebIE10test'''') AND SAMAccountName > ''''####'''' ORDER BY SAMAccountName'')'
set @sql = replace(@sql,'dbname',db_name())
set @sql = replace(@sql,'####',@accname)
exec(@sql)
set @i = @@rowcount
end
--REMOVE ALL ADMIN & DUMMY ACCOUNTS
update activedirectory_temp
set enabled = 1
where sn > ''
and isnull(givenname,'') > ''
and isnull(title,'') not in ('Resource','Additional')
and isnull(sn,'') <> 'Template'
--and mail is not null
--and left(mail,1) <> '_'
delete from activedirectory_temp
where enabled <> 1
--LOAD DATA INTO LIVE TABLE IF SUCCESSFULL
set @i = (select count(*) from activedirectory_temp)
/* MN 23/08/2016
Insert changed to a select distinct to remove duplicate problem for sort order difference between LDAP and SQL
ie SQL orders local variable @accname (SAMAccountName) natpaint -> N-E-H, whilst LDAP orders N-E-H -> natpaint
This causes a duplicate when selecting 901 rows into the temp table */
if @i > 0
begin
truncate table activedirectory
insert into activedirectory (sn,GivenName,displayName,Department,Title,SAMAccountName,Company,mail,physicalDeliveryOfficeName, wWWHomePage)
select distinct sn,GivenName,displayName,Department,Title,SAMAccountName,Company,mail,physicalDeliveryOfficeName, wWWHomePage
from activedirectory_temp
end
if exists (select * from sysobjects where name = 'activedirectory_temp' ) Drop Table activedirectory_temp
1条答案
按热度按时间gzszwxb41#
经过进一步调查,我们发现是一个单独的过程/工作导致了问题。