Monday, August 27, 2012

Querying Active Directory from SQL Server


This is quite easy except the restriction on the number of records fetched.
After sql server 2008, the limit is 901, so 

a simple query to AD from sql is

select * from
openquery(ADSI,'select  samaccountname, cn, employeeID,mail,distinguishedname  from ''LDAP://DC=prod,DC=companydomain,DC=com''
where objectClass = ''person'' and objectClass = ''user'' and objectClass = ''organizationalPerson''
and objectClass <> ''contact'' and msExchHideFromAddressLists <> TRUE and accountExpires <> 0
')
but this fails with the below error, though it displays the 901 records.

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

The same query using openrowset is:

SELECT * FROM OPENROWSET('ADSDSOObject', '','SELECT cn, ADsPath FROM ''LDAP://DC=prod,DC=mycompany,DC=com'' ')

I read somewhere that the restriction in sql 2000 is 1000 records
This limit is defined by the "maxpagesize" in Active Directory.

If you want to view this value.
logon to a windows 2003 server, run-> ntdsutil.exe
and you can see this value using the command "show values".

you can use the same query from the command prompt using "dsquery" and get the records
the query is something like this:
dsquery * dc=prod,dc=companydomain,dc=com -scope -base -attr sAMAccountname employeeid department cn employeetype

using dsquery you can get all the data with a "-limit" parameter and dump it into a text file.
this is one way of overcoming the 1000 record limit in sql server.

To add the linked server in sql:
eXEC sp_addlinkedserver @server = 'ADSI', @srvproduct = 'Active Directory Services 2.5', 
@provider = 'ADSDSOObject', @datasrc = 'adsdatasource'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'dd\sssss',@rmtpassword='ddddd'

sample queries:
SELECT top 900 * FROM OpenQuery
(ADSI, 'SELECT Name,SN,ST,cn,adspath FROM ''LDAP://OU=Users,OU=CSC-Managed,DC=au,DC=amp,DC=local''
where objectCategory=''Person'' and objectClass=''User''   ')

Monday, August 20, 2012

SAP got its own DB - MAXDB

yes SAP's MAXDB is the official SAP's DBMS.
I think SAP is now giving a strong competition to all other ERP vendors by having its own DB instead of depending on Oracle, Microsoft and other vendors.

looks like in the years to come all successful businesses would develop their own tools and products to support their IT systems instead of depending on an external vendor.
In such case it saves millions of dollars to the companies in license fees and support fees but of course they have to bear a one time investment to develop their products.

Wednesday, August 15, 2012

julian date conversion script


--date to julian 
(select (100+year('05-01-2010')-2000)*1000+ datediff(d, convert(datetime,('01/01/'+convert(char(4),year('05-01-2010')))),'05-01-2010')+1) 


-- julian to date format
select DATEADD(day, CAST(RIGHT(112228,3) AS int)-1,
dateadd(yy, 112228/1000, 0))