3

I'm querying AD to pull user details incl. thumbnail into an SQL service using an OPENQUERY call (see below):

CREATE TABLE #ADContacts(Name VARCHAR(100), Company VARCHAR(100), Title VARCHAR(100),
            Landline VARCHAR(100), Mobile VARCHAR(100), Email VARCHAR(100),
            ThumbnailPhoto VARBINARY(MAX), sAMAccountName VARCHAR(1000))

INSERT INTO #ADContacts(Name, Company, Title, Landline,
                        Mobile, Email, ThumbnailPhoto, sAMAccountName)
SELECT * FROM OpenQuery ( 
  ADSI,  
  'SELECT sAMAccountName, thumbnailPhoto, mail, mobile, telephoneNumber, title, Company, displayName
   FROM  ''LDAP://domain.int/OU=UsersOU,DC=domain,DC=int'' 
   WHERE objectClass =  ''User''
  ') AS tblADSI
ORDER BY displayname

Where the user's thumbnail jpeg is above 4K in size, I'm getting the following error message on the query:

OLE DB provider 'ADSDSOObject' for linked server 'ADSI' returned truncated data for column '[ADSDSOObject].thumbnailPhoto'. The actual data length is 9358 and truncated data length is 4000.

I'd massively appreciate your help in upgrading the size of the thumbnail we can import into AD / this application.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve Buchanan
  • 31
  • 2
  • Have you tried explict conversion of `thumbnailPhoto` to varbinary(max)? In your OPENQUERY query, try `CONVERT(VARBINARY(MAX),thumbnailPhoto)` in place of `thumbnailPhoto`. – digital.aaron Oct 03 '18 at 22:24
  • Using a `Convert(...)` does not work because OPENQUERY does not allow mutations, as it's not a true SQL query. @Steve Buchanan, were you able to find a solution to this issue? – Soturi Mar 15 '19 at 16:04

0 Answers0