December 19, 2008 at 4:23 am
OK, I've trawled for a goodly while for an answer to this issue, but can find nothing useful, so either this is an arcane problem or I'm rubbish at googling.....
I have a linked server to our AD, and it's been working fine for ages. However, now that some of the custom extension attributes in AD have become more heavily used, I've discovered the columns in the results from my openquery seem to be capped at 256 characters.
The query I'm using is as follows:
SELECTName AS fullname,
distinguishedname,
objectGUID,
company,
department,
division,
extensionattribute1,
extensionattribute2,
extensionattribute3,
extensionattribute4,
extensionattribute5,
extensionattribute6,
extensionattribute7,
extensionattribute14,
extensionattribute15,
l,
physicaldeliveryofficename
FROMOPENQUERY(
ADSI,
'SELECT Name,
distinguishedname,
objectGUID,
company,
department,
division,
extensionattribute1,
extensionattribute2,
extensionattribute3,
extensionattribute4,
extensionattribute5,
extensionattribute6,
extensionattribute7,
extensionattribute14,
extensionattribute15,
l,
physicaldeliveryofficename
FROM ''LDAP://xxx.yyyy/ DC=xxx,DC=yyyy''
WHERE objectCategory = ''Person'' AND
objectClass = ''User''')
If I look at the AD records through (for instance) an LDAP browser, I can see some of the attributes (such as extensionattribute14) contain up to about 650 characters. If I amend my SQL to cast extensionattribute14 to allow for more characters, the cast works fine but the column still contains only 256 characters of data as a maximum. Oh, and yes I have changed my SSMS settings so it will allow more than 256 characters per column in the query results.
So is this an ADSI limitation? Is it just an ADSI limitation with the SQL dialect? Am I being dumb and missing a simple configuration parameter out somewhere? Net result is that I'd like to be able to retrieve the full contents of AD attributes containing more than 256 characters into SQL Server, and I don't much care how I do it. Any and all suggestions gratefully accepted.
Semper in excretia, suus solum profundum variat
December 19, 2008 at 6:39 am
If you are using SSMS, then there are query options that limit the size of the text that it will display. The options are under Query Reusts..Text, and ..Grid.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 19, 2008 at 6:55 am
Thanks for that. Yes, I know, and my apologies that I didn't explain myself quite as clearly as I ought in my original post. However, I've already amended those settings to allow for column widths way bigger than I need now.
I've also tried an amended version of my script to do a select .... into newtable from ...... so the raw results of the ADSI query are dumped into a table for me to disseminate as part of my troubleshooting. However, that SQL creates the newtable with all the extensionattribute columns as nvarchar(256), so it seems pretty definite it's a limitation being imposed on the data returned, not on what SSMS is able to display.
I'm definitely puzzled.
Semper in excretia, suus solum profundum variat
December 19, 2008 at 7:04 am
Hmmm, what's your SET TEXTSIZE setting? (use Select @@TEXTSIZE to view it).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 19, 2008 at 7:08 am
Oops, looking at it again, that shouldn't matter if you've already used CAST(..) on the interior select. My guess would be that you are right: it probably is some limitation of SQL's interface to ADSI.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply