LDAP Query Returning Erroneous Values

Jan 24, 2007

I'm trying to query an LDAP server from a stored procedure written for the CLR but not getting the expected results.

The code is as follows:

<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub LDAP_UserExists(<Out()> ByRef exists As Boolean, ByVal username As SqlString)

Dim adspath As New StringBuilder()
adspath.Append(LDAP://[.......]/ou=Members/cn=)
adspath.Append(username)

If username.ToString().Length > 0 Then
Dim uobject As New DirectoryEntry(adspath.ToString(), "", "", System.DirectoryServices.AuthenticationTypes.Anonymous)
If Not (uobject Is Nothing) Then
exists = True
Else
exists = False
End If
End If

End Sub

The same code works fine from an ASP.NET. If I deploy the code and execute it with

exec LDAP_UserExists 'username'

I receive the error

Error converting data type varchar to bit.

And if I right-click and select "Execute Stored Procedure..." I receive @exists = 1 and Return Value = 0, regardless of the value I pass in as the username parameter.

Given that the same code works correctly on the ASP.NET page I suspect that this error has something to do with the <out()> parameter in the stored procedure declaration.

Can anyone suggest the correct method of performing this query?

View 1 Replies


ADVERTISEMENT

Query Returning Multiple Wrong Values

Jan 16, 2013

I've got this sql statement that keeps returning the wrong data. (it's related to a previous post, but is different)

Code:
SELECT C.NAME, OL.PART_ID, SL.SHIPPED_QTY
FROM CUSTOMER C
INNER JOIN USERS U ON C.ID = U.ID
INNER JOIN ORDERS O ON C.ID = O.ID
INNER JOIN ORDER_LINE OL ON O.ID = OL.ORDER_ID

[Code] .....

The qry pulls like this: (wrong way)

HTML Code:
company partID Qty
Acme Inc ABC123 3
Acme Inc ABC123 4
Acme Inc ABC123 100
Acme Inc KLM444 3
Acme Inc KLM444 4
Acme Inc KLM444 100
Acme Inc QRP456 3
Acme Inc QRP456 4
Acme Inc QRP456 100

It should be:

HTML Code:
Co. part Qty
Acme Inc ABC123 3
Acme Inc KLM444 4
Acme Inc QRP456 100

The Qty field has duplicates. I've tried various ways, but I cannot make this work.

View 14 Replies View Related

LDAP - HOW DO I CONNECT TO LDAP DB FROM SQL SERVER 2005?

Dec 12, 2006

This is something that should not be so difficult, but it is proving to be very cumbersome.
I need to query an LDAP database on a Linux server FROM a sql server, namely sql server 2005.
I have found bits and pieces, but can€™t seem to put it all together yet €“ it should be easy, but it€™s proving to be very difficult.
It seems like you can either link an LDAP server through sp_addlinkedserver OR through enterprise manager/sql server studio, you can create a LINKED SERVER.
The problem is HOW to do this.
Here are the parameters for the ldap server:
Server name: serverabc
Ldap info (which I don€™t know how to label) is: 'ou=Hosts,dc=mc,dc=vanderbilt,dc=edu'.
I was able to somehow get this to work last week, but now it€™s not working - it didn't return any data, as i don't have any ldap hierarchy specified, but at least it came back with "command completed successfully":
SELECT * FROM OPENQUERY( [serverabc],
'SELECT * FROM ''LDAP://serverabcu/ OU=Hosts,DC=mc,DC=vanderbilt,DC=edu''
')
With all the ldap usage out there, this is really frustrating not being able to find out how to do this.
Any help is greatly appreciated.
Thanks


View 3 Replies View Related

Query Users In A Security Group With LDAP

Aug 11, 2004

I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:

SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')

Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?

Thanks much.

View 3 Replies View Related

Query LDAP/ADSI For Group Members?

May 16, 2007

I am trying to get members of an Active Directory group by querying the AD server from Transact-SQL (SQL Server 2005). Although there does not seem to be any written list of LDAP attributes that can be queried in AD (or I am not finding it), I have gotten this far:



SELECT * FROM

OPENQUERY( MYSERVER,

'SELECT cn, msExchHomeServerName, userPrincipalName FROM ''LDAP://CN=Users,DC=MYSERVER,DC=COM'' WHERE userPrincipalName=''*'' ')



This gives me a user list. But I can't find the syntax or attribute name(s) to query in order to get the membership of a specific group - for example, the group "SQL_Developers".



Anybody out there familiar enough with LDAP, AD and OPENQUERY() to give me a hand?



Thanks....



Tom

View 2 Replies View Related

Query LDAP/Active Directory In Stored Procedure

Apr 28, 2004

I know you can access LDAP via ADO.NET and the ADsDSOObject Provider.

Is there away to do it within a SQL Server Stored Procedure?

Thanks,
Tim

View 2 Replies View Related

SQL 2005 LDAP Query Error: Msg 7321, Level 16

Aug 4, 2006

Hello I am trying to run a query via tsql against ad. Below is the error I am getting. I have read the http://msdn2.microsoft.com/en-US/library/ms190803.aspx and changed the domain but still having issues. Any help would be appreciated.

 

EXEC sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject', 'adsdatasource'

GO

SELECT *

FROM OPENQUERY( ADSI,

'SELECT Name, SN, ST

FROM ''LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL''

WHERE objectCategory = ''Person'' AND

objectClass = ''user''')

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT Name, SN, ST

FROM 'LDAP://ADSISrv/ OU=Users,DC=XXXXX,DC=LOCAL'

WHERE objectCategory = 'Person' AND

objectClass = 'user'" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".

View 14 Replies View Related

Transact SQL :: LDAP Query Checking When Created Within Last 7 Days

Nov 6, 2015

I currently have an ldap query in a stored procedure that is working fine but is checking the 'whenCreated' attribute against a hardcoded data.

SELECT * FROM OpenQuery (
  ADSI,  
  'SELECT  whenCreated,
  whenChanged,
  telephoneNumber,

[Code] .....

How can I modify the hardcoded date (''20130101000000.0Z'') to check against current_date - 7 days?

View 7 Replies View Related

Reporting Services :: Pass A Parameter To LDAP Query In SSRS

Aug 20, 2015

Is it possible to use a parameter in a ldap query using the ADsDSOObject provider?  I keep getting an error "The ICommandWithParameters interface is not supported by the "ADSDSOObject" provider".  Command parameters are unsupported with the current provider.

I don't have a linked server on my DB server to Active Directory so I'm just querying in the SSRS report design.  Here is my query for my dataset.  If I hard code an example it works.  Just doesn't work when I pass a parameter. I've tried making it an expression (= sign), Tried several syntax's, Tried everything I can think of.  Is this possible? or do I just need to push for a linked server?

="SELECT sAMAccountName, displayName, distinguishedName " +
"FROM 'LDAP://DC=xxxx,DC=xx,DC=xx,DC=xx,DC=xx,DC=xx' " +
"WHERE objectCategory = 'Person' " +
"AND objectClass = 'user' " +
"AND memberOf = '" + @GlobalGroup + "'"

View 3 Replies View Related

ADSI LDAP Query 1000 Record Limit For MSSQLServer Linked Server

Jul 23, 2005

Hi,I have successfully set and used a linked server to query ADSI.Since this question also concerns MSSQLServer, I've cross posted it --I hope this is not a breach of etiquette.I have successly created a view based on the linked server.Unfortunately, it only shows 1000 records, and there does not seem tobe any way to set the Page Size.I found the following:http://support.microsoft.com/defaul...kb;en-us;243281Which seems to imply that the default can be set by changing registrykey: "HKEY_CURRENT_USERSoftwarePoliciesMicrosoftWind owsDirectoryUI"I have set this key, and also set it for the user account under whichMSSQLServer runs. The value persists after a reboot. The Domain Grouppolicy sets the default to 15000.This behaviour is not restricted to the linked server. If I use thescript found here:http://hacks.oreilly.com/pub/h/1121 I can access morethan 1000 records, but only if I set the "Page Size" property. If Icomment it out to let the default hold, it is 1000.It must be settable SOMEWHERE or the whole linked server thing is ofvery limited use.At present, the best solution I've been able to come up with is to usethe above script modified to run as a DTS package. Yuck.TIA,BM

View 2 Replies View Related

Returning Two Values

Jul 30, 2007

I have a stored procedure that does all the dirty work for me. I'm just having one issue. I need it to run the total number of RECORDS, and I need it to return the total number of new records for TODAY. Here is part of the code:SELECT COUNT(ID) AS TotalCount FROM CounterSELECT   COUNT(*) AS TodayCount FROM Counter     WHERE     DATEPART(YEAR, visitdate) = Year(GetDate()) AND    DATEPART(MONTH, visitdate) = Month(GetDate()) AND    DATEPART(DAY, visitdate) = Day(GetDate())The statement works great, I just need to return TotalCount and TodayCount in one query. Is this possible?  

View 6 Replies View Related

Returning Values

Feb 18, 2008

Hi All

I have a stored procedure that returns a numeric value. I wish to call this procedure from within another one and have the returned value stored against a declared variable. I'm struggling with the syntax!!

Any help out there?

Thanks

Xo

View 9 Replies View Related

Returning Values From Sp

Sep 13, 2006

hi
can i return value(bool) from a sp based on the result and receive in my vb code.


suji

View 4 Replies View Related

MS SQL For Returning No Values

Nov 20, 2007

Hello,im having a problem integrating an SQL statement with my program,it works fine when theres a result but throws an error when there isno result,is there anyway in SQL to make up a row if there isnt one that matchesthe query for example in this i could put indicator values in thereturned fields then a simple check to see if its a proper field of myindicator fieldI know ISNULL works with single values but that only works if there isa row returnedDoes anyone know of any SQL syntax for MS SQL that when no row isreturned it makes on up and parses it backThank you in advance for your help!!Ben

View 4 Replies View Related

Erroneous Warning: RsMissingFieldInDataSet

Feb 26, 2008

Every time I build my project I get the following warnings:

[rsMissingFieldInDataSet] The data set €˜spMyProc€™ contains a definition for the Field €˜Status€™. This field is missing from the returned result set from the data source



and

[rsErrorReadingDataSetField] The data set €˜spMyProc' contains a definition for the Field €˜Status€™. The data extension returned an error during reading the field. There is no data for the field at position 4.



The report is working fine. The Status column IS present in the source Dataset, is used in the report, and displays perfectly.

I've tried refreshing the field list from the Data tab of the report designer and executing the query (a stored proc call to 'spMyProc'). All works well, yet these warnings persist. The only thing I can see is that 'Status' is a column alias in the select list of the proc, e.g.,
"select ..., Status = s.Description, ...".

How can I get rid of these spurious warnings?

View 6 Replies View Related

Returning Null Values

Jun 4, 2007

I have a simple table on my webpage which shows data from my database.
It all worked fine apart from one part. I wanted the select statement to select only NULL values from a column, as these are classed as open queries. Once I have closed the query a 0 or 1 will automatically be posted and will no longer have a NULL value.
I have a simple select statement (which I will post on here when I log in with my work computer) and at the end I have a WHERE Column = NULL. I have also tried WHERE column <> 0.0 AND column <>1.0 and that did not work.
If I only did WHERE column <> 1.0, i only get the 0.0 results and none of the NULL results, but if I have no WHERE statement I get all the results including the NULL values.
 
Oliver

View 6 Replies View Related

Using Identities And Returning Their Values

Aug 31, 2000

I have a table that contains an identity column. This column is updated in a stored procedure that has a linked trigger that updates another 'backup' table. How can I ensure that the value of the new row added in the table with the identity is passed back to calling client. ?

The value of @@identity is incorrect as it reflects the value in the 'backup table' not the original table. and use select max() on the original table within the stored procedure could be violated by other users attempting the same operation

View 2 Replies View Related

Returning Values From A Dynamic SQL

Feb 19, 2006

I have an accounting database which contains data from various years.The frontend is a VB.Net program. At the year end, the program createsnew voucher and transaction tables and creates new stored procedures forthem.I just append the 'new year' at the end and create themie, Vouchers2001, Vouchers2002, Vouchers2003Similarly Transactions2001, Transactions2002.The data for all the years is in the same database.Also, I maintain a table called 'Books' which contains the Years forwhich data is present in the Database. The Structure of the Books tableisBookID BookYear1 20012 20023 20034 2004My Problem is that i need to know the current balance of any ledger forany year. The method to calculate the balance for any year is to startfrom the Minimum year in the Books table and continue upto the requiredyear. The SQL is as follows.DECLARE @iLedgerID AS INT --will be passed as parameterDECLARE @iYear as INT --will be passed as parameterDECLARE @CurrentBalance as MONEYSET @iLedgerID =1DECLARE @MinBook as INTEGERDECLARE @String nVarchar(4000)SELECT @MinBook = Min(BookYear)FROM BooksWHILE @MinBook <= @iYearBEGINSET @String = ' DECLARE @TT Money ' + char(13) +' SELECT @TT = ISNULL( SUM( ISNULL(Debit,0) - ISNULL(Credit,0) ),0 )'+ ' FROM transactions' + CAST(@MinBook AS CHAR(4)) + ' LEFT OUTER JOINdbo.Vouchers' + CAST(@MinBook AS CHAR(4)) + ' ON dbo.Transactions' +CAST(@MinBook AS CHAR(4)) + '.VoucherID' + ' = dbo.Vouchers' +CAST(@MinBook AS CHAR(4)) + '.VoucherID ' +'WHERE (LedgerID = @iLedgerID)'EXEC sp_executesql @String, N'@iLedgerID Int', @iLedgerID */SET @MinBook = @MinBook + 1ENDNow this is just a sample code. It may have a few glitches. My questionisa) Do I have to create a dynamic sql if the name of the database is notknown ahead of time. If No thenb) I need to add the balance of each year to the grand total. How do ireturn a value from a dynamic sql.TIA*** Sent via Developersdex http://www.developersdex.com ***

View 6 Replies View Related

Returning Values From Stored Procedure

Aug 16, 2004

Hi,
How to return values from stored procedures?? I have a value whose variable would be set thru this sp and it should return this value. How to do this?

Thanks,

View 1 Replies View Related

Function Returning Middle Values

Mar 7, 2007

I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned.
i.e. SELECT * FROM GetMiddleValues (3);

If anyone has any ideas on how to accomplish this, that would be great.

Thanks

View 1 Replies View Related

Function Returning Multiple Values?

Mar 1, 2012

I have a table called tableA and i am fetching 10 rows from table.

select dept_id from tableA where branch = 'Chennai';

I got 10 records.

dept_id
-------
001
002
003
004
005
so n.....

Now i want to pass these dept_ids dynamically to a function parameter.

ie. exec function_name (@dept_id).

How do i write a function?

View 3 Replies View Related

Returning A List Of Different Column Values

Sep 7, 2012

My table has a column called Period i want to get a list of different periods example:
Period
1
2
31
1
2
4
12
31
2

then run an sql statement and should return you the following
Period
1
2
4
12
31

View 3 Replies View Related

Returning Values From SP To Perl Script.

Apr 2, 2008

I am just starting to learn about stored procs and how great they can make my world. I have been able to create a pro that does some data checking and inserts based on the results but what I have not been able to figure out is how would I pass a message from the proc to my Perl script that calls it? If anyone could point me in the right direction on what commands I should be looking into I would appreciate it.

--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia

View 1 Replies View Related

Returning Values From 2 Queries To A Single Row?

Sep 30, 2014

I have a query set that returns values as part of a data set, I need a new one to return values from two queries to a single row.

select '1' as thekey, 'Total Picks' as Tot,sum(prod_qty) as picks from exceed.aseld, exceed.csymh
where luis_id in ('I','E')
and aseld.whse_id = 1
and (
(aseld.batch_id between goal_beg_batch and goal_end_batch
and monitor_group = 'YK')

[code]....

is it possible to get the numbers from keys 1 & 2 on the same row in a new query?

Or if it is easier a query that with give me (completed picks/total picks) = a decimal I can feed to the display as a percentage.

View 3 Replies View Related

Returning Records That Exclude Top 10% Of Values

Mar 6, 2008

I have data that looks like below (columns are Timestamp, Offered, Answered and Delay). I'm looking to exclude returning records that have a value for Delay that are within the top 10% of values of that column. Are there any 2005 tricks where this can be accomplished in a simple statement?

2008-02-18 08:30:002322173
2008-02-18 08:45:002120174
2008-02-18 09:00:002425230
2008-02-18 09:15:002828277
2008-02-18 09:30:002522159

View 3 Replies View Related

Counting Items And Returning Values

Jul 20, 2005

I have a table of product orders. It contains a row for "platform" and Ineed to return how many times each platform is listed in the DBExample data for platform could be:XBOXXBOXXBOXPLAYSTATIONPLAYSTATIONGAMECUBEPLAYSTATIONI'd like the data to be returned asXBOX - 3PLAYSTATION - 3GAMECUBE - 1How would I go about doing this please?

View 1 Replies View Related

Troubles Returning Values With SQL Statement -

Mar 28, 2006

Hi, I just want to know the following.

Is it posible to write a SQL statement that searches all values in a table where the

e.g:

"Jobid = 1" and sort the result set according to the "CategoryID"?

then if you want to display the values, you can populate them according to the "CategoryID"?



and then also write the result set to a "text file", and catagorize everything according to the "categoryID" in the following format???



Category Number: 8 Description: Site

Date Labour Hours Rate Subtotal

10/05/2005 Artisan 20 95.00 1,900.00
10/06/2005 Labourer 7 45 315.00


Category Total 2,215.00



And then after that the next "categoryid"

Thank You, I really hope you understand what I mean.

your help will be greatly appreaciated.

View 4 Replies View Related

Returning Formated Real Values

Jan 21, 2008



I have some Sp that return real values, I''m using function like SELECT CONVERT(REAL,field) FROM .... to ensure that conversion and rounding is done.

When I got the values in my application (Datagrid) I receive values having even 9 decimals

How can efectively return only 2 decimals from my query?

Thanks

View 3 Replies View Related

Returning Values From Stored Procedure

Jun 9, 2006

Hi Using Following Stored Procedure,

Which always returns Null,

What s the error,



CREATE PROCEDURE prLoginAuth

(

@pStrUserName varchar(50),
@pStrPassword varchar(50),
@pOutput Varchar(20) Output
)

AS

Declare @V_Facilities Varchar(50)

SELECT Facilities=@V_Facilities From UserLoginFacilities where LoginID=(Select LoginID From UserLogin where LoginName=@pStrUserName and Password=@pStrPassword)

If(@V_Facilities=null)

Set @pOutput = @V_Facilities

Return @pOutput;

Else

Set @pOutput = @V_Facilities

Return @pOutput;

GO



Anyone correct this query , I want return the output from this procedure

Thanx in advance

Selva.R

View 3 Replies View Related

Erroneous Error Aggrigating Different Data Types

Jul 10, 2006

Im getting an error message stating that I'm trying to aggrigate values of different data types, but I don't believe I am. In a table footer, I have a sum(fields!revenue). When I preview the report, the field says Errror, and the output window has a message that the field formula is trying to aggrigate different data types. The query column is a money type. The Report Service is interpreting zero value rows as integers and other rows as money or maybe decimal. If there are no zero value rows, the formula works. I've tried all kinds of conversion gimmicks to make the Report Service see the items as money, without any luck.

Does anyone know what I'm doing wrong, or know a work around?

Thank you,
Charles

View 1 Replies View Related

Problem Returning Two Values From Stored Procedures

Jan 29, 2004

Hi, i am trying to return two values from SQL 2000 using a single stored procedure. The stored working fine in Query Analyser and returns the two values and two grids in the results window.

My problem is that when i execute the stored procedure using ADO.Net the dataset only has one of the values. e.g TId : 2, where it should read 'TId' : 2, 'ConfigPath': 'C:lah'

Please could anyone shed ligth on this problem?

here the code for the stored procedure:

CREATE PROCEDURE dbo.GetTillInfo
(
@TillIdR varchar(50),
@Password varchar(50)
)
AS

declare @TillId int
declare @configpath varchar(150)

IF Exists (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
BEGIN

set @TillIdR = (SELECT Id FROM Tills WHERE TillRef=@TillId and TillPassword=@Password)
select @TillIdR as 'TId'

set @configpath = (SELECT configpath from customer,tills where
tills.customerid = customer.id and tills.id = @login)
select @configpath as 'ConfigPath'
END
ELSE
BEGIN
set @TillIdR = 0
select @TillIdR as 'TId'
set @configpath =''
select @configpath as 'ConfigPath'
END
GO

View 1 Replies View Related

SP Causes A INSERT On Table With A Trigger. Not Returning Values ??

Feb 21, 2002

Sp which inserts inforamtion into a table works fine. The trigger on that table which then inserts information into another table works fine. Only problem is that the SP will not return anything to Visual Basic.. Anyone know how to fix it?

SPbob
INsert into b values(1,1,2,2)
select 0

(this is a cut up version of the sp just to show about the way it is formated)
Please help.

View 1 Replies View Related

Returning Parameter Values From Stored Procedure

Oct 16, 2001

I am having trouble using Output parameters. I have set up an Execute SQL Task to call a Stored Procedure. I am passing an input parameter and indicating 1 global variable for output to retrieve a unique value from the stored procedure call. When I execute the step, it completes successfully but nothing is returned in my output parameter for the unique value??? Below is the call that I use:

EXEC SYS_GENERATE_ID ?

-in the Execute SQL Task I click the Parameters button to set up the input:
Status = Parameter1
and the Output Variable Type:
Rowset = GUID.

the stored procedure:
CREATE PROCEDURE dbo.SYS_GENERATE_ID
(@Statusvarchar(20),
@GUID uniqueidentifier = NULL OUTPUT)
AS
BEGIN
SET @GUID = NEWID()
INSERT INTO dbo.ACTIVITY (GUID, STATUS)
VALUES (@GUID, @Status)
SET @GUID = convert(varchar(50), @GUID)
RETURN @@ERROR
END

A table is correctly populated but the GUID value does not make it back to the calling task??? The Global Variable Type under Package Properties has been changed to "Dispatch" and the value is "Not Displayable"???
Any help that is offered will be appreciated GREATLY!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved