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 1Cannot 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, employeenumberFROM 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_3WHERE (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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
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 KizerSQL Server MVP since 2007http://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 <> 0insert into #temp(...)SELECT physicalDeliveryOfficeName, manager, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName, initials, displayName, mail, name, cn, ADsPath, 'xx\' + sAMAccountName AS login, employeenumberFROM OPENQUERY(ADSI, 'SELECT TOP 900 employeenumber, sAMAccountName, ADsPath, cn, name, mail, displayName, initials, givenName, sn,telephoneNumber,mobile, l,c,st,facsimileTelephoneNumber, title, manager, physicalDeliveryOfficeNameFROM ''LDAP://xx/ ou=Users, ou=accounts,dc=xx,dc=d-xxx,dc=xxx''')AS derivedtbl_3WHERE (mail IS NOT NULL) and NOT EXISTS (select * from #temp t where derivedtbl_3.employeenumber = t.employeenumber)Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2014-07-22 : 17:35:00
|
USE ldapcreate 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 <> 0insert into #MyActiveDirectorySELECT physicalDeliveryOfficeName, manager, title, facsimileTelephoneNumber, st, c, l, mobile, telephoneNumber, sn, givenName, initials, displayName, mail, name, cn, ADsPath, 'xxxx\' + sAMAccountName AS login, employeenumberFROM OPENQUERY(ADSI, 'SELECT TOP 900 employeenumber, sAMAccountName, ADsPath, cn, name, mail, displayName, initials, givenName, sn,telephoneNumber,mobile, l,c,st,facsimileTelephoneNumber, title, manager, physicalDeliveryOfficeNameFROM ''LDAP://xxxx/ ou=Users, ou=accounts,dc=xx,dc=d-xxx,dc=xx''')AS derivedtbl_3WHERE (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 26An 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, physicalDeliveryOfficeNameFROM '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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2014-07-23 : 14:55:01
|
Still having problems.... |
|
|
|
|
|