Listing 1: LOAD_ADSI_USERS_SP Stored Procedure

USE ReportServer
GO

CREATE PROCEDURE LOAD_ADSI_USERS_SP
AS
BEGIN
  BEGIN
  IF EXISTS (SELECT name
                  FROM ReportServer.dbo.sysobjects
                  WHERE name = 'ADSI_USERS')
  BEGIN
  BEGIN TRANSACTION
  DROP TABLE ADSI_USERS
  IF @@ERROR <> 0
  BEGIN
                  ROLLBACK TRANSACTION
                  RAISERROR  ('An error occurred attempting
                     to drop the ADSI_USERS table!', 16, 1) 
  END
  ELSE
                  COMMIT
                  END
  END

  BEGIN
  BEGIN TRANSACTION
  SELECT name, mail, mailnickname
  INTO ADSI_USERS
  FROM OPENQUERY(ADSI,'
  SELECT name, mail, mailnickname
  FROM ''LDAP://DC=polkaudio,DC=com''
  WHERE objectclass=''user''')
  WHERE mail IS NOT NULL
  ORDER BY mailnickname
  IF @@ERROR <> 0
  BEGIN
                  ROLLBACK TRANSACTION
                  RAISERROR  ('An error occurred attempting
                     to load the ADSI_USERS table!', 16, 1) 
  END
  ELSE
  COMMIT
  END

  BEGIN
  BEGIN TRANSACTION	
  CREATE CLUSTERED INDEX ADSI_USERS_IDX
  ON ADSI_USERS(mailnickname)
  IF @@ERROR <> 0
  BEGIN
                  ROLLBACK TRANSACTION
                  RAISERROR  ('An error occurred attempting
                    to create the ADSI_USERS index!', 16, 1) 
  END
  ELSE
  COMMIT
  END
  END
GO


2