SQL Server 2008 querying Active Directory

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server 2008 querying Active Directory

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2014-07-22 : 12:08:11

Hi Team:

You guys are my last resource. I am using SQL Server 2008 to query Active Directory using a link Server. The problem is that I can only query 901 - 1,000 rows everything over this number give me an error message. Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "ADsDSOObject" for linked server "ADSI".]

Here is an example of the query:
SELECT physicalDeliveryOfficeName, manager, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName, initials, displayName, mail, name, cn,
ADsPath, 'xx\' + sAMAccountName AS login, employeenumber
FROM OPENQUERY(ADSI,
'SELECT employeenumber, sAMAccountName, ADsPath, cn, name, mail, displayName, initials, givenName, sn,telephoneNumber,mobile, l,c,st,facsimileTelephoneNumber, title, manager, physicalDeliveryOfficeName
FROM ''LDAP://xx/ ou=Users, ou=accounts,dc=xx,dc=d-xxx,dc=xxx''
')
AS derivedtbl_3
WHERE (mail IS NOT NULL)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 13:05:41

Can the OPENQUERY query use TOP 900 and a WHERE clause?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-07-22 : 15:13:11

There are a number of limitations in this method. If you want to use an API based method - use Powershell - this post is an example of Exporting Active Directory Group Members http://www.sqlserver-dba.com/2014/06/how-to-export-active-directory-group-members-with-powershell-get-adgroupmember.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2014-07-22 : 15:48:55

Hi Tkizer:

I am trying to pull about 50,000 records. Sure the open query can use top 900 but how do I pull the remaining 49,100 records.

Thank You,

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 15:53:15

quote:
Originally posted by osirisa

Hi Tkizer:

I am trying to pull about 50,000 records. Sure the open query can use top 900 but how do I pull the remaining 49,100 records.

Thank You,

That's why I asked about a WHERE clause. Constrain the 900 rows and keep querying until done.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2014-07-22 : 16:32:50

Tara:

Thank You can you give me an example ? Thank You.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 16:54:32

Pseudocode:
create table #temp (.....)

while @@ROWCOUNT <> 0
insert into #temp(...)
SELECT physicalDeliveryOfficeName, manager, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName, initials, displayName, mail, name, cn,
ADsPath, 'xx\' + sAMAccountName AS login, employeenumber
FROM OPENQUERY(ADSI,
'SELECT TOP 900 employeenumber, sAMAccountName, ADsPath, cn, name, mail, displayName, initials, givenName, sn,telephoneNumber,mobile, l,c,st,facsimileTelephoneNumber, title, manager, physicalDeliveryOfficeName
FROM ''LDAP://xx/ ou=Users, ou=accounts,dc=xx,dc=d-xxx,dc=xxx''
')
AS derivedtbl_3
WHERE (mail IS NOT NULL) and NOT EXISTS (select * from #temp t where derivedtbl_3.employeenumber = t.employeenumber)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2014-07-22 : 17:35:00

USE ldap

create table #MyActiveDirectory
( [manager] [nvarchar](256) NULL,
[title] [nvarchar](256) NULL,
[facsimileTelephoneNumber] [nvarchar](256) NULL,
[st] [nvarchar](256) NULL,
[c] [nvarchar](256) NULL,
[l] [nvarchar](256) NULL,
[mobile] [nvarchar](256) NULL,
[telephoneNumber] [nvarchar](256) NULL,
[sn] [nvarchar](256) NULL,
[givenName] [nvarchar](256) NULL,
[displayName] [nvarchar](256) NULL,
[mail] [nvarchar](256) NULL,
[name] [nvarchar](256) NULL,
[cn] [nvarchar](256) NULL,
[ADsPath] [nvarchar](256) NULL,
[initials] [nvarchar](256) NULL,
[physicalDeliveryOfficeName] [nvarchar](256) NULL,
[login] [nvarchar](256) NULL,
[employeenumber] [nvarchar](256) NULL
)

while @@ROWCOUNT <> 0
insert into #MyActiveDirectory
SELECT physicalDeliveryOfficeName, manager, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName, initials, displayName, mail, name, cn,
ADsPath, 'xxxx\' + sAMAccountName AS login, employeenumber
FROM OPENQUERY(ADSI,
'SELECT TOP 900 employeenumber, sAMAccountName, ADsPath, cn, name, mail, displayName, initials, givenName, sn,telephoneNumber,mobile, l,c,st,facsimileTelephoneNumber, title, manager, physicalDeliveryOfficeName
FROM ''LDAP://xxxx/ ou=Users, ou=accounts,dc=xx,dc=d-xxx,dc=xx''
')
AS derivedtbl_3
WHERE (mail IS NOT NULL) and NOT EXISTS
(select * from #MyActiveDirectory t where derivedtbl_3.employeenumber = t.employeenumber)

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

Getting the following Error:
Msg 7321, Level 16, State 2, Line 26
An error occurred while preparing the query "SELECT TOP 900 employeenumber, sAMAccountName, ADsPath, cn, name, mail, displayName, initials, givenName, sn,telephoneNumber,mobile, l,c,st,facsimileTelephoneNumber, title, manager, physicalDeliveryOfficeName
FROM 'LDAP://nadrc/ ou=Users, ou=accounts,dc=na,dc=d-rco,dc=com'
" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 17:44:12

Seems like the problem is with your ADSI query. Run it without the stuff I added.

But the temp table doesn't match the layout of your query.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2014-07-23 : 14:55:01

Still having problems....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources