none
Query Active Directory + Problem with thumbnailPhoto

    Question

  • Hi<o:p></o:p>

    I have a problem and I don’t know if it is my SQL Query, so here goes

    <o:p></o:p>

    I have a view on my SQL server that Queries our Active Directory. I can see that there is data in the table.<o:p></o:p>

    But when I try to use the Image in some C# code I get an error on 60% of the images with the exception header missing or corrupted.

    My view is built with this Query:

     

    select * from openquery

    (

    ADSI,'SELECT sAMAccountName, mail, title, displayName, telephoneNumber, mobile, sn, givenName,  department, thumbnailPhoto

    FROM ''LDAP:[REMOVED]''

    WHERE objectCategory = ''Person''

    ')  

    Do you have any idea where the problem is? The photos shows up fine in Outlook, SharePoint, lync etc. I’m pretty sure that the C# code works correctly. Hope you can help.

     

    Regards



    If only I had time to learn everything I wanted ...

    • Moved by Tom PhillipsModerator Wednesday, August 01, 2012 4:24 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    Wednesday, August 01, 2012 10:30 AM

All replies

  • Hello Jacob,

    If you know of a specific record that is giving you issues, could you try using BCP with a format file to pull that photo out? AD will render GIF, PNG, JPEG, and BMP from what I understand. I am wondering if somehow a different format was used than what you are attempting to render.

    Example

    Format File:

    9.0 1

    1 SQLBINARY 0 0 "" 1 ThumbnailPhoto ""


    Command Line:

    bcp "SELECT ThumbnailPhoto FROM MyTable WHERE ID = 12345" queryout Photo_test.jpeg -S ServerName -T-d My_Database -f Path_To_Format_File.fmt

    Try changing the file associations to see if it is a different format or if indeed there seems to be an issue.

    Edit: On second thought, if you have the header information available for each graphic format then you could test the first X bytes to see which type it was without having to export.

    -Sean


    Sean Gallardy, MCC | Blog



    Wednesday, August 01, 2012 12:52 PM
  • Hi Sean

    Thanks for your reply i will try it out first thing tomorrow when i come to work.

    -jacob


    If only I had time to learn everything I wanted ...

    Wednesday, August 01, 2012 3:11 PM
  • Hi Sean

    I've tried it now and i can't get the bcp to export any images, all the exported images comes with an error "file appears to be damaged..."  the image size are 3.96kb


    i've used this code

    EXEC master..xp_cmdshell 'bcp "SELECT [Photo] FROM [dbo].[VW_AD] WHERE id = 363" queryout "C:\MSSQL\OSAK_IMPORT_DATA\Photo_test.jpg" -T -d DataCollection -f "C:\MSSQL\OSAK_IMPORT_DATA\FormatFiles\imageformatfile.fmt" -N'


    my format file looks like this:

    10.0
    1
    1	SQLBINARY	0	0	""	1	Photo	""

    -Jacob


    If only I had time to learn everything I wanted ...

    Thursday, August 02, 2012 7:29 AM
  • Thursday, August 02, 2012 7:34 AM
  • After tying a LOT of different things i finally got this message from the SQL server

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

    When i select from the AD with the OpenQuery and create my view i have no control over the output datatype, the view's collum datatype is varbinary(4000), any idea how to change this?

    -Jacob


    If only I had time to learn everything I wanted ...

    Tuesday, August 07, 2012 8:13 AM
  • Can you try changing the datatype of the view column from Varbinary(4000) to varbinary(MAX).

    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, August 07, 2012 8:39 AM
  • Hi Latheesh

    I've tried with this script:

    SELECT  ISNULL(ROW_NUMBER() OVER ( ORDER BY department ), -999) 'id' ,
            CONVERT(NVARCHAR(25), givenName) AS Fornavn ,
            CONVERT (NVARCHAR(50), sn) AS Efternavn ,
            CONVERT(CHAR(5), UPPER(SUBSTRING(mail, CHARINDEX(mail, N'@'),
                                             CHARINDEX(N'@', mail)))) AS 'initialer' ,
            CONVERT(NVARCHAR(255), mail) AS Mail ,
            CONVERT(NVARCHAR(75), title) AS Stilling ,
            CONVERT(NVARCHAR(120), department) AS Afdeling ,
            CONVERT(NVARCHAR(13), telephoneNumber) AS Fastnet ,
            CONVERT(NVARCHAR(13), mobile) AS Mobil ,
            CASE WHEN userAccountControl = 2 THEN 'Account is Disabled'
                 WHEN userAccountControl = 16 THEN 'Account Locked Out'
                 WHEN userAccountControl = 17
                 THEN CONVERT (VARCHAR(48), 'Entered Bad Password')
                 WHEN userAccountControl = 32
                 THEN CONVERT (VARCHAR(48), 'No Password is Required')
                 WHEN userAccountControl = 64
                 THEN CONVERT (VARCHAR(48), 'Password CANNOT Change')
                 WHEN userAccountControl = 512 THEN 'Normal'
                 WHEN userAccountControl = 514 THEN 'Disabled Account'
                 WHEN userAccountControl = 544
                 THEN 'Account Enabled - Require user to change password at first logon'
                 WHEN userAccountControl = 8192
                 THEN 'Server Trusted Account for Delegation'
                 WHEN userAccountControl = 524288
                 THEN 'Trusted Account for Delegation'
                 WHEN userAccountControl = 590336
                 THEN 'Enabled, User Cannot Change Password, Password Never Expires'
                 WHEN userAccountControl = 65536
                 THEN CONVERT (VARCHAR(48), 'Account will Never Expire')
                 WHEN userAccountControl = 66048
                 THEN 'Enabled and Does NOT expire Paswword'
                 WHEN userAccountControl = 66050
                 THEN 'Normal Account, Password will not expire and Currently Disabled'
                 WHEN userAccountControl = 66064
                 THEN 'Account Enabled, Password does not expire, currently Locked out'
                 WHEN userAccountControl = 8388608
                 THEN CONVERT (VARCHAR(48), 'Password has Expired')
                 ELSE CONVERT (VARCHAR(248), userAccountControl)
            END AS 'Disabled' ,
            CONVERT(NVARCHAR(75), givenName + ' ' + sn) AS 'DisplayName' ,
            CONVERT (VARBINARY(MAX), thumbnailPhoto) AS 'Photo'
    INTO    ##adTemptable
    FROM   openquery
    (
    ADSI,'SELECT sAMAccountName, mail, title, displayName, telephoneNumber, mobile, sn, givenName,  department, thumbnailPhoto,userAccountControl 
    FROM ''[REMOVED]''
    WHERE objectCategory = ''Person'' 
    ')
    WHERE    department IS NOT NULL
    But i still gets the same error on MANY rows

    OLE DB provider 'ADsDSOObject' for linked server 'ADSI' returned truncated data for column '[ADsDSOObject].thumbnailPhoto'. The actual data length is 6846 and truncated data length is 4000.
    OLE DB provider 'ADsDSOObject' for linked server 'ADSI' returned truncated data for column '[ADsDSOObject].thumbnailPhoto'. The actual data length is 7006 and truncated data length is 4000.
    OLE DB provider 'ADsDSOObject' for linked server 'ADSI' returned truncated data for column '[ADsDSOObject].thumbnailPhoto'. The actual data length is 6496 and truncated data length is 4000.


    If only I had time to learn everything I wanted ...

    Tuesday, August 07, 2012 10:29 AM
  • If you do not mind, Can you try with using OPENROWSET instead of Openquery here.

    I am not sure this would solve your issue. But just a wild guess.(I always do things differently...)


    Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!

    Tuesday, August 07, 2012 12:13 PM
  • Thanks for the reply, but still the same result :-(

    If only I had time to learn everything I wanted ...

    Tuesday, August 07, 2012 1:04 PM