|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Change a stored proc to a functionI have a linked server on SQL 2000 sp3 that points to Active Directory. Using this, I'm able to retrieve data for users based on their AD login credentials. I've wrapped this up in the following stored procedure which is working beautifully: CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID ( @ntid char(7), @name varchar(80) output ) AS SET QUOTED_IDENTIFIER OFF Declare @sql varchar (250), @retVal varchar(80) if(exists(Select name from tempdb.dbo.sysobjects where(name='##temp_asdi_rnfn'))) drop table tempdb.dbo.##temp_asdi_rnfn set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, ' SELECT name FROM ''LDAP:// DC=ad,DC=bls,DC=com'' WHERE objectCategory=''Person'' AND sAMAccountName=''"+@ntid+"'' ')" Exec(@sql) Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR Select [name] from ##temp_asdi_rnfn OPEN tCUR Fetch Next From tCUR Into @name CLOSE tCUR DEALLOCATE tCUR RETURN @@Error THE PROBLEM: Although this works as designed, it also requires me to create a cursor for any resultset of values that I want to pass. To really get good use out of this, I need to put it in a user defined function so that it can be used 'in-line' in a query for example: Select NTID, fn_ResolveName(NTID) As 'UserName' From TABLENAME THE QUESTION: How can I encapsulate this code into a UDF? Because I need to pass the ntid (login id) to the linked server, I have to encapsulate the tsql into a variable and execute that variable. Of course, attempting to call Execute from within a UDF raises an error. I'd be grateful for any ideas. Kevin Bowker wrote:
Show quote > THE HISTORY: Why do you need a cursor to select one value from temp table? > > I have a linked server on SQL 2000 sp3 that points to Active > Directory. Using this, I'm able to retrieve data for users based on > their AD login credentials. I've wrapped this up in the following > stored procedure which is working beautifully: > > CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID > ( > @ntid char(7), > @name varchar(80) output > ) > AS > > SET QUOTED_IDENTIFIER OFF > Declare @sql varchar (250), > @retVal varchar(80) > > if(exists(Select name from tempdb.dbo.sysobjects > where(name='##temp_asdi_rnfn'))) > drop table tempdb.dbo.##temp_asdi_rnfn > > set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, > ' > SELECT name > FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > WHERE objectCategory=''Person'' AND > sAMAccountName=''"+@ntid+"'' > ')" > Exec(@sql) > > Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR > Select [name] from ##temp_asdi_rnfn > OPEN tCUR > Fetch Next From tCUR Into @name > CLOSE tCUR > DEALLOCATE tCUR > RETURN @@Error > Why not just select the value directly from the global temp table: Select TOP 1 @name = [name] from ##temp_asdi_rnfn Why not:
declare @query varchar(8000) set @query = ' SELECT name FROM ''LDAP:// DC=ad,DC=bls,DC=com'' WHERE objectCategory=''Person'' AND sAMAccountName= ''' + @ntid + '''' SELECT @name = name FROM OPENQUERY(ADSI, @query) I might have missed something (like some syntax in my answer!) but this should work, right? -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:ODtgQ2%23gFHA.576@TK2MSFTNGP15.phx.gbl... > Kevin Bowker wrote: >> THE HISTORY: >> >> I have a linked server on SQL 2000 sp3 that points to Active >> Directory. Using this, I'm able to retrieve data for users based on >> their AD login credentials. I've wrapped this up in the following >> stored procedure which is working beautifully: >> >> CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID >> ( >> @ntid char(7), >> @name varchar(80) output >> ) >> AS >> >> SET QUOTED_IDENTIFIER OFF >> Declare @sql varchar (250), >> @retVal varchar(80) >> >> if(exists(Select name from tempdb.dbo.sysobjects >> where(name='##temp_asdi_rnfn'))) >> drop table tempdb.dbo.##temp_asdi_rnfn >> >> set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, >> ' >> SELECT name >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >> WHERE objectCategory=''Person'' AND >> sAMAccountName=''"+@ntid+"'' >> ')" >> Exec(@sql) >> >> Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR >> Select [name] from ##temp_asdi_rnfn >> OPEN tCUR >> Fetch Next From tCUR Into @name >> CLOSE tCUR >> DEALLOCATE tCUR >> RETURN @@Error >> > > Why do you need a cursor to select one value from temp table? Why not just > select the value directly from the global temp table: > > Select TOP 1 @name = [name] from ##temp_asdi_rnfn > > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com Looks VERY plausible, thanks for the answer. I will test it tomorrow morning
(Mon) and advise. Thanks for your thoughts. Show quote "Louis Davidson" wrote: > Why not: > > declare @query varchar(8000) > set @query = > ' > SELECT name > FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > WHERE objectCategory=''Person'' > AND sAMAccountName= ''' + @ntid + '''' > > SELECT @name = name FROM OPENQUERY(ADSI, > @query) > > I might have missed something (like some syntax in my answer!) but this > should work, right? > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > > > "David Gugick" <david.gugick-nospam@quest.com> wrote in message > news:ODtgQ2%23gFHA.576@TK2MSFTNGP15.phx.gbl... > > Kevin Bowker wrote: > >> THE HISTORY: > >> > >> I have a linked server on SQL 2000 sp3 that points to Active > >> Directory. Using this, I'm able to retrieve data for users based on > >> their AD login credentials. I've wrapped this up in the following > >> stored procedure which is working beautifully: > >> > >> CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID > >> ( > >> @ntid char(7), > >> @name varchar(80) output > >> ) > >> AS > >> > >> SET QUOTED_IDENTIFIER OFF > >> Declare @sql varchar (250), > >> @retVal varchar(80) > >> > >> if(exists(Select name from tempdb.dbo.sysobjects > >> where(name='##temp_asdi_rnfn'))) > >> drop table tempdb.dbo.##temp_asdi_rnfn > >> > >> set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, > >> ' > >> SELECT name > >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > >> WHERE objectCategory=''Person'' AND > >> sAMAccountName=''"+@ntid+"'' > >> ')" > >> Exec(@sql) > >> > >> Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR > >> Select [name] from ##temp_asdi_rnfn > >> OPEN tCUR > >> Fetch Next From tCUR Into @name > >> CLOSE tCUR > >> DEALLOCATE tCUR > >> RETURN @@Error > >> > > > > Why do you need a cursor to select one value from temp table? Why not just > > select the value directly from the global temp table: > > > > Select TOP 1 @name = [name] from ##temp_asdi_rnfn > > > > > > -- > > David Gugick > > Quest Software > > www.imceda.com > > www.quest.com > > > openquery() does not accept @variable. Your effort will be in vain. ;-)
-- Show quote-oj "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message news:31F25FD2-8E56-4DC4-9A3E-E5D26B2AC89D@microsoft.com... > Looks VERY plausible, thanks for the answer. I will test it tomorrow > morning > (Mon) and advise. Thanks for your thoughts. > > "Louis Davidson" wrote: > >> Why not: >> >> declare @query varchar(8000) >> set @query = >> ' >> SELECT name >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >> WHERE objectCategory=''Person'' >> AND sAMAccountName= ''' + @ntid + '''' >> >> SELECT @name = name FROM OPENQUERY(ADSI, >> @query) >> >> I might have missed something (like some syntax in my answer!) but this >> should work, right? >> >> -- >> ---------------------------------------------------------------------------- >> Louis Davidson - http://spaces.msn.com/members/drsql/ >> SQL Server MVP >> >> >> "David Gugick" <david.gugick-nospam@quest.com> wrote in message >> news:ODtgQ2%23gFHA.576@TK2MSFTNGP15.phx.gbl... >> > Kevin Bowker wrote: >> >> THE HISTORY: >> >> >> >> I have a linked server on SQL 2000 sp3 that points to Active >> >> Directory. Using this, I'm able to retrieve data for users based on >> >> their AD login credentials. I've wrapped this up in the following >> >> stored procedure which is working beautifully: >> >> >> >> CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID >> >> ( >> >> @ntid char(7), >> >> @name varchar(80) output >> >> ) >> >> AS >> >> >> >> SET QUOTED_IDENTIFIER OFF >> >> Declare @sql varchar (250), >> >> @retVal varchar(80) >> >> >> >> if(exists(Select name from tempdb.dbo.sysobjects >> >> where(name='##temp_asdi_rnfn'))) >> >> drop table tempdb.dbo.##temp_asdi_rnfn >> >> >> >> set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, >> >> ' >> >> SELECT name >> >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >> >> WHERE objectCategory=''Person'' AND >> >> sAMAccountName=''"+@ntid+"'' >> >> ')" >> >> Exec(@sql) >> >> >> >> Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR >> >> Select [name] from ##temp_asdi_rnfn >> >> OPEN tCUR >> >> Fetch Next From tCUR Into @name >> >> CLOSE tCUR >> >> DEALLOCATE tCUR >> >> RETURN @@Error >> >> >> > >> > Why do you need a cursor to select one value from temp table? Why not >> > just >> > select the value directly from the global temp table: >> > >> > Select TOP 1 @name = [name] from ##temp_asdi_rnfn >> > >> > >> > -- >> > David Gugick >> > Quest Software >> > www.imceda.com >> > www.quest.com >> >> >> Yeah, I was just guessing. Your other solution looks good (and I will guess
you have tested it.) Note that I admited that I was guessing! -- Show quote---------------------------------------------------------------------------- Louis Davidson - http://spaces.msn.com/members/drsql/ SQL Server MVP "oj" <nospam_ojngo@home.com> wrote in message news:e7Fd86ZhFHA.3436@tk2msftngp13.phx.gbl... > openquery() does not accept @variable. Your effort will be in vain. ;-) > > -- > -oj > > > > "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message > news:31F25FD2-8E56-4DC4-9A3E-E5D26B2AC89D@microsoft.com... >> Looks VERY plausible, thanks for the answer. I will test it tomorrow >> morning >> (Mon) and advise. Thanks for your thoughts. >> >> "Louis Davidson" wrote: >> >>> Why not: >>> >>> declare @query varchar(8000) >>> set @query = >>> ' >>> SELECT name >>> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >>> WHERE objectCategory=''Person'' >>> AND sAMAccountName= ''' + @ntid + '''' >>> >>> SELECT @name = name FROM OPENQUERY(ADSI, >>> @query) >>> >>> I might have missed something (like some syntax in my answer!) but this >>> should work, right? >>> >>> -- >>> ---------------------------------------------------------------------------- >>> Louis Davidson - http://spaces.msn.com/members/drsql/ >>> SQL Server MVP >>> >>> >>> "David Gugick" <david.gugick-nospam@quest.com> wrote in message >>> news:ODtgQ2%23gFHA.576@TK2MSFTNGP15.phx.gbl... >>> > Kevin Bowker wrote: >>> >> THE HISTORY: >>> >> >>> >> I have a linked server on SQL 2000 sp3 that points to Active >>> >> Directory. Using this, I'm able to retrieve data for users based on >>> >> their AD login credentials. I've wrapped this up in the following >>> >> stored procedure which is working beautifully: >>> >> >>> >> CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID >>> >> ( >>> >> @ntid char(7), >>> >> @name varchar(80) output >>> >> ) >>> >> AS >>> >> >>> >> SET QUOTED_IDENTIFIER OFF >>> >> Declare @sql varchar (250), >>> >> @retVal varchar(80) >>> >> >>> >> if(exists(Select name from tempdb.dbo.sysobjects >>> >> where(name='##temp_asdi_rnfn'))) >>> >> drop table tempdb.dbo.##temp_asdi_rnfn >>> >> >>> >> set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, >>> >> ' >>> >> SELECT name >>> >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >>> >> WHERE objectCategory=''Person'' AND >>> >> sAMAccountName=''"+@ntid+"'' >>> >> ')" >>> >> Exec(@sql) >>> >> >>> >> Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR >>> >> Select [name] from ##temp_asdi_rnfn >>> >> OPEN tCUR >>> >> Fetch Next From tCUR Into @name >>> >> CLOSE tCUR >>> >> DEALLOCATE tCUR >>> >> RETURN @@Error >>> >> >>> > >>> > Why do you need a cursor to select one value from temp table? Why not >>> > just >>> > select the value directly from the global temp table: >>> > >>> > Select TOP 1 @name = [name] from ##temp_asdi_rnfn >>> > >>> > >>> > -- >>> > David Gugick >>> > Quest Software >>> > www.imceda.com >>> > www.quest.com >>> >>> >>> > > And again, thanks for your thoughts. Actually, neither solution worked.
Oj's looked good to and would work under 'normal' conditions. Active Directory, however, does not return a table of data that can be filtered with a where clause. All of the parameters must be sent in the initial query. Show quote "Louis Davidson" wrote: > Yeah, I was just guessing. Your other solution looks good (and I will guess > you have tested it.) Note that I admited that I was guessing! > > -- > ---------------------------------------------------------------------------- > Louis Davidson - http://spaces.msn.com/members/drsql/ > SQL Server MVP > > > "oj" <nospam_ojngo@home.com> wrote in message > news:e7Fd86ZhFHA.3436@tk2msftngp13.phx.gbl... > > openquery() does not accept @variable. Your effort will be in vain. ;-) > > > > -- > > -oj > > > > > > > > "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message > > news:31F25FD2-8E56-4DC4-9A3E-E5D26B2AC89D@microsoft.com... > >> Looks VERY plausible, thanks for the answer. I will test it tomorrow > >> morning > >> (Mon) and advise. Thanks for your thoughts. > >> > >> "Louis Davidson" wrote: > >> > >>> Why not: > >>> > >>> declare @query varchar(8000) > >>> set @query = > >>> ' > >>> SELECT name > >>> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > >>> WHERE objectCategory=''Person'' > >>> AND sAMAccountName= ''' + @ntid + '''' > >>> > >>> SELECT @name = name FROM OPENQUERY(ADSI, > >>> @query) > >>> > >>> I might have missed something (like some syntax in my answer!) but this > >>> should work, right? > >>> > >>> -- > >>> ---------------------------------------------------------------------------- > >>> Louis Davidson - http://spaces.msn.com/members/drsql/ > >>> SQL Server MVP > >>> > >>> > >>> "David Gugick" <david.gugick-nospam@quest.com> wrote in message > >>> news:ODtgQ2%23gFHA.576@TK2MSFTNGP15.phx.gbl... > >>> > Kevin Bowker wrote: > >>> >> THE HISTORY: > >>> >> > >>> >> I have a linked server on SQL 2000 sp3 that points to Active > >>> >> Directory. Using this, I'm able to retrieve data for users based on > >>> >> their AD login credentials. I've wrapped this up in the following > >>> >> stored procedure which is working beautifully: > >>> >> > >>> >> CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID > >>> >> ( > >>> >> @ntid char(7), > >>> >> @name varchar(80) output > >>> >> ) > >>> >> AS > >>> >> > >>> >> SET QUOTED_IDENTIFIER OFF > >>> >> Declare @sql varchar (250), > >>> >> @retVal varchar(80) > >>> >> > >>> >> if(exists(Select name from tempdb.dbo.sysobjects > >>> >> where(name='##temp_asdi_rnfn'))) > >>> >> drop table tempdb.dbo.##temp_asdi_rnfn > >>> >> > >>> >> set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, > >>> >> ' > >>> >> SELECT name > >>> >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > >>> >> WHERE objectCategory=''Person'' AND > >>> >> sAMAccountName=''"+@ntid+"'' > >>> >> ')" > >>> >> Exec(@sql) > >>> >> > >>> >> Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR > >>> >> Select [name] from ##temp_asdi_rnfn > >>> >> OPEN tCUR > >>> >> Fetch Next From tCUR Into @name > >>> >> CLOSE tCUR > >>> >> DEALLOCATE tCUR > >>> >> RETURN @@Error > >>> >> > >>> > > >>> > Why do you need a cursor to select one value from temp table? Why not > >>> > just > >>> > select the value directly from the global temp table: > >>> > > >>> > Select TOP 1 @name = [name] from ##temp_asdi_rnfn > >>> > > >>> > > >>> > -- > >>> > David Gugick > >>> > Quest Software > >>> > www.imceda.com > >>> > www.quest.com > >>> > >>> > >>> > > > > > > > What others said about not using cursor and directly assigning @name is
valid. But it is certainly possible to convert it to an udf. e.g. create function dbo.fn_ResolveName (@ntid char(7)) returns varchar(80) as begin declare @name varchar(80) select @name=name FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName FROM ''LDAP:// DC=ad,DC=bls,DC=com'' WHERE objectCategory=''Person''')derived where sAMAccountName=@ntid return @name end go -- Show quote-oj "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message news:BB2147E3-023A-4780-9E9B-92A339879FFA@microsoft.com... > THE HISTORY: > > I have a linked server on SQL 2000 sp3 that points to Active Directory. > Using this, I'm able to retrieve data for users based on their AD login > credentials. I've wrapped this up in the following stored procedure which > is > working beautifully: > > CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID > ( > @ntid char(7), > @name varchar(80) output > ) > AS > > SET QUOTED_IDENTIFIER OFF > Declare @sql varchar (250), > @retVal varchar(80) > > if(exists(Select name from tempdb.dbo.sysobjects > where(name='##temp_asdi_rnfn'))) > drop table tempdb.dbo.##temp_asdi_rnfn > > set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, > ' > SELECT name > FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > WHERE objectCategory=''Person'' AND > sAMAccountName=''"+@ntid+"'' > ')" > Exec(@sql) > > Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR > Select [name] from ##temp_asdi_rnfn > OPEN tCUR > Fetch Next From tCUR Into @name > CLOSE tCUR > DEALLOCATE tCUR > RETURN @@Error > > THE PROBLEM: > > Although this works as designed, it also requires me to create a cursor > for > any resultset of values that I want to pass. To really get good use out > of > this, I need to put it in a user defined function so that it can be used > 'in-line' in a query for example: > > Select NTID, fn_ResolveName(NTID) As 'UserName' From TABLENAME > > THE QUESTION: > > How can I encapsulate this code into a UDF? Because I need to pass the > ntid > (login id) to the linked server, I have to encapsulate the tsql into a > variable and execute that variable. Of course, attempting to call Execute > from within a UDF raises an error. I'd be grateful for any ideas. Thanks, OJ. The answer you've posted is really slick and would probably work
with everything but ActiveDirectory. AD is rather odd and doesn't return a table of results without already having specifics in the Where clause that is sent to it. Therefore, when trying to get the results by placing the Where clause outside of the OpenQuery call, the result is always null. Thanks again (and a bunch!) for your time and thoughts on this problem. Very good ideas. Show quote "oj" wrote: > What others said about not using cursor and directly assigning @name is > valid. But it is certainly possible to convert it to an udf. > > e.g. > create function dbo.fn_ResolveName (@ntid char(7)) > returns varchar(80) > as > begin > declare @name varchar(80) > select @name=name > FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName > FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > WHERE objectCategory=''Person''')derived > where sAMAccountName=@ntid > return @name > end > go > > > -- > -oj > > > "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message > news:BB2147E3-023A-4780-9E9B-92A339879FFA@microsoft.com... > > THE HISTORY: > > > > I have a linked server on SQL 2000 sp3 that points to Active Directory. > > Using this, I'm able to retrieve data for users based on their AD login > > credentials. I've wrapped this up in the following stored procedure which > > is > > working beautifully: > > > > CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID > > ( > > @ntid char(7), > > @name varchar(80) output > > ) > > AS > > > > SET QUOTED_IDENTIFIER OFF > > Declare @sql varchar (250), > > @retVal varchar(80) > > > > if(exists(Select name from tempdb.dbo.sysobjects > > where(name='##temp_asdi_rnfn'))) > > drop table tempdb.dbo.##temp_asdi_rnfn > > > > set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, > > ' > > SELECT name > > FROM ''LDAP:// DC=ad,DC=bls,DC=com'' > > WHERE objectCategory=''Person'' AND > > sAMAccountName=''"+@ntid+"'' > > ')" > > Exec(@sql) > > > > Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR > > Select [name] from ##temp_asdi_rnfn > > OPEN tCUR > > Fetch Next From tCUR Into @name > > CLOSE tCUR > > DEALLOCATE tCUR > > RETURN @@Error > > > > THE PROBLEM: > > > > Although this works as designed, it also requires me to create a cursor > > for > > any resultset of values that I want to pass. To really get good use out > > of > > this, I need to put it in a user defined function so that it can be used > > 'in-line' in a query for example: > > > > Select NTID, fn_ResolveName(NTID) As 'UserName' From TABLENAME > > > > THE QUESTION: > > > > How can I encapsulate this code into a UDF? Because I need to pass the > > ntid > > (login id) to the linked server, I have to encapsulate the tsql into a > > variable and execute that variable. Of course, attempting to call Execute > > from within a UDF raises an error. I'd be grateful for any ideas. > > > It works just fine if you have valid/correct LDAP ADsPath. Also, make sure
you have proper mapping between local and remote login (i.e. take a look at sp_addlinkedsrvlogin). Post the exact error message and perhaps I can help further. create function dbo.fn_ResolveName (@ntid char(7)) returns varchar(80) as begin declare @name varchar(80) select @name=name FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName FROM ''LDAP://dc=pdxad2,dc=xxx,dc=com'' WHERE objectCategory=''Person'' ')derived where sAMAccountName=@ntid return @name end go select dbo.fn_ResolveName('eric') as [ADName] go ADName ---------------------------- Eric d. engineer -- Show quote-oj "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message news:B739A88D-6863-4E0C-8A44-F70A20D70232@microsoft.com... > Thanks, OJ. The answer you've posted is really slick and would probably > work > with everything but ActiveDirectory. AD is rather odd and doesn't return > a > table of results without already having specifics in the Where clause that > is > sent to it. Therefore, when trying to get the results by placing the > Where > clause outside of the OpenQuery call, the result is always null. > > Thanks again (and a bunch!) for your time and thoughts on this problem. > Very good ideas. > > "oj" wrote: > >> What others said about not using cursor and directly assigning @name is >> valid. But it is certainly possible to convert it to an udf. >> >> e.g. >> create function dbo.fn_ResolveName (@ntid char(7)) >> returns varchar(80) >> as >> begin >> declare @name varchar(80) >> select @name=name >> FROM OPENQUERY(ADSI, 'SELECT name,sAMAccountName >> FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >> WHERE objectCategory=''Person''')derived >> where sAMAccountName=@ntid >> return @name >> end >> go >> >> >> -- >> -oj >> >> >> "Kevin Bowker" <KevinBow***@discussions.microsoft.com> wrote in message >> news:BB2147E3-023A-4780-9E9B-92A339879FFA@microsoft.com... >> > THE HISTORY: >> > >> > I have a linked server on SQL 2000 sp3 that points to Active Directory. >> > Using this, I'm able to retrieve data for users based on their AD login >> > credentials. I've wrapped this up in the following stored procedure >> > which >> > is >> > working beautifully: >> > >> > CREATE PROCEDURE dbo.proc_ASDI_ResolveNameFromNTID >> > ( >> > @ntid char(7), >> > @name varchar(80) output >> > ) >> > AS >> > >> > SET QUOTED_IDENTIFIER OFF >> > Declare @sql varchar (250), >> > @retVal varchar(80) >> > >> > if(exists(Select name from tempdb.dbo.sysobjects >> > where(name='##temp_asdi_rnfn'))) >> > drop table tempdb.dbo.##temp_asdi_rnfn >> > >> > set @sql = "SELECT name into ##temp_asdi_rnfn FROM OPENQUERY(ADSI, >> > ' >> > SELECT name >> > FROM ''LDAP:// DC=ad,DC=bls,DC=com'' >> > WHERE objectCategory=''Person'' AND >> > sAMAccountName=''"+@ntid+"'' >> > ')" >> > Exec(@sql) >> > >> > Declare tCUR CURSOR FAST_FORWARD READ_ONLY FOR >> > Select [name] from ##temp_asdi_rnfn >> > OPEN tCUR >> > Fetch Next From tCUR Into @name >> > CLOSE tCUR >> > DEALLOCATE tCUR >> > RETURN @@Error >> > >> > THE PROBLEM: >> > >> > Although this works as designed, it also requires me to create a cursor >> > for >> > any resultset of values that I want to pass. To really get good use >> > out >> > of >> > this, I need to put it in a user defined function so that it can be >> > used >> > 'in-line' in a query for example: >> > >> > Select NTID, fn_ResolveName(NTID) As 'UserName' From TABLENAME >> > >> > THE QUESTION: >> > >> > How can I encapsulate this code into a UDF? Because I need to pass the >> > ntid >> > (login id) to the linked server, I have to encapsulate the tsql into a >> > variable and execute that variable. Of course, attempting to call >> > Execute >> > from within a UDF raises an error. I'd be grateful for any ideas. >> >> >>  |
|||||||||||||||||||||||
Other interesting topics