Post reply

Is there a way to convert a USP to a CLR SP or be treated as a table returned SP?

  • November 1, 2011 at 10:35 pm

    #246165

    Here goes the scenario:

    Need to pass an active directory security group and get the memberOf.

    Tried table value function does not allow EXEC to execute and return a table containing the results simply because I am using openquery with LDAP.

    So, calling an sp within function is out of question.

    The results can be achieved by a stored procedure but the "caller" must be able to get the results in a table form.

    I am open for suggestions, but at this time, I am planning on finding out if there is a way to convert an SP to a CLR.

    Ideas are welcome.

    Mean while, there is my code in SP that works.

    ALTER PROCEDURE [dbo].[USP_DisplayMemberOf_ADGroup]

    @ADGroup nvarchar(64) = '__MyGroup__'

    AS

    BEGIN

    SET NOCOUNT ON;

    declare @PartOne nvarchar(1000), @PartTwo nvarchar(2000)

    set @PartOne = 'SELECT cn, company, department, displayName, employeeID, facsimileTelephoneNumber, givenName

    , groupType, lastLogoff, mail, manager, mobile, ou, physicalDeliveryOfficeName, sAMAccountName

    , sAMAccountType ,sn, telephoneNumber, title, url, userAccountControl, userPrincipalName

    FROM ''LDAP://DomainName''

    WHERE memberOf = ''cn='+ @ADGroup + ',OU=Groups,OU=ParentGroup,DC=CompanyNameOrDomain,DC=ad'''

    set @PartTwo =N'

    SELECT

    cn

    , department

    , displayName

    , employeeID

    , givenName

    , sn

    , mail

    , manager

    , physicalDeliveryOfficeName

    , sAMAccountName

    , telephoneNumber

    , title

    , userPrincipalName

    FROM OPENQUERY (ADSI, ''' + replace(@PartOne, '''', '''''') + ''' )'

    print @PartTwo

    exec (@PartTwo)

    END

    The only way that the MemberOf in openquery is with double string and that is not possible in a table returned function.

    appreciate your help.

    Please let me know if there is any other additional information that's lacking in here. .

    thx

    Cheers,
    John Esraelo

  • November 2, 2011 at 4:14 pm

    #1402460

    Taking out my request.

    I have create other UDF and USP to get what I needed and don't need a CLR.

    Thx anyway guys..

    If anyone is interested in knowing what I did then please comment or reply in here and I will be more than glad to post it.

    thx

    Cheers,
    John Esraelo

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply