[SQL] Variabele in stored procedure in ADSI

[SIMPY.be]

Ahoi

Ik ben bezig met een stored procedure aan het schrijven op SQL server. De bedoeling is om een tabel up te daten met gegevens uit de active directory. Dit werkt al perfect, maar dit bedrijf heeft meerdere vestigingen. Als ik alle accounts uit de AD haal, dan zijn er dat meer dan 1000, en werkt het niet.
Het idee is om dan maar een loopje te maken die vestiging per vestiging afhandelt.

Ik heb dus een loopje voor iedere vestiging:

SQL:

DECLARE @places as varchar(200)
DECLARE @placestemp as varchar(200)
DECLARE @place as varchar(4)

SET @places = ',CTBP,CSBK,CSDF,CSNR,CSRO,CSSV,CTBI,CTCK'

declare @iPos int

set @ipos = 1

while @iPos != 8
    begin
    set @placestemp = LTrim(left(LTrim(right(@places, (@iPos*5))),5))
    set @placestemp = LTrim(right(@placestemp, 4))


    
    DECLARE @ADSIURI as varchar(128)
    SET @ADSIURI = 'LDAP://OU=' + @placestemp + ';DC=***;DC=***'
    print @ADSIURI
    set @ipos = @ipos + 1
end

Ik heb uit veiligheid de naam van het bedrijf in sterretjes gezet. Ik kan dus hiermee iedere keer de "connectionstring" naar de ADSI gaan genereren. Dit werkt perfect.
Ik heb ook het update script:

SQL:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--ALTER procedure [dbo].[pr_UserAdministration_DailyJob]
--as

DECLARE @mobile as varchar(15)
DECLARE @telephonenumber as varchar(15)
DECLARE @facsimiletelephonenumber as varchar(15)
DECLARE @mail as varchar(255)
DECLARE @sAMAccountName as varchar(50)

DECLARE @places as varchar(200)
DECLARE @placestemp as varchar(200)
DECLARE @place as varchar(4)
declare @iPos int

DECLARE @MyCursor1 CURSOR 

SET @places = ',CTBP,CSBK,CSDF,CSNR,CSRO,CSSV,CTBI,CTCK'

set @ipos = 1


while @iPos != 9
    begin
    set @placestemp = LTrim(left(LTrim(right(@places, (@iPos*5))),5))
    set @placestemp = LTrim(right(@placestemp, 4))


    SET @MyCursor1= CURSOR FAST_FORWARD

    FOR 

SELECT mobile, telephonenumber, facsimiletelephonenumber, mail, sAMAccountName FROM OPENQUERY(ADSI,'SELECT mobile, telephonenumber, facsimiletelephonenumber, mail, sAMAccountName FROM ''LDAP://OU=CTBP;DC=***;DC=***'' WHERE objectCategory = ''Person'' AND objectClass = ''user''') WHERE sAMAccountName IS NOT NULL

    

    OPEN @MyCursor1
    FETCH NEXT FROM @MyCursor1
    INTO @mobile, @telephonenumber, @facsimiletelephonenumber, @mail, @sAMAccountName

    WHILE @@FETCH_STATUS = 0 
    BEGIN 
    
/*DECLARE @ADSIURI as varchar(128)
SET @ADSIURI = 'LDAP://OU=' + @placestemp + ';DC=***;DC=***'
print @ADSIURI*/
    --print @placestemp + @sAMAccountName + @mobile + @telephonenumber + @facsimiletelephonenumber + @mail
EXEC pr_UserAdministration_UpdateEmployeeAD @mobile, @telephonenumber, @facsimiletelephonenumber, @mail, @sAMAccountName

    FETCH NEXT FROM @MyCursor1
    INTO @mobile, @telephonenumber, @facsimiletelephonenumber, @mail, @sAMAccountName

    END

    CLOSE @MyCursor1
    DEALLOCATE @MyCursor1

    set @ipos = @ipos + 1

end

Maar het combineren van de 2 lukt me niet... Gewoon de connectionstring gaan printen is geen probleem, maar het daarwerkelijk gaan gebruiken, dat wil SQL server precies niet.

Tussen de "for" en "select" kan ik niets zetten, of ik krijg een fout: "Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'DECLARE'."...

Kan iemand me helpen?

Bedankt!

[SIMPY.be]

Voila, ik heb het opgelost met een tussentabel te gebruiken:

SQL:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

--ALTER procedure [dbo].[pr_UserAdministration_DailyJob]
--as

DECLARE @mobile as varchar(15)
DECLARE @telephonenumber as varchar(15)
DECLARE @facsimiletelephonenumber as varchar(15)
DECLARE @mail as varchar(255)
DECLARE @sAMAccountName as varchar(50)

DECLARE @places as varchar(200)
DECLARE @placestemp as varchar(200)
DECLARE @place as varchar(4)
declare @iPos int

DECLARE @MyCursor1 CURSOR 
DECLARE @Querystring as varchar(1000)

SET @places = ',CTBP,CSBK,CSDF,CSNR,CSRO,CSSV,CTBI,CTCK'

set @ipos = 1


while @iPos != 9
    begin
    set @placestemp = LTrim(left(LTrim(right(@places, (@iPos*5))),5))
    set @placestemp = LTrim(right(@placestemp, 4))

    set @querystring ='
    select mobile, telephonenumber, facsimiletelephonenumber, mail,         sAMAccountName INTO ##AD_Users FROM OPENQUERY(ADSI,''SELECT mobile, telephonenumber, facsimiletelephonenumber, mail, sAMAccountName FROM ''''LDAP://ou=' + @placestemp  + ',dc=***,dc=***'''' WHERE objectCategory = ''''Person'''' AND objectClass = ''''user'''''') WHERE sAMAccountName IS NOT NULL'
 EXEC(@querystring)

--SELECT mobile, telephonenumber, facsimiletelephonenumber, mail, sAMAccountName FROM OPENQUERY(ADSI,'SELECT mobile, telephonenumber, facsimiletelephonenumber, mail, sAMAccountName FROM ''LDAP://OU=CTBP;DC=***;DC=***'' WHERE objectCategory = ''Person'' AND objectClass = ''user''') WHERE sAMAccountName IS NOT NULL

    SET @MyCursor1= CURSOR FAST_FORWARD
    FOR SELECT * from ##AD_Users
    OPEN @MyCursor1
    FETCH NEXT FROM @MyCursor1
    INTO @mobile, @telephonenumber, @facsimiletelephonenumber, @mail, @sAMAccountName

    WHILE @@FETCH_STATUS = 0 
    BEGIN 

    EXEC pr_UserAdministration_UpdateEmployeeAD @mobile, @telephonenumber, @facsimiletelephonenumber, @mail, @sAMAccountName

    FETCH NEXT FROM @MyCursor1
    INTO @mobile, @telephonenumber, @facsimiletelephonenumber, @mail, @sAMAccountName   

    END

    CLOSE @MyCursor1
    DEALLOCATE @MyCursor1

    drop table ##AD_users

    set @ipos = @ipos + 1

end