ALTER PROCEDURE LDAP_SAMName_to_DisplayName_CreateTable
-- обновление данных LDAP домена, чтобы отображать имена пользователей красиво.
AS
SET NOCOUNT ON 
SET XACT_ABORT ON

begin tran LDAP
	if OBJECT_ID('dbo.[LDAP_SAMName_to_DisplayName]') is not null 
		DELETE dbo.[LDAP_SAMName_to_DisplayName]
	else begin
		CREATE TABLE dbo.[LDAP_SAMName_to_DisplayName] (
			[DisplayName] [nvarchar] (256) COLLATE Cyrillic_General_CI_AS NOT NULL ,
			[AccountName] [nvarchar] (256) COLLATE Cyrillic_General_CI_AS NOT NULL ,
			[SAMAccountName] [nvarchar] (128) COLLATE Cyrillic_General_CI_AS NOT NULL ,
			CONSTRAINT [PK_LDAP_SAMName_to_DisplayName] PRIMARY KEY  CLUSTERED ([AccountName]) 
		)	
		CREATE  UNIQUE  INDEX [IX_LDAP_SAMName_to_DisplayName] ON [dbo].[LDAP_SAMName_to_DisplayName]([SAMAccountName]) 
          end

	declare @sql_template nvarchar(4000), @sql nvarchar(4000), @fqdn nvarchar(128)

	select @fqdn=str FROM dbo.[isa2006 Глобальные параметры] WHERE ID=2
	if @fqdn is null  SET @fqdn=N''

	set @sql_template='SELECT Name DisplayName, ''' + dbo.LDAP_DomainName()+'\''+ SAMAccountName [AccountName], SAMAccountName '
		+' FROM OPENQUERY(ADSI, ''select Name, sAMAccountName from ''''LDAP://'
		+dbo.[LDAP_FQDN->DCs](@fqdn)
		+''''' where objectClass = ''''User'''' and objectCategory=''''Person'''' <<replace>> ORDER BY SAMAccountName ASC '')'
	declare @rc int, @name nvarchar(256)
	set @rc=1
	set @sql=REPLACE(@sql_template, N'<<replace>>', N'')
	while @rc>0 begin
		INSERT INTO dbo.[LDAP_SAMName_to_DisplayName] exec(@sql)
		set @rc=@@rowcount
		if @rc>0 begin
			select top 1 @name = SAMAccountName FROM dbo.[LDAP_SAMName_to_DisplayName]  ORDER BY SAMAccountName DESC
			set @sql=REPLACE(@sql_template, N'<<replace>>', N' AND SAMAccountName>'''''+@name+''''' ')
		end
	end
commit tran LDAP


	RETURN

"dbo.[LDAP_FQDN->DCs](@fqdn)"