OPENROWSET and AD - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

OPENROWSET and AD

Author

Topic

cfoushee
Starting Member

2 Posts

Posted - 2010-02-16 : 10:01:36

I've successfully used OPENROWSET to connect to our AD Server. This is really cool BTW. However, I've discovered the information I really need is being stored in a multi-line AD field that doesn't convert to a SQL datatype. Even though this is a multi-line AD datatype, only one line is being used. All I need is the first 7 characters of the first line in this field. Unfortunately, I can't change it to a single-line datatype. I also haven't been able to find any way to cast this field into another datatype or a way to "JOIN" the array into a single string. Any ideas?

BELOW is an example of the openrowset connection. The field in question is description.

SELECT Name,description,displayName,givenname,distinguishedName, SAMAccountName
FROM
OPENROWSET('ADSDSOObject','adsdatasource'; 'DC1\username';'PASSWORD',
'SELECT Name,description,displayName,givenname,distinguishedName, SAMAccountName
FROM ''LDAP://DC1.DC2/ou=OU1,ou=OU2,dc=DC1,dc=DC2''
WHERE SAMAccountName=''jsmith''')

The error is [COLUMN_NAME=description STATUS=DBSTATUS_E_CANTCONVERTVALUE]

Thanks for any assistance.

venkat09
Starting Member

17 Posts

Posted - 2010-03-02 : 04:37:08

STATUS=DBSTATUS_E_CANTCONVERTVALUE might mean that the Content in Description column has a datatype which couldnot be understood by SQL server.

If you use SQl server 2000, the max length of an nvarchar variable is 4000.

Check if you have the Description (where SAMAccountName = 'jsmith') has more than 4000 characters.

Correct me if I approached it the wrong way.

Regards,
Renuka Prasad

cfoushee
Starting Member

2 Posts

Posted - 2010-03-02 : 10:43:42

Thanks for the reply

The problem seems to be when SQL tries to cast the description field to a SQL datatype. For example, if I leave the description field in the AD select, but remove it from the SQL select the query works:

SELECT Name,displayName,givenname,distinguishedName, SAMAccountName
FROM
OPENROWSET('ADSDSOObject','adsdatasource'; 'DC1\username';'PASSWORD',
'SELECT Name,description,displayName,givenname,distinguishedName, SAMAccountName
FROM ''LDAP://DC1.DC2/ou=OU1,ou=OU2,dc=DC1,dc=DC2''
WHERE SAMAccountName=''jsmith''')

Since I only need the first 7 chars of the description field, I was hoping something like this would work:

SELECT Name,displayName,description,givenname,distinguishedName, SAMAccountName
FROM
OPENROWSET('ADSDSOObject','adsdatasource'; 'DC1\username';'PASSWORD',
'SELECT Name,left(description,7) description,displayName,givenname,distinguishedName, SAMAccountName
FROM ''LDAP://DC1.DC2/ou=OU1,ou=OU2,dc=DC1,dc=DC2''
WHERE SAMAccountName=''jsmith''')

However, I think the description field in AD is a multi-line data type.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources