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 ...

Answer 1

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

Answer 2

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 ...

Answer 3

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 ...

Answer 4

Check if this one helps, it's a C# code to store and retrieve any files to/from database.

http://randypaulo.wordpress.com/2011/06/21/storing-files-to-database-and-downloading-using-asp-net/

---

Randy Aldrich Paulo

MCTS(BizTalk 2010/2006,WCF NET4.0), MCPD | My Blog

BizTalk Message Archiving - SQL and File
Automating/Silent Installation of BizTalk Deployment Framework using Powershell >
Sending IDOCs using SSIS

Answer 5

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 ...

Answer 6

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!

Answer 7

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 ...

Answer 8

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!

Answer 9

Thanks for the reply, but still the same result :-(

---

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