Manipulating Integer8 (Active Directory)

  • Greetings.

    I want to query Active Directory from SQL Management Studio Express. As you can see, I need to get Integer8 type values:

    SELECT badPasswordTime, cn

    FROM OPENQUERY(ADSI,

    'SELECT badPasswordTime, cn

    FROM ''LDAP://DOMAINCTLR1/OU=Application Development,OU=IT,DC=My,DC=Domain,DC=com'' WHEREobjectClass = ''user'')

    I get this error:

    Msg 7346, Level 16, State 2, Line 1

    Cannot get the data of the row from the OLE DB provider "ADsDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.

    If I remove the badPasswordTime field, I get results. I know I could use VB to make a conversion (http://www.rlmueller.net/Integer8Attributes.htm), but is there a way to do it in T-SQL directly?

    Thanks.

  • What data type is the BadPasswordTime inside the database?

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • In the LDAP database, it's Integer8 (see above link for more info). With the right conversion, it could be stored as datetime. It's this conversion I can't do on T-SQL (if possible at all).

  • I'm sorry, I've got nothing that shows how to convert from the 64bit Integer8 data type into anything in SQL Server within TSQL.

    You may have a perfect opportunity to set up a CLR stored procedure so that you can call the VB code to open & convert this data from within TSQL. I'm not very knowledgable in CLR, but there's another forum and someone who hangs out over there may be able to help.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I don't know if this will help, but the big_int data type is 8 bytes in size, si it should be bign enough to hold the value; you probably already tried it, but does CONVERT(BIG_INT,badPasswordTime) help at all in the query?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I was thinking the same thing, but I did some searching Integer8 and it's actually a 64 bit value. I'm not convinced you can put it into a BigInt, but it might be worth a shot. I suspect that the CLR solution will be the best though.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • The better way will likely be to pull the .lowpart and .highpart separately. Also - are you trying to convert it to bigint or datetime?

    SQL datetime doesn't deal in NS, so we're going to need to remove some precision.

    Generically you'd need something like this handling for datetime:

    ...

    declare @lowpart bigint

    declare @highpart bigint

    declare @result datetime

    select @highpart=@highpart_fromLDAP*(2^32)/(10^6),

    @lowpart=@lowpart_fromLDAP/(10^6),

    @result=dateadd(ms,@lowpart,dateadd(ms,@highpart,0))

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • How would you get @highpart_fromLDAP and @lowpart_fromLDAP from the query?

    BTW, the bigint conversion returns the same error.

  • If I understand the LDAP description you should be able to refer to badpasswordtime.Lowpart and BadPasswordTime.HighPart (assuming badpasswordtime is a valid name - I didn't actually look that one up). It's supposed to be true for ALL integer8 fields.

    Have you tried simply defining it as a char(100) field and seeing what you get back? If you could show us specifically what came back - it might make it a little easier to help you with a conversion scheme (from this - to that)

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Hello.

    I tried what you said ([field].HighPart and [field].LowPart), and still couldn't get anything. This time, the errors returned are:

    Msg 7399, Level 16, State 1, Line 1

    The OLE DB provider "ADsDSOObject" for linked server "ADSI" reported an error. The provider did not give any information about the error.

    Msg 7350, Level 16, State 2, Line 1

    Cannot get the column information from OLE DB provider "ADsDSOObject" for linked server "ADSI".

    I get the desired results if I use whenCreated and whenChanged fields, which are of type GeneralizedTime in LDAP. So, for this query:

    SELECT cn, whenChanged

    FROM OPENQUERY(ADSI, 'SELECT cn, whenChanged FROM ''DOMAINCTLR1/OU=Application Development,OU=IT,DC=My,DC=Domain,DC=com'' WHERE objectClass = ''user'' AND samAccountName = ''rnave''')

    I get this result:

    cn whenChanged

    ---------------------------------------------------

    Ricardo Nave 2007-10-10 21:02:07.997

    Other Integer8-type fields (lastLogon, lastLogoff, badPasswordTime, lockoutTime and pwdLastSet) would just generate the same error, even tough it 'seems' to return something before "overflowing". This is the error:

    Msg 7346, Level 16, State 2, Line 1

    Cannot get the data of the row from the OLE DB provider "ADsDSOObject" for linked server "ADSI". Could not convert the data value due to reasons other than sign mismatch or overflow.

    It's the same thing if I try to CAST or CONVERT the selected fields outside the OPENQUERY function. OPENROWSET does the same thing.

  • Here my code to convert Integer8 to date time.

    The only problem is that I don't know sql language and I've to round to minute

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

    -- =============================================

    -- Author:

    -- Create date:

    -- Description:

    -- =============================================

    ALTER FUNCTION [dbo].[ProvaLdapFunction] (@parameter1 varchar(18))

    RETURNS datetime

    AS

    BEGIN

    declare @mathResult bigint

    declare @result datetime

    select

    @mathResult=CAST(left(@parameter1,14) AS bigint)/60000,

    @mathResult=@mathResult-157257960, -- -[(min from 01/01/1601 to 01/01/1900) + (120 min timezone)]

    @result=dateadd(mi,@mathResult,0)

    RETURN (@result)

    END

  • I ran into similar problems getting the accountexpires value.

    I created the following function:

    CREATE FUNCTION [dbo].[UTC2date] (@numSeconds BIGINT)

    RETURNS DATETIME

    AS BEGIN

    DECLARE @TimeBias AS INT

    EXEC master..xp_regread @rootkey = 'HKEY_LOCAL_MACHINE',

    @key = 'SYSTEM\CurrentControlSet\Control\TimeZoneInformation',

    @value_name = 'ActiveTimeBias', @value = @TimeBias OUTPUT

    SET @TimeBias = @TimeBias * 60

    DECLARE @date AS DATETIME

    SET @numSeconds = @numSeconds / 10000000 - 11644473600 - @TimeBias

    IF @numSeconds < 0

    OR @numSeconds > 2147483647

    BEGIN

    SET @numSeconds = 0

    END

    RETURN DATEADD(ss, @numSeconds, '01-01-1970 00:00:00')

    END

    And I use it in the following query:

    SELECT

    samAccountName

    ,EmployeeID

    ,dbo.UTC2date(accountExpires)

    ,sn

    ,givenName

    ,initials

    ,telephoneNumber

    ,msExchHideFromAddressLists

    ,department

    ,dbo.UTC2date(badPasswordTime )

    FROM

    OpenQuery(ADSI,'

    SELECT

    sAMAccountName

    ,EmployeeID

    ,accountExpires

    ,sn

    ,givenName

    ,initials

    ,telephoneNumber

    ,msExchHideFromAddressLists

    ,department

    ,badPasswordTime

    FROM ''LDAP://ou=Accounts,DC=mycom,DC=com''

    where objectClass = ''User''')

    I have had it in production for over 4 years without a problem.

    Bill Soranno
    MCP, MCTS, MCITP DBA
    Database Administrator
    Winona State University
    Maxwell 143

    "Quality, like Success, is a Journey, not a Destination" - William Soranno '92

  • I can read integer8 type value only from a 64-bit SQL Server.

  • BTW, this is explained a little more

    EDIT:

    HERE

    (sorry, that was the wrong link)

    HERE

    (and again, they moved my link)

    HERE[/url]

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply