Problem with Linked Server and Active Directory

• Friday, February 01, 2013 1:12 PM

   

   
  

  0

  I have a strange problem

  I’ve written a Query that gets information from our Active Directory via a linked server, ant that has worked fine for some time.

  Now my job has begun to fail and I’m getting this error:

  Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".

   

  When I look at my query and removes my INTO “tempTable and ISNULL(ROW_NUMBER() OVER ( ORDER BY department ), -999) 'id' ,

  Then the query  will execute, still with an error but with data in the result.

  What can be wrong and how can I debug this?

  My complete Query looks like this:

  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' ,
          null AS 'Photo',
  SUBSTRING(manager,4, (CHARINDEX(',',manager,0)-4)) AS 'Leder'
  INTO    ##adTemptable
  FROM   openquery
  (
  ADSI,'SELECT sAMAccountName, mail, title, displayName, telephoneNumber, mobile, sn, givenName,  department, thumbnailPhoto,userAccountControl, manager
  FROM ''LDAP://[removed]''
  WHERE objectCategory = ''Person''
  ')
  WHERE  UPPER(mail) LIKE '%@VERDO.DK' AND department IS NOT NULL

  
  

  
  

   

  ---

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

  • Reply
  • Quote