Updating a table from AD into SQL

Question

Post reply

Updating a table from AD into SQL

Kris-155042

Hall of Fame

Points: 3460
More actions
July 15, 2004 at 11:56 pm

#108277

I have a database that I want to update with data from AD. When the login matches the login from both tables I want to update the requested information. If the login id is in AD and not in the database then I want all the information from AD to be inserted. If the there is a login id in the database and not in AD I just want it to leave it as is.
This is what I have so far but I'm fairly confident this is wrong can someone help me with this script.
Update DT_Users
SET givenName = DT_Users.Firstname, sn = DT_Users.LastName,
company = DT_Users.Company, titla = DT_Users.Title, mail = DT_Users.Email
FROM DT_Users
Where sAMAccountName = DT_Users.Login
Select sAMAccountName AS Login, givenName AS FirstName, sn AS LastName,
company AS Company, title AS Title, mail AS Email
FROM OpenQuery(
ADSI,'<LDAP://Server/OU=User Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));
sAMAccountName, givenName, sn, company, title, mail,
adspath;subtree')
It pulls the data from AD and inserts any differences as long as the login exists in the SQL database, if it doesn't exist in the SQL DB then create it, if it does exist in SQL DB and not in AD then leave it as is.
I hope I haven't confused everyone.
Thanks in advance for your help
Thanks,
Kris

Viewing 12 posts - 1 through 11 (of 11 total)

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

R M Buda SSCrazy Points: 2225 More actions · Answer 1

An UPDATE statement cannot insert new rows into Sql Server.

You will need two statements: An UPDATE statement - you already have, and an INSERT statement.

The INSERT will use WHERE NOT EXISTS(...) OR WHERE NOT IN(...)

Maybe something like

INSERT INTO DT_USers(Login,...)

SELECT sAMAccountName,...

FROM OpenQuery(

ADSI,';(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

WHERE sAMAccountName NOT IN

(SELECT Login FROM DT_Users)

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

Thanks for that but...

I'm a little confused with ADSI,';(&(objectCategory=Person)); Is this instead of or as well as ADSI,'<LDAP://Server/OU=User Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

I done

INSERT INTO DT_USers(Login, FirstName, LastName, Company, Title, Email)

SELECT sAMAccountName, givenName, sn, company, title, mail

FROM OpenQuery(

ADSI,';(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

WHERE sAMAccountName NOT IN

(SELECT Login FROM DT_Users)

I'll take a wild stab in the dark and guess this is wrong

Thanks in advance

Thanks,

Kris

R M Buda SSCrazy Points: 2225 More actions · Answer 3

You are right about the LDAP query. Somehow my cut and paste did not work.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

I get an error message when I run the following script

Server: Msg 446, Level 16, State 9, Line 2

Cannot resolve collation conflict for equal to operation.

INSERT INTO DT_USers(Login, FirstName, LastName, Company, Title, Email)

SELECT sAMAccountName, givenName, sn, company, title, mail

FROM OpenQuery(

ADSI,'<LDAP://Server/OU=User Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

WHERE sAMAccountName NOT IN

(SELECT Login FROM DT_Users)

My other question is don't I need to have somewhere that if Login = sAMAccountName then update the data, not just if it doesn't exist.

Thanks again for your help.

Thanks,

Kris

R M Buda SSCrazy Points: 2225 More actions · Answer 5

If you do

sp_help DT_Users

you will see a collation name such as SQL_Latin1_General_CP1_CI_AS

You can then use the syntax belowm, substituting the actual collation of your SQL Server data.

.....

WHERE sAMAccountName COLLATE SQL_Latin1_General_CP1_CS_AS NOT IN

(SELECT Login FROM DT_Users)

I have used collations but not the AD so let us know how it goes. I would be interested to know what collation the AD data is.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 6

Thanks, that's worked. However the script is not doing what I want it to do.

INSERT INTO DT_USers(Login, FirstName, LastName, Company, Title, Email)

SELECT sAMAccountName, givenName, sn, company, title, mail

FROM OpenQuery(

ADSI,'<LDAP://Server/OU=User Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

WHERE sAMAccountName NOT IN

(SELECT Login FROM DT_Users)

This is only inserting a line if the login doesn't exist. I need it to update Login, FirstName, LastName, Company, Title, Email if it does exist as well, not create a new line.

I also need it to add a password in the password column if it adds a new entry. This can be a default of '0000' it doesn't really matter.

Thanks,

Kris

R M Buda SSCrazy Points: 2225 More actions · Answer 7

You will need 2 SQL statements:

1. An UPDATE statement to cope with updating existing data - your statement in the first post in this thread looks OK I think.

2. An INSERT statement to insert new data.

So I think what you need to do is run your original UPDATE as well as the INSERT we have been discussing. You could do them in either order.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 8

Neither one of these statements work. The first one doesn't recognise givenName and sAMAccountName.

Update DT_Users

SET givenName = DT_Users.Firstname, sn = DT_Users.LastName,

company = DT_Users.Company, title = DT_Users.Title, mail = DT_Users.Email

FROM DT_Users

Where sAMAccountName = DT_Users.Login

Select sAMAccountName AS Login, givenName AS FirstName, sn AS LastName,

company AS Company, title AS Title, mail AS Email

FROM OpenQuery(

ADSI,'<LDAP://Server/OU=User Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

This one is inserting a new line if any of the columns are different. I don't want a new line I just want it to update the current one. The only time it should add a new line is when Login doesn't exist.

INSERT INTO DT_USers(Login, FirstName, LastName, Company, Title, Email)

SELECT sAMAccountName, givenName, sn, company, title, mail

FROM OpenQuery(

ADSI,';(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

WHERE sAMAccountName NOT IN

(SELECT Login FROM DT_Users)

Thanks again for your help

Thanks,

Kris

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 9

O.K, I've tried a new tactic, but I'm still coming up with an error, here's the script followed by the error message. Any help is grateful.

EXEC sp_addlinkedserver

'ADSI',

'Active Directory Services 2.5',

'ADSDSOObject',

'adsdatasource'

EXEC sp_addlinkedsrvlogin

'ADSI',

'false',

NULL,

NULL

-- Create a temp table that contains the same data as ADSI ??? Whatever that is

select *

into TMP_Accounts

from OpenQuery(ADSI,'< LDAP://Server/OU=User <ldap://Server/OU=User>

Accounts,DC=company,DC=com,DC=au>;(&(objectCategory=Person));

sAMAccountName, givenName, sn, company, title, mail,

adspath;subtree')

-- Use the temp table to drive the update of existing records in DT_Users

update DT_Users

set Firstname = A.givenName

, LastName = A.sn

, Company = A.company

, Title = A.title

, Email = A.mail

from DT_Users U

, TMP_Accounts A

where U.Login = A.sAMAccountName

-- Insert new records that are missing from DT_Users

insert into DT_Users

( Login

, FirstName

, LastName

, Company

, Title

, Email

)

select sAMAccountName

, givenName

, sn

, company

, title

, mail

from TMP_Accounts

where sAMAccountName not in (select Login

from DT_Users)

Error Message:

Server: Msg 446, Level 16, State 9, Line 2

Cannot resolve collation conflict for equal to operation.

Thanks to anyone who can help

Thanks,

Kris

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 10

Try:

update DT_Users

set Firstname = A.givenName

, LastName = A.sn

, Company = A.company

, Title = A.title

, Email = A.mail

from DT_Users U

, TMP_Accounts A

where U.Login = A.sAMAccountName COLLATE database_default

Make sure that the character columns in tables TMP_Accounts and DT_Users have the same collation.

/rockmoose

You must unlearn what You have learnt

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 11

Thanks for that, you gave me the right idea. I tried this and it still didn't work.

update DT_Users

set Firstname = A.givenName

, LastName = A.sn

, Company = A.company

, Title = A.title

, Email = A.mail

from DT_Users U

, TMP_Accounts A

where U.Login = A.sAMAccountName COLLATE database_default

but I changed where U.Login = A.sAMAccountName COLLATE database_default to where U.Login = A.sAMAccountName COLLATE SQL_Latin1_General_CP1_CS_AS and it worked perfectly.

Thanks so much for that.

Thanks,

Kris