July 27, 2015 at 3:09 pm
Hello,
While I would describe myself as "relatively competent" in TSQL to the casual observer, my skills are (at least in comparison to most of the folks here) is effectively rank amateur with no real experience or skill at designing queries with Best Practices and performance optimisation in mind.
First, please allow me to describe the problem that I'm trying to solve. I have a table that happens to reference some Active Directory objects. One of the value in that table needs to be substituted with something else that I wanted to query directly from AD.
So, my query started out something like this:
CREATE FUNCTION GetDomainNetbiosName
(
@DomainDNS as varchar(MAX)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @DomainNetbiosName as varchar(255)
DECLARE @sqlquery AS varchar(MAX)
DECLARE @params AS nvarchar(255)
SET @sqlquery = 'SELECT @DomainNetbiosName = nETBIOSName
FROM OPENQUERY(ADSI, ''<LDAP://CN=Partitions,CN=Configuration,DC=mydomain,DC=com>;(dnsRoot='+@DomainDNS+');nETBIOSName;OneLevel'''
SET @params = N'@DomainNetbiosName varchar(255) OUTPUT'
EXECUTE sp_executeSQL @sqlquery, @params, @DomainNetbiosName = @DomainNetbiosName OUTPUT
RETURN @DomainNetbiosName
END;
GO
SELECT GetDomainNetbiosName([Domain]),
, [Value1]
, [Value2]
FROM [TABLE1]
END;
GO
After finding this failed as apparently you can't have dynamic SQL (or stored procedures) within a function, I've been struggling to figure out what the "right" way to accomplish my goal is. I.e., what is the "by the book" way that you're supposed to solve this kind of problem? Am I supposed to create a Temp table? If so, I'm still not sure how to iteratively process each record.
Any thoughts are most welcome!
I also have a question about how to query the rootDSE in Active Directory, so that "DC=mydomain,DC=com" is not hard-coded in that query, but that would seem to warrant a separate post.
Thanks!
July 27, 2015 at 4:01 pm
Further investigation has shown a couple of things.
First, I started experimenting with querying all of the values from CN=Partitions,CN=Configuration,DC=domain,DC=com and storing them in a temp table, however of the values that I'm interested in (nETBIOSName and dnsRoot), dnsRoot appears to be multi-valued. This appears to be a problem, as the ADSI provider for SQL doesn't seem to support multi-value attributes. Attempts to retrieve that value provide me the error:
Msg 7346, Level 16, State 2, Line 3
Cannot get the data of the row from the OLD DB provider "ADsDSOObject" for linked server "ADSI". Could not convert the data value due to reasons othen than sign mismatch or overflow.
Second, it would appear to me that the general consensus for these kinds of question appears to be to use SQL CLR. This would also appear to be the same answer for my side question, relating to querying the rootDSE.
Thanks again!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply