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) ='',
select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup from updatedb left join opencall on updatedb.callref=opencall.callref
where udindex = '0' and suppgroup = 'SUPPORT' and (updatetxt like '%' + @Word + '%')
And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.
They would like it to be more fuzzy with "and" and "or". How can I adapt this?
Recently to help improve search times across one of our catalogs we implemented a full text index. So far this has worked wonders and improved our speeds 20 fold!
However recently we hit a snag with certian searches. Our catalog contains parts with descriptions such as 3'x2'. We have taken out single characters and numbers from our noise files, but they don't seem to contain special characters (ie . , " ' / ). So it seems that each one of these characters acts as a work breaker. Is there a way we can structure our search to include these characters, or is there another file to modify to allow these characters?
Currenty our call, simplified, looks like this:
select * from catalog where freetext(description,'3''x2''')
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. "
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)
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.
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.
I need to search all tables in my user database to find a value. I don't know what the column name is.
I know of one table that contains the value, but I need to look through all the tables and all there columns to see if I can find if this value exists in more than one place. It is an int value - 394789.
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
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?
Select * from TableName Where Contains(ColumnToBeSearched, 'testtosearch')
QUESTION: Can the 'testtosearch' be another table field like ''' + <AnotherTBLField> + '''Is there anyway to make the population of 'testtosearch' to be based on another table field (say when you join to it)?If not what else can I do, another function maybe?
Are the XML tags searched, as well as, the contents within the tags, when an xml column is included in a full-text index? Or is it only the contents within the tags? Would I need to combine full-text search with XPath/XQuery to get at the actual tags?
I'm trying to pull up a report of restored databases from our bug tracking software to audit and see if any of them can be taken down, and having some trouble figuring out how to pull the list of databases out of the entire request text, since they usually come in via email. Some sample data is below with the paltry beginning of a solution that I came up with.
WITH bug (BugID, BugComment) AS ( SELECT 1, 'Hello DB_001000, DB_001000, DB_001000 Blick' UNION ALL SELECT 2, 'Hi DB_001000 DB_001000 DB_001000 DB_001000 Flick' UNION ALL SELECT 3, 'Urgent DB_001000 DB_001000 Glick'
I've a table that stores operationcode for each jobnumber. The jobnumber can have multiple operationcode. From the below DDL, I need to show all the jobs that have operation codes as 2001 and 2002. In the below DDL Jobnumber 80011 has both the operation codes 2001 and 2002 so this job will display on the report.
On the other hand Job 80021 only has operationcode 2001 and I do not want this job to show up on the report.
I need to show all the operationcodes for a job if it has operationcode 2001 and 2002.
USE tempdb; GO DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED , OperationCodeVARCHAR(10) NOT NULL , EmployeeCode VARCHAR(10) NOT NULL
So to fetch the data having only special characters in it, I used below query
Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].
I have written a query to search for a string in an expression by the number of it's appearance. Script is like this:
DECLARE @Expression VARCHAR(8000) = 'abcd_e_fgh', @SearchString VARCHAR(10)= '_', @OccuranceNumber SMALLINT = 1 DECLARE @SearchIndex INT = 0, @SearchIndexPrevious INT = 0, @Sno INT = 0 WHILE @Sno < @OccuranceNumber BEGIN
[Code] .....
Here i'm trying to search "_" in expression "abcd_e_fgh" where it is appearing for first time. it gives me 5 correctly. Now when i change the @OccurenceNumber to 2 or 3, it gives correct values 7 and -1 respectively. However now when i change it to 4, it gives me 5. So when it's trying to check for fifth appearance of "_", it's not actually giving 0 or -1 but repeating the value 5.
How to implement a universal search in my site And it must be give result precised up to 90%.
Scenario is like that whenever customer come to our site he/she can search for example: Car parts
My Table structure is like below:
Column 1 Column 2 Column 3 Column 4 column 5 column 6 (Keyword) Toyota camry 2000 wheel ABC123 Toyota camry 2000 wheel abc123 Toyota camry 2000 wheel cover 123 Toyota camry 2000 wheel cover 123
Now i have created a full text search index on column 6(keyword)
so whenever someone search exactly describe in column 6 it gives result
For ex: if i search (Toyota Camry wheel) with CONTAINS Option
Select * from mytable where Contains (Keyword,'Toyota and Camry and wheel')
It gives me rows which contains Toyota Camry wheel and other rows which having wheel cover also. So i get multiple rows. Because of we have very large table which contains data in millions its not to feasible to have that thing.
another thing is :
if i search (Toyota Camry wheels) with CONTAINS Option Select * from mytable where Contains (Keyword,'Toyota and Camry and wheels')
It won't give any result back to me which is embarrassment for me. Because customer can search anything so if its not giving result its bad impression.
Also i tried to use FREETEXT option but it gives result in combination of all this word which i searched. so its huge amount of data in result.
I am having hard time figuring out what query I need to write.
What I want to do is, when users enter a word into a textbox, it displays results similar to it. Something similar to what Google does.
This is what I am doing now. For example I will use "John Smith"
1. I get the first 3 letters of the string "Joh" 2. Get the last letter of the string "h" 3. and this is what I search for "Joh%h%"
This works, but I still have some problems. I want to be able to search like Google, where it recognizes words. Does anybody know how I can do this, or send me a link with an example?
I would like to write query which can1. ignore whether the search keyword is Upper case or lower Case2. deal with tense or verb form related problem eg. comparing --> also search compareAny Idea
hi all I have the follwoing search page: several text boxes in case they want to search for SSN, first name, last name, zipcode, several dorpdown lists, and a set of radio buttons. I want to create the SQL statement to display the results I am checking if any of the text boxes has anything in it, if yes add "where something = value" to the query, if not then skip... now let's say it goes through textbox 1 and finds it empty, so it jumps to the second one it find info in it, how do I deal with the "and" clause within the query? any tips are fine.. (im using c#) thanks
I am writing a small search feature to return a list of companies whose name "Begins with" a certain string (up to 5 chars) provided by the user via a textbox. I want the results to only return results that begin with the letter/letters specified. Below I will put the code that I came up with that isn't working quite how I expected. I am new to this so any assistance and short explanation would be very much apperciated. sql="SELECT distinct cm.cmmst_id, cm.cm_compno, cm.cm_cname1 + ' ' + cm.cm_cname2 AS cm_cname1, cm_tele, cm_fax, cm_s16 " sql=sql & "FROM cmmst cm " sql=sql & "WHERE cm_cname1 + ' ' + cm_cname2 LIKE '%" companyNameBegins,"'","''") & "%' " sql=sql & "AND (cm_mbtyp='M' OR cm_mbtyp='SUBDIV') " sql=sql & "ORDER BY cm_s16 DESC, cm_cname1 ASC" companyNameBegins is the string passed in by the user
I am having a difficult time figuring out the differences between the following 2 queries. The first query ( which should return around 8000 records) when run against a Sql7 DB with 70000 records will always timeout (timeout set at 30 secs), but the 2nd query will return the correct amount of records ( about 8000 ) in under 8 secs.
Could someone shed some light on this for me?
Thank you Robert
******Start Query 1****** Select Lot, Title, UserId, HighBidder, HighBid, BidTimes, DateIn, EndDate, Price, Category, Options, Reserve, StartDate From Auctions A Where ( ((title LIKE '%STUB%' )) or ((description LIKE '%STUB%' )) ) And (Status In ('A', 'P') Or (Status In ('B', 'C') And EndDate >= '02/25/2001') ) And ((A.Reserve > A.Price And A.Reserve Between 0 And 999999.99) Or (A.Price Between 0 And 999999.99)) Order By Lot Asc
*****End Query 1*****
******Start Query 2****** Select Lot, Title, UserId, HighBidder, HighBid, BidTimes, DateIn, EndDate, Price, Category, Options, Reserve, StartDate From (Select Lot, Title, UserId, HighBidder, HighBid, BidTimes, DateIn, EndDate, Price, Category, Options, Reserve, StartDate,Description From Auctions A Where (A.Status In ('A', 'P')
Or (A.Status In ('B', 'C') And A.EndDate >= '02/25/2001')
) And ((A.Reserve > A.Price And A.Reserve Between 0 And 999999.99) Or (A.Price Between 0 And 999999.99)) ) as X Where (
I'm building a serch page on website. It basicly searches 2 tables. Right now, the way I have it so the user can enter a value in one of the fields, and not the others. And it will return the value. For example, put in '34' in the age field, it will return all people with 34. Now if you add 'Jones' to the last name field, it will return all people with the age of 34, and all people with Jones.
But what if I want it so it returns only the people with the age 34, and Jones. But leave the other fields blank. The later than add 'Jim' to the first name, so now it will return 34, jones, jim. I tried to use the AND operator, but then it requires that field to be filled. Here is my code.
ALTER PROCEDURE [dbo].[SrchActiveII]
@FName nvarchar(50)=NULL, @LName nvarchar(50)=NULL, @DOB nvarchar(50)=NULL, @Acct nvarchar(50)=NULL, @Login nvarchar(50)=NULL, @Status nvarchar(50)=NULL, @Rmark nvarchar(255)=NULL, @Room nvarchar(50)=NULL, @Age nvarchar(50)=NULL, @Type nvarchar(50)=NULL, @Misc bit = NULL AS BEGIN SELECT Active_Orders.First_Name, Active_Orders.Last_Name, Active_Orders.Account_Number, Order_Status.Status, Active_Orders.Remarks, Locations.Loct_Desc, Active_Orders.Rm_Desc, Active_Orders.Age, Active_Orders.Type, Active_Orders.Stat, Active_Orders.Order_ID, Active_Orders.Login, Active_Orders.Misc FROM Active_Orders INNER JOIN Order_Status ON Active_Orders.Status_ID = Order_Status.Status_ID INNER JOIN Locations ON Active_Orders.Location_ID = Locations.Location_ID WHERE (Active_Orders.First_Name =@FName) OR (First_Name IS NULL) OR (Active_Orders.Last_Name =@Lname) OR (Last_Name IS NULL) OR (Active_Orders.DOB = @DOB) OR (DOB IS NULL) OR (Active_Orders.Account_Number = @Acct) OR (Account_Number IS NULL) OR (Order_Status.Status = @Status) OR (Status IS NULL) OR (Active_Orders.Remarks = @Rmark) OR (Remarks IS NULL) OR (Active_Orders.Rm_Desc = @Room) OR (Rm_Desc IS NULL) OR (Active_Orders.Age = @Age) OR (Age IS NULL) OR (Active_Orders.Type = @Type) OR ([Type] IS NULL) OR (Active_Orders.Login = @Login) OR ([Login] IS NULL) OR (Active_Orders.Misc = @Misc) OR (Misc IS NULL) END
I am making a search query for jobs site. I have a situation that if user type following string in my search box:
Web Developer Designer ISF New York
It should display all jobs conatins any one of given words. following are columns in my jobs table: (Query will be applied on Location, Title, Description, Company fields)
I am developing a website for multiple clients, each with their own separate database on SQL Server 2005. The database structures are identical for all clients. I like to use SQL stored procedures for the security advantages (i.e., don't need to grant access to the tables, only exec permissions on the stored procedures), but maintaining and deploying many sp's across all databases is becoming unwieldy and error-prone.
Is there a way to use parameterized queries (SqlCommand, SqlParameter) in C# code (which could be reused for all databases by changing the connection string) without having to grant access to the tables?
[OpporID] [numeric](18, 0) IDENTITY (1000, 1) NOT NULL , [OpportunityID] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [OpportunityTypeID] [numeric](10, 0) NOT NULL , [SLABased] [int] NOT NULL , [LoginID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateCreated] [datetime] NOT NULL , [AccountID] [int] NOT NULL , [GeographyID] [int] NOT NULL , [VerticalID] [int] NOT NULL , [BDMID] [int] NOT NULL , [Probability] [int] NOT NULL , [PASStatus] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL
and SKILL [SkillNo] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [OpportunityID] [numeric](18, 0) NOT NULL , [OrderId] [numeric](18, 0) NOT NULL , [PracticeID] [int] NULL , [SkillID] [int] NOT NULL , [NoOfPeople] [int] NOT NULL , [Clientinterview] [int] NOT NULL , [Location] [int] NOT NULL , [JDAttached] [int] NOT NULL , [JDFilePath] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Status] [int] NULL , [Experience] [int] NULL , [InsertedDate] [datetime] NULL , [InsertedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [UpdatedDate] [datetime] NULL , [UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [UpdatedFlag] [int] NULL , [GeoLocation] [int] NULL )
i want to make a stored procedure for custom search on these two tables with the following fields given to the user as an option to make his choice.. from opportunity table - OpportunityTypeID,SLABased,AccountID , GeographyID, VerticalID, BDMID, Probability
and from skill table SkillID, Location, GeoLocation
Hi!In short description I want to make table with Articles and search Query for this table like there is search engine at templatemonster.com with templates and categories. Basicly I have table:- id - cat_name- 1 - Software- 2 - Hardware- 3 - Games- 4 - Internet- 5 - Events etc.And for example Article is in Software, Games and Events category, "1,3,5".Now user select to show articles in Games and Events categories, so "3,4,5"How write this search query? I don't have idea.
Hi, I'm wondering about the following: I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I havereviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're foundinside some DLL. I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere. So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)? Thanks in advance for your advise. Stewart
