Including ActiveDirectory Data in a SQL query

Hi, I am hoping someone may have tried this before.......

Our application takes user details from Active directory, and stores the Guid in the database against an autonumber field for an easy to use userid. Any time the application wants to know anything about the user, it gets the information from Active Directory based upon the stored Guid.

I am writing a query to be used in generating reports, so I don't want to use .NET, Only SQL. I would like to be able to extract the username from Active Directory using SQL, so that the user's name, and not just their ID can be used in the report.

So far I have been able to extract all of my users names and their Guids from Active Directory using SQL, and I can extract the user Guid from our database. The problem I am having is comparing the 2! Visually they look the same, however the datatypes are different. If I convert the ActiveDirectory Guid to varchar I get gobbledegook, and if I convert the stored database Guid to varbinary then it's value is changed.

The query as it stands is below:

SELECT convert(varchar(50), [Name]) as FullName,objectGUID,ADSPath
FROM openquery(ADSI, 'SELECT name, objectGuid, ADSPath
FROM ''<LDAP Path>'' WHERE objectClass = ''User''')
WHERE objectGuid in(Select ADObjectGUID FROM users WHERE UserId='1')

I am working with SQL Server 2000 - as many of our clients are still using this system, so solutions based on SQL Server 2005 would not be practical. (I beleive there are ways of running .NET code from SQL 2005 which would solve this problem)

Any ideas anyone has would be much appreciated

Thanks

Gillian

Have you tried converting them to the uniqueidentifier datatype in SQL Server? That is the GUID datatype in SQL Server.|||

Thanks Cam - that did the trick.

For anyone trying to do the same kind of thing, the query looks like:

SELECT CONVERT(varchar(50), Rowset_2.name) AS FullName, Users.UserID

FROM OPENQUERY(ADSI, 'select name, objectGuid, ADSPath

from''<LDAP Path inserted here>''

whereobjectClass = ''User''') Rowset_2

INNER JOIN Users ON CONVERT (uniqueIdentifier, Rowset_2.objectGuid) = Users.ADObjectGUID

Cheers

Gillian

Include Tick ('') in varchar?

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

Hi,

If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.

Ray|||

I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much

|||

I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.

|||

Thanks all.

Millie.

|||

Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).

http://universitywriting.shu.ac.uk/punct/advice/d_double.htm

Ray

Include Tick ('') in varchar?

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

Hi,

If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.

Ray|||

I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much

|||

I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.

|||

Thanks all.

Millie.

|||

Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).

http://universitywriting.shu.ac.uk/punct/advice/d_double.htm

Ray

Include Tick ('') in varchar?

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

Hi,

If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.

Ray|||

I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much

|||

I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.

|||

Thanks all.

Millie.

|||

Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).

http://universitywriting.shu.ac.uk/punct/advice/d_double.htm

Ray

Include Tick ('') in varchar?

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

Hi,

If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.

Ray|||

I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much

|||

I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.

|||

Thanks all.

Millie.

|||

Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).

http://universitywriting.shu.ac.uk/punct/advice/d_double.htm

Ray

Include Tick ('') in varchar?

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

Hi,

If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.

Ray|||

I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much

|||

I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.

|||

Thanks all.

Millie.

|||

Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).

http://universitywriting.shu.ac.uk/punct/advice/d_double.htm

Ray

Include Tick ('') in varchar?

Hi guys,

Been working on an app that stores customer data. All has been well until an address contained the tick ' character, ie the address is John O'Grotes in Scotland. Now, all ticks are currently stripped from any input for obvious reasons, but if I wanted to add it, how do I do it, and do it safely?

Address3 Varchar(100) is where I want to put it.

Many thanks,

Millicent.

Hi,

If you want to store single inverted comma (tick) in the db you can double up them up in the insert statement (e.g. John O''Grotes), also have a look at SET QUOTED_IDENTIFIER ON/OFF too, this allows you to mix single and double inverted comma.

Ray|||

I have never heard them called "single inverted commas" Single quotes, yes, commas, not so much

|||

I have also not heard the reference "single inverted comma". I would also advise against a consistent practice of setting QUOTED_IDENTIFIER OFF so that you can use double quotes as a string delimiter. We have a couple of applications that do this thoughout and and as a result there is no hope of ever being able to use indexed views.

|||

Thanks all.

Millie.

|||

Absolutely nothing wrong with quotation marks, just didn't think of it at the time, I thought I would include a reference just in case you thought I was making up the inverted commas thing ;-).

http://universitywriting.shu.ac.uk/punct/advice/d_double.htm

Ray