Including ActiveDirectory Data In A SQL Query
Jul 6, 2007
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
View 4 Replies
ADVERTISEMENT
Jun 27, 2007
Hi all
What i want to do - Execute a Stored Procedure when a user log on that is in aActiveDirectory Group.
I want to create a storedprocedure that will be executed when a windows user log on that is part of a specific ADGroup.
I was able to create the ADGroup and add it to logins. I was able to create the procedure with the ADGroup as owner.
The problem is when the user log on, he is not seen as part of the group that has rights on the DB.
Please help
View 1 Replies
View Related
Feb 27, 2008
Has anyone in here had luck using AD as a datasource? Big picture, we have an input file with unique identifiers (employee clock numbers) and I need to associate a clock number with an email address. The only place we have those two together is in Active Directory.
Currently, we have a lookup task with an OLE DB connection to our AD and a query that looks like
Code Snippet
SELECT mail, clockNumber
FROM 'LDAP://DC=company,DC=com'
I've mapped my input to the clockNumber in AD and all is well and good. I then go to thinking, perhaps it'd be better if I used that query as a source in an OLE DB component and used a Merge Join to make the link between the two. I paste that same query into a source component and boom
Code Snippet
TITLE: Microsoft Visual Studio
------------------------------
Error at Create extracts [OLE DB Source [1226]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x00000000.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC0202009 (Microsoft.SqlServer.DTSPipelineWrap)
Is this just not something that a person can do or is there something I've set up incorrectly? I'd be lucky to be considered a novice with AD so I'm fine if someone has a better suggestion for retrieving this data, this was just something I'd cobbled together out of some forum postings.
View 4 Replies
View Related
May 23, 2006
Collin writes "i have tried to do a query between 2 different tables that have different columns. within both these tables there is a column that has a column name different from the column name of the other table but the data within both columns is the same. i wanted a query that compares the data between the 2 tables.
Example is:
Table 1
Columns : firstname , age , id , grade.
Table 2
Columns : name, sex , school.
lets say that the data of column "firstname" of table 1 contains the same data as column "name" of table 2 and i would want to do a query that retrieves records for where data in the column "firstname" in table 1 is found in column "name" of table 2.
sql server 2000 with service pack 3 running on windows 2003 server service pack 1
Rgds,
Mr. C. "
View 2 Replies
View Related
Jul 17, 2014
I'm trying to integrate the security settings for our system into the reports and search and its a nightmare trying to fit in all the rules.
Basically I have a massive custom search query which I now have filter even further. [URL] for previous discussions on this query, which I'm currently happy with).
so we have 6 different types of transactions. each type can have different admins. the search can be done for either 1 type or all types.Transactions by default are available to everyone, But there are a few (probably less than 1% of all) that security is enabled which is simply done by added people to the security table.
each transaction can be see by
* Everyone If no record found in security table for transaction.
* If even just one transaction found, the below have permission to view it.
- Person who raised transaction
- person currently responsible for it.
- person currently working on it.
- everyone in the security table for this transaction.
- Admins for this Transaction Type.
So when someone does a search I need to fit all the above. Because I'm building the search query it does make it a little easier to accommodate the various scenarios.And I think I have them all EXCEPT someone who is trying to search for records under ALL types, but is an admin of just 1 or 2 of the types. Ie they have too be able to see all records for those they are admins for, and then have normal security imposed for the other types of records.
The section near "DECLARE @IsUserAdmin AS BIT" sets admin flags for when they are an admin of a single type if searching for a single type, or sets admin flag if they are an admin for all types.
ALTER PROCEDURE [dbo].[uspJobSearch]
@csType as nvarchar(20) = '-1',
@status as nvarchar(20) = '-1',
@startID as nvarchar(50) = '',
@endID as nvarchar(50) = '',
@complaintType as nvarchar(50) ='',
[code]....
View 0 Replies
View Related
Aug 12, 2012
An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
View 2 Replies
View Related
Apr 21, 1999
Hi All
I have a table of students, and a table of lecturers, and I wish to use one select statement to return a list of users.
What I really need to do is something like this:
SELECT Students.UserID AS UserID, Lecturers.UserID AS UserID, Students.FullName AS FullName, Lecturers.FullName AS FullName FROM Students, Lecturers
which would (in my dream world!) produce something like:
UserID FullName
s0002131 Darren Student
s0054242 Richard Student
e13412 Michael Lecturer
x92342 Linda Lecturer
Does anyone know how I should do this? Should I be doing it in an stored proceedure and returning the record set from that? Currently I'm only using ASP and one line SQL statements, and would like to continue doing so.
View 1 Replies
View Related
Aug 22, 2007
Im try to use SQL server 2000 and use Enterprise to cript a table
But my result is only table structure in the script (myfile.sql)
(something like "create table Tb1, column..") and not have any data in my table included.
how good it is if it can write some insert statement (depend on number of records existed).
Could I have any way to do it with MS SQLServer 2000?
Thank you much.
DongMT
View 1 Replies
View Related
Oct 10, 2007
i'm not able to generate a sql script with the data in the tables! using sql express.
i need this script to ganerate the same database with its datas in a new sql server.
thanks in advance
View 2 Replies
View Related
Nov 11, 2015
I have two queries yielding the same result that I wanted to compare for performance. I did enter both queries in one Mangement Studio query window and execute them as one batch with the actual query plan included.Query 1 took 8.2 seconds to complete and the query plan said that the cost was 21% of the batchQuery 2 took 2.3 seconds to complete and the query plan said that the cost was 79% of the batch.The queries were run on my local development machine. I was the only user. No other programs were running at the time of this test. The results are repeatable.I understand that the query with the lowest cost is not necessarily the fastest query. On the other hand, the difference is quite big. The query that has approx. 80% of the cost takes 20% of the time and the other way around. I have two questions:
Is such a discrepancy normal?Can conclusions be drawn from the cost distribution? For instance, does the query that takes 8.2 seconds but only costs 21% scale better?
View 9 Replies
View Related
Sep 18, 2015
I am trying to import data from 4 columns in a spreadsheet, the Columns are (Last Name - First Name - ID - Code) and this spreadsheet has around 10k records. I want to add what is in this spreadsheet to the query I have below that uses the EXCEPT operator but I am not sure the best way to go about it.
Using the example I have filtered below for the name "Denise Test", at the end of the day I want everything that is in the spreadsheet to also be excluded from the results.
So before the spreadsheet lets say the 2nd query referencing table C has the following results for Denise Test
Last_Name First_Name ID Code
Test Denise 1 5
Test Denise 2 4
After adding the spreadsheet I want it to show this:
Last_Name First_Name ID Code
Test Denise 1 5
Test Denise 2 4
Test Denise 3 3
Here is the query as it stands now without the inclusion of the spreadsheet:
SELECT
ta.last_name,
ta.first_name,
tb.ID,
tb.code
FROM
TableA ta
INNER JOIN TableB tb
[code]....
View 0 Replies
View Related
Jan 30, 2008
Hi,I have a sql query like this select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08' group by [mycolumn]order by [mycolumn] descIf all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.(0+1+2)/3 = 1. But sql is returning a value as if my 0's were nulls and not factored in: (1+2)/2 = 1.5Does anyone know why this is happening and how to fix it?
View 5 Replies
View Related
Jan 30, 2008
Hi,
I have a sql query like this
select avg([mycolumn]) from data where date > '1/5/08' and date < '1/10/08'
group by [mycolumn]
order by [mycolumn] desc
If all values within that average are numbers, I'm fine. If it's a 0 (not a null, a 0) it doesn't get averaged in. For instance, values 0,1,2 should produce an average of 1.
(0+1+2)/3 = 1.
But sql is returning a value as if my 0's were nulls and not factored in:
(1+2)/2 = 1.5
Does anyone know why this is happening and how to fix it?
View 3 Replies
View Related
Dec 27, 2007
Hi,I'm trying to include a button control inside an iff where I am showing the results of a datalist. My aim is for each user listed, to show whether or not he is already a friend of the logged in user. To do this, I want to create an iff where if the user is already a friend, I will show the text "already a friend". If the user is not yet a friend, there will be a button saying "add as friend" which when clicked will execute an insert query to the database to add that user as a friend. I tried the code below, but it doesn't seem to recognize the button inside the iff. I really need a button to work if the result of the iff condition is DBNull.Value... Can anyone please help? <%#IIf(Container.DataItem("userNameIfFriend") Is DBNull.Value, "<asp:LinkButton id='addbuddy' runat='server' > Add as buddy</asp:LinkButton>", "Already buddy")%> I also tried the code below instead, but it doesn't work either, and I do not know what to include instead of true and false, since the button would only be enabled if the case was true. <asp:LinkButton id='addbuddy' runat='server' Enabled="<%#IIf(DataBinder.Eval(Container.DataItem, “userNameIfFriend�) is DBNull.Value, “True�, “False�)%>">Add as friend</asp:LinkButton> Thanks a lot.
View 6 Replies
View Related
Jun 15, 2007
Hi everybody
I have this sql which calculates the total
MY TABLE IS:
A B C
100 5 3
100 0 5
20 0 0
sql is
A - B + C as total
the result is it gives me only the total of the first row since it has values on the on the B AND C
the result is
A B C total
100 5 3 98
100 0 5
20 0 0
with no result for the last two rows.. I want to give a condition that if the value is zero or null go to the next...
thanks for any help
View 13 Replies
View Related
Jun 28, 2006
I have a three server peer-to-peer replication setup that includes articles for tables and views. As I understand the BOL, scheme changes -- which I take to mean changes, amont other things, changes in the design of a table or view -- should automatically replicate to the other servers in the topology. Here are my quesitons:
When including a view as part of the publication, what is it, exactly, that is getting replicated? If all the tables supporting a given view are being replicated, and the view exists on all three boxes, whatelse, besides the view schema (and changes thereto) is being replicated?
Secondly, if in fact schema changes are replicated, why can't I modify a view that is part of a publication? When I try to make a change to such a view, I get a server timeout message, every single time. When I remove the view from the publication, I can make the modification with no trouble. What does replicating schema changes mean if I can't make changes to the schema?
Thanks for any enlightenment on this.
Randy
View 5 Replies
View Related
Apr 20, 2008
Hi all,
I am processing multiple flatfiles in the same folder using a "for each loop". Inorder to keep track of the file where the record comes from, I would like to add the full path and filename to the record that gets written to SQL.
Can anyone help me with this.
Thanks,
Dan
View 3 Replies
View Related
Jan 31, 2008
I'm using reporting services actions to execute some javascript. My javascript is quite complicated. Thus I don't want to duplicated it on each item. I've created javascript function and wan't to execute it from actions. But I can't find the way how to include my javascript code into my reports. If there's way to include it for all reports generated on reporting services server - it works fine for me.
Thanks in advance!
View 3 Replies
View Related
Nov 21, 2006
Hi,
I'm trying to write a SQL SELECT statement where the phone numer ("telnr") is divided on three rows. How do I write a newline? I've tried , NEWLINE, and a few others.
Thanks in advance!
Pettrer, Sweden (VB, Sql Server, VWD Express, Asp.Net 2.0)
Code:
SelectCommand="SELECT [gID], [enamn], [fnamn], telnr1 + ' ' + telnr2 + ' ' + telnr3 As telnr, [epost] FROM...
The corresponding gridview's cell's value is
08-43 244 234 08-432
23 08-424333
and should be
08-43 244 234
08-432 23
08-424333
View 7 Replies
View Related
May 25, 2001
Hi All,
Here's my situation: I need to move several application db's to a new server. The current server is SQL 7.0 SP2, the new server will be SQL 7.0 SP3.
In order to avoid having the problems of mismatched logins and db user definitions, I'd like to restore the current master over the master that's created at install on the new server.
I was doing some testing of this by trying to restore the 7.0 SP2 master onto a SQL 7.0 DE edition and had some problems. (Obviously, this is not the exact scenario that I need to take place -- I want to restore standard edition to standard edition.)
Is restoring the master db from different service packs under 7.0 going to pose a problem? Is restoring the master db the best way to get around the logins issue when moving db's?
Thanks in advance for all replies.
View 1 Replies
View Related
Nov 30, 2000
I am trying to write a stored procedure that will automatically link a server and then run a stored proc on the newly linked server. At the end the stored proc will drop the server. I do not want a permanantly linked server due to the fact that this only has to be ran once a month, can you run a remote stored proc any other way then linking the servers?? Cheers to all who reply
View 1 Replies
View Related
Nov 14, 2007
I have one table where i want to use aggregate function for duplicate rows and also be able to select all fields to display. How would i do that?
Here is my query:
select Z, count(*)as num from Table
group by Z
having count(Z) > 1 ----- this returns 213 rows
Select Z, A,B,C,D From Table
Group By Z, A,B,C,D
Having Count(Z)>1 ----This gives me nothing
Hope I am explaining this correctly as to what I want.
Thanks,
Saru Brochu
View 3 Replies
View Related
Mar 29, 2008
Hello, I understand that we should use SSMS -> Server Properties -> Memory to put a cap on the SQL server memory usage, therefore it gives some space memory for OS, this is based on the fact if the max memory is not specified, SQL will use whatever available memory and eventually crash the system.
My question is that when a server has SSIS and SSAS services installed along with the SQL service. Would the max memory setting covers the SSIS and SSAS memory usage, or the SSIS and SSAS has to shared the memory with OS?
Thanks,
fshguo.
View 6 Replies
View Related
Jul 23, 2005
Hi,I have 2 tables, Mail_subject and Mail_Usage.Mail_Subject contains the subject, body and some other bits of info.CREATE TABLE [Waterford_MailSubject] ([ID] [int] NOT NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailCategory] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailBody] [ntext] COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[MailCreateDate] [smalldatetime] NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOMail_Usage records the amount of times a certain mail was sent.CREATE TABLE [Waterford_MailUsage] ([ID] [int] NOT NULL ,[RepScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MemberScreenName] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[MailSubject] [nvarchar] (50) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[TimeDate] [smalldatetime] NULL ,) ON [PRIMARY]GOThey are joined by Subject (not my idea, its a DB ive inherited).What i need is to get the Mail Subject and the number of times thatMail was sent. Ive Joined them using an INNER JOIN which gave me acount of the number of times each one occoured except for Mails thathave not been used. I need to get zero as the count of Mails notsent. Ive tried a LEFT OUTER JOIN but it didnt work either.Can someone point out what i need to do ?
View 1 Replies
View Related
Jul 20, 2005
I've seen plenty of posts regarding the estimation of table size,usually in the processing of planning for server storage needs.Well, I've got a different problem. I need to know how much data eachof our Customers are using in a Database. (1 SQL DB stores multiplecustomers).Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:655 MB.So, how can I ask SQL Server how much data each Row in each table istaking up? I want to be able to calculate nightly the total size, so Iwould take each row in each table that belong to the customer, and addall the sizes together. I want to take into account blobs that arestoring images and PDF files also.Thanks in advance,Jesse Wolgamott
View 1 Replies
View Related
May 14, 2007
I am trying to add an Imports statement to the beggining of the custome code section of my report but I recieve this error. There is an error on line 0 of custom code: [BC30465] 'Imports' statements must precede any declarations.
My syntax looks like this:
Imports SB = System.Text.StringBuilder
Imports System.Enum
public function Test ...
end function
Does anyone know if this is even possible and why or why not?
In relation to that, can anyone answear as to why there is a 32K limit on the code section in reporting services?
View 4 Replies
View Related
Oct 29, 2006
We are thinking of using a Sql Server Express database (.mdb.mdf) as a data resource in a Windows Forms application that we will be distributing to clients. When the application is built, the mdb file is copied to the bin directory.
In order for clients to access this database, will they need to have an instance of the SQLEXPRESS server installed on their computer? Or since the .mdb.mdf file is being accessed from a .Net 2.0 application, there is no need to install a separate db component?
If I do have to separately install the db component on the client end,
is this the way to go about it? This? Or is there a different/better way?
View 6 Replies
View Related
Jun 13, 2006
Why is it that when I include a column from my SQL Server database table, which has it's Allow Nulls checked, in the data source of a control that the record becomes not update-able? How do I get around this?
View 1 Replies
View Related
May 23, 2001
I've been fighting with this for a few days now, hopefully it's a simple answer.
I need to take a daterange between two dates and output the number of work days in that range.
I wrote this but it doesn't work. I know how to write a query that tells me if a date is a word day or not but not to actually determine a date range.
SELECT in_date,out_date,DATEDIFF((weekday, in_date, out_date) in (1,2,3,4,5)) AS no_of_days
from <table>
where ikey = 'whatever'
I am in desperate need of help as my deadline is coming up soon.
View 7 Replies
View Related
May 8, 2007
I have a report that is built using the report builder and the report model that was created.
With the fields that are displayed in the report, it could be possible for the database to have multiple rows with the same values for all the fields (that are displayed in the report). The PK will be different, but this is not displayed in the report as the PK won't make sense in the report.
So what happens is that the report displays just 1 record even though the database has multiple records because all the fields (that are displayed in the report) are identical. Is there a way to make the report display all the rows irrespective of duplicates?
View 1 Replies
View Related
Sep 26, 2006
I'm working on an MSI-based install that installs a suite of applications. One of the applications requires SQL Server 2005 Express to be installed or already on the machine, but if the user doesn't want that app installed, I don't want to have to bother with the SQL Server issue; however, if that app is to be installed and SQL Server is not already installed, I want to be able to have it installed automatically during our install in order to make it easier for our end users. The obvious answer to this is to launch the SQL Server Express install as a nested MSI install at the appropriate point in our install. Since the SQL Server install is so complex, I was wondering if this would work if I were to launch the SQL Server install's setup.exe; or do I have to launch one of its dozen MSI files? If it's the latter, which one should my install launch? (Or is the SQL Server Express install simply too complex to be included in my install in this way? My plan is to have the SQL Server install sitting somewhere on our install CD - we've already obtained a redistribution licence - and have our install access it from there.) And will SQL Server Express appear as a separate entry in the Add/Remove Programs applet if it's installed this way? (I'd like to be able to leave SQL Server in place when uninstalling our application suite - or even just the one app that needs it - and allow SQL Server to be uninstalled separately if the user wishes.)
View 6 Replies
View Related
May 24, 2015
I am new to sql server and i would like to set up a multimedia database (includes photos) using sql server (regardless of the version). How can i achieve that?
View 3 Replies
View Related
Mar 28, 2006
I'm trying to distribute an application but I face a little problem. I
want to install the .net framework and SQL Server Express while my app
installs itself in the host PC. Also I would like to let SQL server
express prepared for remote conections, all of this in a single install
project and totally transparent for the user.
I don't if it can be acomplished using scripts or some other thing, please help...
View 3 Replies
View Related
