October 5, 2007 at 4:02 pm
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.
October 9, 2007 at 6:40 am
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
October 9, 2007 at 9:06 pm
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).
October 10, 2007 at 5:35 am
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
October 10, 2007 at 6:43 am
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
October 10, 2007 at 6:56 am
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
October 10, 2007 at 7:16 am
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?
October 11, 2007 at 8:16 am
How would you get @highpart_fromLDAP and @lowpart_fromLDAP from the query?
BTW, the bigint conversion returns the same error.
October 11, 2007 at 10:10 am
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?
October 11, 2007 at 3:51 pm
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.
August 8, 2008 at 10:08 am
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
September 11, 2008 at 9:46 am
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
December 30, 2008 at 1:10 pm
I can read integer8 type value only from a 64-bit SQL Server.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply