query a LDAP server from MS SQL

I try to connect to a LDAP server from SQL query analyzer with the following string

(SELECT * FROM OPENQUERY
(ADSI,'SELECT * FROM ''LDAP://DC=test-02,DC=test,CD=no'' '))

This resulted in a error meesage:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'ADSDSOObject'.
OLE DB error trace [Non-interface error: OLE DB provider ADSDSOObject returned DBPROP_STRUCTUREDSTORAGE without DBPROPVAL_OO_BLOB being supported].
OLE DB error trace [OLE/DB Provider 'ADSDSOObject' ICommandPrepare::Prepare returned 0x80040e14].

What is the problem ? It is something wrong with my db-setup, the query or what ? I try to do the same thing in a VB-program and that worked fine.Did you setup the linked server?

exec sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

instead of select * try and name them to see if you can at least return something

SELECT [Name],SN[Last Name]
FROM OPENQUERY( ADSI,
'SELECT Name,SN FROM ''LDAP://DC=test-02,DC=test,CD=no'' ')

also, if objects are in containers you can specify the container name by CN= or if in OU's - OU=

if you are only wanting users you can specify

WHERE objectCategory = ''Person'' AND objectClass = ''user''

HTHsql

Query - check for a string in stored procedure

Hi,

I would like to check if a string value exist in a string in sql
server stored procedure, e.g.

set @.testString = 'this is my test document.'
if (@.testString contains 'test')
begin
....
end

How do I do this in sql server stored procedure?

Thanks,
June...try this

Declare @.testString varchar(100)
set @.testString = 'this is my test document.'
if charindex('test',@.testString)>0

begin
....
end

Madhivanan

Qouted String

Hi all,
I have a string user_name and it is saved in table with single

qoutes, i mean if you view the table there are single qoutes on both
sides of user_name like 'amir', it is saved with single qoutes. The
problem is how can i write sql query with = comparison, i'm
using
select * from sasuser.Followup where user_name= ' 'amir ' ';.

I need to use single qoutes for the query, coz variable is string but
how can i use single qoutes which are with the name itself.

Thanx.Khan (amir@.programmer.net) writes:
> I have a string user_name and it is saved in table with single
> qoutes, i mean if you view the table there are single qoutes on both
> sides of user_name like 'amir', it is saved with single qoutes. The
> problem is how can i write sql query with = comparison, i'm
> using
> select * from sasuser.Followup where user_name= ' 'amir ' ';.
> I need to use single qoutes for the query, coz variable is string but
> how can i use single qoutes which are with the name itself.

To include the string delimiter in a string literal you need to double
it:

SELECT * FROM sasuser.Followup WHERE user_name = '''amit''';

Note that when you work from client code, you should use parameterised
SQL commands, which makes this a non-issue..

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||thanx erland,, its fine now.

q; sp_executesql and speed

Hello,
I am using sp_executesql this to pass parameter to sql string and I am
seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
done by another process. When it comes to speed and deadlock, would you
recomand not using sp_executesql?Hi
I don't think you blocking is caused by sp_executesql. Look at what you are
executing and make sure that it is properly optimized and the transactions
are short.
John
"JIM.H." wrote:
> Hello,
> I am using sp_executesql this to pass parameter to sql string and I am
> seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
> done by another process. When it comes to speed and deadlock, would you
> recomand not using sp_executesql?
>

q; sp_executesql and speed

Hello,
I am using sp_executesql this to pass parameter to sql string and I am
seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
done by another process. When it comes to speed and deadlock, would you
recomand not using sp_executesql?Hi
I don't think you blocking is caused by sp_executesql. Look at what you are
executing and make sure that it is properly optimized and the transactions
are short.
John
"JIM.H." wrote:

> Hello,
> I am using sp_executesql this to pass parameter to sql string and I am
> seeing deadlock between sp_prepexec which does UPDATE with another UPDATE
> done by another process. When it comes to speed and deadlock, would you
> recomand not using sp_executesql?
>

q:remove spaces

How can you remove spaces in the middle of a string, RTRIM and LTRIM does no
t
workYou can use the REPLACE function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A9862B7-8446-484B-99E7-19336D19EF8F@.microsoft.com...
> How can you remove spaces in the middle of a string, RTRIM and LTRIM does
not
> work

q:remove spaces

How can you remove spaces in the middle of a string, RTRIM and LTRIM does not
workYou can use the REPLACE function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:0A9862B7-8446-484B-99E7-19336D19EF8F@.microsoft.com...
> How can you remove spaces in the middle of a string, RTRIM and LTRIM does not
> work

Q: time convert

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.Jim,
I would try an Instr() function to find the position of the ":" and then
split the hours from the minutes (6) (30) with a mid() function; then sort on
the two values. But I usually find the hard way -- I'm sure there is an
easier way.
:-)
HTH
Jim
"JIM.H." wrote:
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by time.
> How should I do this?
> Thanks,
> Jim.
>|||If you want to do the conversion in the SQL query, you could try and
use the Convert(datetime, field) function, not sure if it will accept
just the time. If not prefix string, eg
Convert(datetime, '1901/01/01 ' + field)
Then you can do comparisions and extract specific parts (hours, minutes
etc).
Chris
Jim_OLAP wrote:
> Jim,
> I would try an Instr() function to find the position of the ":" and
> then split the hours from the minutes (6) (30) with a mid() function;
> then sort on the two values. But I usually find the hard way -- I'm
> sure there is an easier way.
> :-)
> HTH
> Jim
>
> "JIM.H." wrote:
> > Hello
> > I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to
> > convert these string to time with the same format (HH:MM) so that I
> > can sort by time. How should I do this?
> > Thanks,
> > Jim.
> >
> >

Q: time conversion

Hello
I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
these string to time with the same format (HH:MM) so that I can sort by time.
How should I do this?
Thanks,
Jim.You may find these articles helpful:
http://www.sommarskog.se/arrays-in-sql.html
http://www.bizdatasolutions.com/tsql/sqlarrays.asp
--
Keith
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, â?¦, in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||Some examples:
select * from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by cast(timecol as datetime)
order by case when len(timecol) < 5 then '0' + timecol else timecol end
select cast(timecol as datetime) as newcol from (
select '6:10' as timecol
union all select '11:30'
union all select '23:10'
union all select '6:03' )
as t1
order by newcol
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> Hello
> I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> these string to time with the same format (HH:MM) so that I can sort by
time.
> How should I do this?
> Thanks,
> Jim.
>|||or instead of
order by case when len(timecol) < 5 then '0' + timecol else timecol end
order by right('0' + timecol,5)
"Scott Morris" wrote:
> Some examples:
> select * from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by cast(timecol as datetime)
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> select cast(timecol as datetime) as newcol from (
> select '6:10' as timecol
> union all select '11:30'
> union all select '23:10'
> union all select '6:03' )
> as t1
> order by newcol
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > Hello
> > I have string 6:10, 11:30, 23:10, ., in one of column. I need to convert
> > these string to time with the same format (HH:MM) so that I can sort by
> time.
> > How should I do this?
> > Thanks,
> > Jim.
> >
>
>|||Good one. The left() function came to mind at first but I couldn't think of
a way to use it. I was looking at the wrong end!
"Nigel Rivett" <sqlnr@.hotmail.com> wrote in message
news:29C1C2D5-CB91-4102-85BC-07A7F18146E5@.microsoft.com...
> or instead of
> order by case when len(timecol) < 5 then '0' + timecol else timecol end
> order by right('0' + timecol,5)
>
> "Scott Morris" wrote:
> > Some examples:
> >
> > select * from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by cast(timecol as datetime)
> > order by case when len(timecol) < 5 then '0' + timecol else timecol end
> >
> > select cast(timecol as datetime) as newcol from (
> > select '6:10' as timecol
> > union all select '11:30'
> > union all select '23:10'
> > union all select '6:03' )
> > as t1
> > order by newcol
> >
> >
> > "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> > news:ED6FF7F2-7B23-488A-8881-88339EBD9103@.microsoft.com...
> > > Hello
> > > I have string 6:10, 11:30, 23:10, ., in one of column. I need to
convert
> > > these string to time with the same format (HH:MM) so that I can sort
by
> > time.
> > > How should I do this?
> > > Thanks,
> > > Jim.
> > >
> >
> >
> >