SQL 2000 MS Search: Boolean Search Doesn't Work When Search By Phrase
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
* Doesn't Seem To Work With Full Text Search
Hi, If I use the following syntax it works OK Where Contains(item_description '"stuff*"') but if I put the '*' at the beginning it doesn't work. Is this syntax only OK as a post fix? i.e. this doesn't work Where Contains(item_description '"*stuff"') Appreciate any help.
View Replies !
Search For A Phrase In Sprocs
Due to a business rule change, I had to take what was 1 column in a table and split it off into a new table. Now I need to find every time that column is used in a SPROC and change those sprocs. Is there a way to sift through the sprocs to search for a "phrase" (the column name) -- other than reading through every one manually? Thanks Mark
View Replies !
Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data
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
View Replies !
Save Indexed Search Words (Index Full-text Search)
We have SQL Sever 2005 and created a Full-text Search on a one of our main tables. When some one comes for the first time and searches for "carb" it takes a long time to return results, but when the same search is done within a few more minutes, results return in less than 1 second. Is there a setting in SQL Server 2005 to save all the indexes of searched words? Thanks
View Replies !
Full-Text Search: Prefix / Suffix Search
Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms. Example: Say I want to find "Terminator" (1984). I want to be able to use "Term" or "ator" as search results and still return the proper record. Here is my Stored Procedure creation sql: CREATE PROCEDURE sps_searchTitles(@searchTerm varchar(255)) AS SELECT * FROM Video WHERE FREETEXT (Video.*, '"*@searchTerm*"') GO --- The above does not appear to properly check both prefix ("Term---") and suffix ("---ator") terms. I am trying to accomplish what is similarly done with LIKE '%term%'. thanks, YM
View Replies !
Full Text Search- Substring Search Not Working
I have Sql server 2005 SP2. I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row. I have a table testtable where description has word Extinguisher. If i run a query with *ting* it doesn't return any row. select * from testtable where contains(description,'"*xting*"') ; But it works if i do select * from testtable where contains(description,'"Exting*"') ; The Full text search document says it supports substring search. Is it an issue with sql server 2005?Please help.
View Replies !
How To Search A Database For A Key Word Based Search?
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View Replies !
Full Text Search Vs LIKE Search
Are there any big differences between the two search techniques? It seems like they are both very similar. SELECT * FROM TABLE1 WHERE TEXTFIELD1 LIKE '%DATABASES%' SELECT * FROM TABLE1 WHERE CONTAINS (TEXTFIELD1 ,' "DATABASES" ')
View Replies !
Create Site Search Using Sql Server &"full Text Search&"
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on... so i thought create site search table, with the columns: id, text, url and to write every thing to this table. but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server... what do you think?
View Replies !
Returning Closest Results When Search Term Doesn't Exist
I'd like to get some ideas for the following: I am writing a quick mini-application that searches for records in a database, which is easy enough. However, if the search term comes up empty, I need to return 10 records before the positon the search term would be in if it existed, and 10 records after. (Obviously the results are ordered on the search term column) So for example, if I am searching on "Microsoft", and it doesn't exist in my table, I need to return the 10 records that come before Microsoft alphabetically, and then the 10 that come after it. I have a SP that does this, but it is pretty messy and I'd like to see if anyone else had some ideas that might be better. Thanks!
View Replies !
Full Text Search Doesn't Find A Specific String
Hi all, We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"') even though there exist records that satisfy the condition in the table, the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1" If I remove the last 1 from the search string i get lot of records, Can anybody help me out.
View Replies !
Report Viewer Control Find Next Doesn't Fire Search Event
Not sure if this is the correct group to talk about the Report Viewer.Webforms control but couldn't find much reference to this in the ASP.Net Group so thought I'd try my luck. Background: ASP.NET project that used the Report Viewer control to view remote report processed on the server. Issue: The find button on the report viewer control fires the ReportViewer_Search Event but the find next does not. This is contrary to MSDN Library that says the event should get fired from both Find and Find Next. See the link below http://msdn2.microsoft.com/en-us/library/microsoft.reporting.webforms.reportviewer.search(VS.80).aspx The Event args even have the boolean method indicating if find next has been clicked (e.IsFindNext). Have Microsoft forgotton to associate the button to the event?
View Replies !
Full Text Search - Conatins Doesnot Work
Our SQL Server 2000 (sp1) on a Windows 2000 system is configured for a full text search. The ntext column having a full text index has a value as follows: Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home when I search it by SELECT NTEXT_COL FROM FULL_TEXT_TABLE WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Away"') it displays the result as NTEXT_COL ====================================== Rob Proctor's Tips: Creating a "Tropical" Get-Away At Home But the query SELECT NTEXT_COL FROM FULL_TEXT_TABLE WHERE CONTAINS(NTEXT_COL, N'"Tropical Get Aw*"') do not return any rows.. Can anybody give me the reason for this behaviour, or is it a known bug? Thanks Sajan
View Replies !
Search Within Search Results?
Hi guys I need to create an "advanced search" which will allow the user to narrow down his results. Ideally I'd want him/her to use the same search criteria form for each iteration, with a checkbox called "Search within results" type of thing. Now what I was wondering if there was any existing literature on how to effectively do this. I have tried doing it just through SQL Statements but they are becoming very messy and large. Is it possible to do this by searching the initial dataset, returning dataset #2 and then if a 3rd "search within results" is done apply the search against dataset #2 and return dataset #3 etc? Many Thanks John
View Replies !
Tag Search And Tag Cloud Search
--set ANSI_NULLS ON --set QUOTED_IDENTIFIER ON --go -- -- --ALTER PROC [dbo].[spEventTagCloud] --as --BEGIN DECLARE @RECORDCOUNT INT; DECLARE @SearchString varchar(2000); DECLARE @QRY VARCHAR(2000); DECLARE @SE VARCHAR(2000); SELECT @RECORDCOUNT=COUNT(*) FROM TBEVENTS DECLARE @ST INT; SET @ST=1; CREATE TABLE #TEMP2 ( MYTAGS VARCHAR(2000) ) --CREATE TABLE #TEMP3 --( -- TAGCOUNT INT --) CREATE TABLE #TEMP1 ( STR1 VARCHAR(2000) ) WHILE @ST<@RECORDCOUNT BEGIN SET @QRY='SELECT TOP ' +CONVERT(VARCHAR,@ST)+' EVENTTAG FROM TBEVENTS' --PRINT @QRY INSERT INTO #TEMP1 EXEC (@QRY) SELECT @SEARCHSTRING=STR1 FROM #TEMP1 SET @ST=@ST+1 declare @i1 int; declare @i2 int; declare @MatchType int ; set @MatchType=0; declare @Word varchar(100); declare @Words table (Word varchar(100) not null); declare @WordCount as integer; DECLARE @TEMPWORD VARCHAR(2000); begin set nocount on if (@MatchType != 2) begin set @SearchString = ' ' + @SearchString + ','; --print 'Search String is :::: '+ @SearchString set @i1 = 1; while (@i1 != 0) begin set @i2=charindex(',', @SearchString, @i1+1) --print @i1 if (@i2 != 0) begin set @Word = rtrim(ltrim(substring(@SearchString, @i1+1, @i2-@i1))) SET @TEMPWORD=@WORD; SET @TEMPWORD=REPLACE(@TEMPWORD,',','') INSERT INTO #TEMP2 SELECT @TEMPWORD --print 'Search WORD is :::: '+ @WORD if @Word != '' insert into @Words select replace(@Word,',','') end set @i1 = @i2 end end else insert into @Words select ltrim(rtrim(@SearchString)) set @WordCount = (select count(*) from @Words) Declare @wordtemp varchar(2000); set @wordtemp=@word set @wordtemp=replace(@word,',','') --INSERT INTO #TEMP2 SELECT @WORDtemp END END SELECT mytags'Tag' , count(mytags)'Count' FROM #TEMP2 group by mytags ORDER BY [COUNT] DESC --SELECT * FROM #TEMP1 DROP TABLE #TEMP1 DROP TABLE #TEMP2 --DROP TABLE #TEMP3 --END ________________________________ THE ABOVE EXAMPLE FOR A TAG CLOUD MY TAGS ARE AS FOLLOW EVENTTAG _________ ASP.NET, C#, VB.NET WELCOME TO ASP.NET ASP.NET BOOKS,C#.NET BOOKS I WOULD LIKE TO SELECT ALL COLUMNS FROM MY TABLE SEARCH TAG IS ASP.NET THE FOLLOWING CODE WILL GENERATE AND SPLIT IT AS TAG ____ ASP.NET c# VB.NET WELCOME TO ASP.NET ASP.NET BOOKS C#.NET BOOKS HOW TO SEARCH AN EXACT MATCH AS ASP.NET THE ONLY ONE ROW
View Replies !
Full-text Search Wont Work For Text Column But Will Work For Varchar!
Hi, I am having the following problem: I am trying to enable full text search for a column of data type text. when i create the index by running the query: CREATE FULLTEXT INDEX ON Test(Content) KEY INDEX MyUniqueIndex3 ON MyFullTextCatalog3 WITH CHANGE_TRACKING AUTO I get the folowing warning: Warning: Table or indexed view 'Test' has full-text indexed columns that are of type image, text, or ntext. Full-text change tracking cannot track WRITETEXT or UPDATETEXT operations performed on these columns. when i try to run: SELECT Test.Content FROM Test WHERE CONTAINS (Test.Content, '"blah blha"'); i get no results (where i should normaly)... When i do the same thing but with the column to be varchar(1000) istead of text everything goes well... what can i do? thanx in advance!
View Replies !
SQL 2000 And Free Text Search
I have created a stored procedure which should accepts a search friendly string to be used in a free text search. I am using DSPerson AS FT_TBL INNER JOIN CONTAINSTABLE (DSPerson, [FiftyWords], @FiftyWords ,50 ) AS KEY_TBL ON FT_TBL.PersonID = KEY_TBL.[KEY] WHERE FT_TBL.PersonID != @PersonID ORDER BY KEY_TBL.RANK DESC Now the problem here is that the parameter @FiftyWords should be able to accept a string such as '"Love" OR "Hate" OR "Customer Service"' Notice the start and end of the query with single quotes and concepts should be between double quotes. Now this works OK inside SQL Query Analyzer, but try to get this right building such a string within ASP.NET and passing it as a parameter to SQL. In SQL profiler it comes out all weird with more quotes than originally built on asp side (errror obviously). Furthermore, I can't find an escape sequence that comes out right on the SQL side. Any ideas of how to fix this??
View Replies !
Sql Server 2000 Identity Search
i am using this search SELECT IDENT_CURRENT('t_Que') AS Expr1 and it will not return anything my primary key is a varchar with the length 12 will this command only work for an int as a primary key and if so is there another way i could do this my plan is to retrieve the last entered primary key so that i can generate a new one to add a new record to the database. the primary key is created by YYMMDDNN where YY=year MM=Month DD=Day NN=the number created that day now just to be specific i dont need help creating the primary key i just need to know how to retrieve the previously created primary key. I am trying this command directly in an sql query there is no code yet i am trying to see how the command functions first
View Replies !
SQL 2000 Full Text Search
This may be more of a site architecture question, but I wonder what mechanism large web sites with hundreds of thousands of products are using to build search functionality for their customers. I understand that the SQL 7 full text indexing was pretty poor, but with the full text search improvements in SQL 2k, it is now usable? Does it stand up under heavy simultaneous load? Does anyone out there use it? I know also that Index server can be used instead, but if anyone has any opinions on this, it would be greatly appreciated. thanks!
View Replies !
No Results From Full-Text Search In SQL 2000
I built a catalog for a table. Ran the Full-Population, and having[color=blue]>5.000.000 items count, > 5.000 unique key count.[/color]However when I ran "SELECT * FROM Categories WHERE CONTAINS(*,'Met*')"I am getting zero results. 'Met*' appears in more than 1000 times intable Categories, but I also used many other keywords without anyresults also.No errors in Log, for buliding the Catalog, everything ran fine.Any Ideas???
View Replies !
Full Text Search Possible For 2000 && 2005 On Same Pc
Hello: I am on a development machine with sql 2000 & sql 2005 installed. The full Text Search for sql 2005 is installed and running on this local machine. However, I need to do a full text search with sql 2000. When I do: sp_fulltext_database 'enable' I get: Full-Text Search is not installed, or a full-text component cannot be loaded. Can I install full text search for both sql server 2000 & 2005. I read somewhere that this is not possible. Since fulltext search loads with sql 2005 (I believe), does this mean I can't do an sql server 2000 full text search on a local machine (or server) that has both of sql2000 & sql 2005 installed. Thanks for any help on this
View Replies !
Full Text Search In SQL Server 2000
I have one question about full text search in SQl Server 2000/2005. I have one table which has 7 text columns. I want to retrieve all rows that have the search word in either of those 7 columns. I want to know if there is a way to add weight to those columns so SQL Server can better rank the result set. For example, column 1 has weight 1 and column 2 has weight 0.1. So records that have search word in column 1 will rank higher that those having search word only in column 2. Thanks a lot, Baoxin
View Replies !
Full Text Search On Clustered SQL Server 2000
I am using SQL Server Enterprise 2000 and have created a full text search on the pubs database as per the instructions below:- http://www.databasejournal.com/features/mssql/article.php/3441981 However, when I verify the search :- SELECT title, notes FROM titles WHERE CONTAINS (notes, ' "quick easy" ') no rows are returned. In the full text tab under the pubs database, my demo catalog is listed, but the Item count and unique key count are both 1. There are 18 records in my titles table. The full text files location is on my H drive which is set up as a dependency of SQL Server in cluster admin. In SQL EM->Support Services->Full Text Search->properties, the location of my temporary files was set to C:winnt empgthrsvc. As nothing in cluster admin can see this directory, I changed it to H: emp but still no data is returned by the query. There is no default error log file listed and I can't work out how to change this. I have checked the Search service is running under the local system account. I can't find any error messages which may indicate a problem apart from the fact that the T-SQL query doesn't return any data. Anyone any ideas? Any help is much appreciated. Thanks in anticipation. Claire
View Replies !
Microsoft Search Service Kills SQL 2000 Server
Hi all, This may be more of a Sharepoint or server question but I thought I would post it here to start off with. I have an installation of SQL 2000 SP4 and sharepoint services 2003 all on one server. For example the SQL 2000 (SP4), sharepoint and its indexes all on the one server (HP G4 DL380). I have installed the index service and the full text services on the SQL server then configured searching in sharepoint. The search worked fine for about a year until one day the server started randomly hanging for no apparent reason. After a hard reboot the server would begin to hand again after around 2-3 minutes of being online, sometimes less. After lots of problem shooting I nailed it down to being the Microsoft search service that caused the issue. Basically if I set this service to manual and boot I have no problem. As soon as I start the service the server will start to hang about 2-3 minutes later. I tried this again last night after trying some fixed and got the same result. The only error I get are in the application log which are about 4 of these with different file types. One or more documents stored in image columns with extension €œX€? did not get full-text indexed because loading the filter failed with error '0x1' The server continues to log messages while it is hanging and you can ping the server but you can not access sharepoint, remote in to the server, you can login locally but as soon as you try anything like bring up the start menu all resources will freeze up. During this time there is no disk activity and the CPU usage is 1-3% and mem usage is low. Can anyone suggest anything to fix this problem so I can start the Microsoft search service and use search in sharepoint again. Thanks, LS
View Replies !
MS Search Service Fails After Windows 2000 Domain Change
After changeing the NT 2000 Server Active Directory domain name everything seems okay except the Microsoft Search service which fails with "Error 1332 No mapping between account names and security ID's was done." Tried starting as Local System Account, New user account with Service and Admin priv's... all fail. Any suggestions would be greatly appreciated.
View Replies !
SQL Server 2000 Full Text Search (extract Pieces Of Text)
Hello everyone ! I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image). I would like to know, how you can extract pieces of text from the documents. Example: I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed. I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page. Does anyone has any idea how to work around it ? Best regards. Yannick
View Replies !
Search
Does anybody know how i can do a search on my website.I think the problem is that is not understanding the varible.If anyone knows how fix this or do it in a better way, tht would be great<form action="search.aspx" method="post"><input name="txtSearch" type="text" /><input name="btnSearch" type="submit" value="Search" /></form>Dim lookforSub Search_patients lookFor = Request.Form("txtUserName") sel = "SELECT * FROM People WHERE Surname LIKE ' lookfor%'" Dim rs As Object rs = Server.CreateObject("ADODB.recordset") rs.Open(sel, cs) Do Until rs.EOF() For Each x In rs.Fields Response.Write(x.value & "<br />") Next rs.MoveNext() Loop rs.close() rs = Nothing End Sub
View Replies !
Help With SQL Bit Search
This is probably a very simple question, but I could not figure it out... In my SQL 2005 Database I have a bit field CompanyIsActive. The field is not nullable. I would like to offer a search feature in the app that shows Active Companies, Inactive companies or All Companies. How do I do that? Here are some examples that did not work: 1- WHERE CompanyIsActive LIKE @CompanyIsActive (Declaring @CompanyActive as bit) : Only the True (1) are returned 2- WHERE CompanyIsActive LIKE @CompanyIsActive (Declaring @Company is the Stored Procedure as varchar) 3- WHERE CAST (CompanyIsActive AS varchar) LIKE @CompanyIsActive 4- WHERE CAST(CompanyIsActive AS int) LIKE @CompanyIsActive 5-WHERE CompanyIsActive <> @CompanyIsActive (declaring @Company as bit, varchar and nvarchar in the stored procedure) Your help is appreciated!
View Replies !
Best Way To Do An Sql Search Please
All I have a search screen with 4 possible input boxes name contents start date end date My user needs to be able to use any combination of these to do an sql search and may only use one criteria or all four what is best practice for creating this kind of sql search string in my aspx page thanks gibbo
View Replies !
Search
Hello, I have a search form where i want users to enter a surname into a textbox i then want to retrieve everything from my table patients thats matches this is there away of selecting from a database where Patient surname = textbox1. Thanks Mike
View Replies !
How To Search....
yes iam storing total data into the database, but actually i don't know how to read data from any type of file also......plz help me
View Replies !
Search
here is my current search i have created which returns values that i want....but my question is there a way to make it search for all related matches??? like lets say i was searching an address and i had "1231 W. Adams" in the table...and i searched by anyone that lived on "Adams" it would pull it up...right now i have to have the exact address in there correctly...any ideas? //create the connection objectSqlConnection myConnection = new SqlConnection(); //set the connection string myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["MRDDstringConnection"].ConnectionString; //open the connection myConnection.Open(); //create a commandSqlCommand myCommand = new SqlCommand(); //VarianceOfUse, Owner, BZA, AND VarianceOfUse.BZAcaseNum = BZA.BZAcaseNum AND Owner.OwnerID = BZA.OwnerID AND Applicant.ApplicantID = BZA.ApplicantID //***set the query text to the name of a stored proceduremyCommand.CommandText = "SELECT * FROM Applicant WHERE Applicant.Line1 = '" + searchTextBox.Text + "'"; //***set the command type, stored proceduremyCommand.CommandType = CommandType.Text; //associate a connection with a command myCommand.Connection = myConnection;SqlDataAdapter myAdapter = new SqlDataAdapter(); //inform the data adapter of the command to execute myAdapter.SelectCommand = myCommand; //4. holds the resultsDataSet myResults = new DataSet(); //execute the query and get results in a table myAdapter.Fill(myResults); //bind to LabelsSearchResults.Visible = true; SearchResults.DataSource = myResults; SearchResults.DataBind(); //6. give back all resources myAdapter.Dispose(); myCommand.Dispose(); myConnection.Dispose();
View Replies !
Help Regarding This Search
//and i m getting this error Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int. ==================================== can anyone help me in this i m doing a search in a form ( for ex. u search for clientID n it'll come up in search result n once u select that it'll fill in the form) if anyone knw how to do this plz help me. thank u Protected Sub cmdSelect2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdSelect2.Click Dim sID As String sID = lstResults.SelectedValue Dim iID As Integer ' iID = Int32.Parse(sID) '' If iID > 0 Then Dim selectSQL As String selectSQL = "SELECT * FROM contactinfo " selectSQL &= "WHERE ClientID=@ClientID" Dim cmd As New SqlCommand(selectSQL, conSR) cmd.Parameters.AddWithValue("@ClientID", iID) Dim reader As SqlDataReader Try conSR.Open() reader = cmd.ExecuteReader() reader.Read() ' Fill the controls. 'txtCaseid.Text = reader("Caseid").ToString() txtClientID.Text = reader("ClientID").ToString() txtFirstname.Text = reader("Client_Fname").ToString() txtLastname.Text = reader("Client_Lname").ToString() txtRace.Text = reader("Race").ToString() txtCounty.Text = reader("County_of_origin").ToString() txtGender.Text = reader("Gender").ToString() txtState.Text = reader("State").ToString() txtReligion.Text = reader("Religion").ToString() txtContactID.Text = reader("ContactID").ToString() 'reader.Close() '' enable_fields() lblResults.Text = "" Catch ex As Exception 'lblResults.Text = "Error inserting record" lblResults.Text = "Error inserting record" lblResults.Text = ex.Message Finally conSR.Close() End Try lblID.Text = sID phID.Visible = True txtID.Text = sID txtAct.Text = "edit" cmdSubmit.Text = "Update" lblCurrentAct.Text = "Update Existing Record" 'Else 'If iID = 0 Then ' lblResults.Text = "Client not found in Contact Information." 'Else ' lblResults.Text = "Please select a contact from the list." ''' End If End Sub
View Replies !
SQL Search
Sorry, guys, This is a pure SQL question. But likely most of you guys have encountered before. I wrote a stored procedure to search classes that matching with the following combined conditions: location, coach, and program name. User may use one or full search conditions. Therefore some input variables for the stored procedure may be empty. Is there some SQL syntax, operators etc. I can use to modify the following SQL script, so that those empty valued conditions can be ingored when executing SQL script. Select * From Classes Where Location=@Location and CoachID=@CoachID and ProgramName=@PogramName Many thanks,
View Replies !
Help With DB Search
Hi everybody: I have a page that has textboxes in order to take data and search a database. On the initial load the page loads fine with all the records from the data base. However when i try to search out a particular record i recieve an error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.I don't get this error until I try to input some text in order for it to search for something specifichere is the stack trace: Stack Trace: [SqlException (0x80131904): The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244 System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +52 System.Data.SqlClient.SqlDataReader.get_MetaData() +130 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +371 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +162 System.Data.SqlClient.SqlCommand.ExecuteReader() +114 PrescriptionProfiler.profileDB.GetProfiles(String rxnumber) in C:Documents and Settings woodard.MSBML_REGGIEMy DocumentsVisual Studio 2005WebSitesWebSite1App_CodeprofileDB.vb:51 [TargetInvocationException: Exception has been thrown by the target of an invocation.] System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +0 System.RuntimeMethodHandle.InvokeMethodFast(Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner) +72 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +358 System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +29 System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) +17 System.Web.UI.WebControls.ObjectDataSourceView.InvokeMethod(ObjectDataSourceMethod method, Boolean disposeInstance, Object& instance) +676 System.Web.UI.WebControls.ObjectDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +2664 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +84 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +154 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +99 System.Web.UI.WebControls.GridView.DataBind() +23 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +92 System.Web.UI.WebControls.BaseDataBoundControl.OnPreRender(EventArgs e) +33 System.Web.UI.WebControls.GridView.OnPreRender(EventArgs e) +74 System.Web.UI.Control.PreRenderRecursiveInternal() +148 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Control.PreRenderRecursiveInternal() +233 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4437 here is the code I am using:data in db:"0000000","Reginald","Woodard","Mike Jhana","Tylenol","2001-08-02""1111111","Ronald","Redd","Miles Janson","Alka Seltzer","2001-09-02""2222222","Jameson","Vickers","Mary Sharpe","Aleve","2004-12-31""3333333","Phillip","Brood","Orville Wittaker","Glucophage","2003-08-21"profileDB.vbImports Microsoft.VisualBasicImports System.Data.SqlClientImports System.Collections.GenericImports System.Collections.ObjectModelImports System.DataNamespace PrescriptionProfiler Public Class profileDB Public Sub New() End Sub Public Function GetProfiles(ByVal rxnumber As String) As ICollection(Of prescriptionrecs) ', ByVal startdate As String, ByVal enddate As String) Dim recs As New List(Of prescriptionrecs) Dim myconn As String = ConfigurationManager.ConnectionStrings("MyConn2").ConnectionString Dim dbconn As SqlConnection = New SqlConnection(myconn) Dim mycmd As SqlCommand Dim myreader As SqlDataReader Dim qry As String If Not (rxnumber = String.Empty) Then qry = "SELECT [profiler].[Rx_Num], [profiler].[pat_Fname], [profiler].[pat_Lname], [profiler].[date_Written],[profiler].[doc_name], [profiler].[drug]" & _ "FROM profiler WHERE Rx_Num = @rxnumber" 'AND date_Written between @startdate and @enddate;" Else qry = "SELECT [profiler].[Rx_Num], [profiler].[pat_Fname], [profiler].[pat_Lname], [profiler].[date_Written],[profiler].[doc_name], [profiler].[drug]" & _ "FROM profiler;" End If mycmd = New SqlCommand(qry, dbconn) If Not (rxnumber = String.Empty) Then mycmd.Parameters.Add("@rxnumber", SqlDbType.Text, 50).Value = rxnumber 'mycmd.Parameters.Add("@startdate", SqlDbType.Text, 10).Value = startdate 'mycmd.Parameters.Add("@enddate", SqlDbType.Text, 10).Value = enddate End If 'MsgBox(rxnumber) 'MsgBox(startdate) 'MsgBox(enddate) 'MsgBox(Convert.ToString(mycmd.CommandText)) dbconn.Open() 'Try myreader = mycmd.ExecuteReader() While myreader.Read() recs.Add(New prescriptionrecs(myreader.GetString(0), myreader.GetString(1), myreader.GetString(2), myreader.GetString(3), myreader.GetString(4), myreader.GetString(5))) End While 'Catch ex As Exception 'MsgBox(ex.ToString) 'End Try dbconn.Close() dbconn.Dispose() Return recs End Function End ClassEnd Namespaceprofile.vbImports Microsoft.VisualBasicImports SystemNamespace PrescriptionProfiler Public Class prescriptionrecs Private _Rxnumber As String Private _FirstName As String Private _LastName As String Private _DocName As String Private _date_Written As String Private _Drugs As String Public Sub New() End Sub Public Sub New(ByVal rxnumber As String, ByVal firstname As String, ByVal lastname As String, ByVal date_written As String, ByVal docname As String, ByVal drugs As String) Me.Rxnumber = rxnumber Me.FirstName = firstname Me.LastName = lastname Me.DocName = docname Me.date_Written = date_written Me.Drugs = drugs End Sub Public Property Rxnumber() As String Get Return _Rxnumber End Get Set(ByVal value As String) _Rxnumber = value End Set End Property Public Property FirstName() As String Get Return _FirstName End Get Set(ByVal value As String) _FirstName = value End Set End Property Public Property LastName() As String Get Return _LastName End Get Set(ByVal value As String) _LastName = value End Set End Property Public Property DocName() As String Get Return _DocName End Get Set(ByVal value As String) _DocName = value End Set End Property Public Property date_Written() As String Get Return _date_Written End Get Set(ByVal value As String) _date_Written = value End Set End Property Public Property Drugs() As String Get Return _Drugs End Get Set(ByVal value As String) _Drugs = value End Set End Property End ClassEnd Namespacesearch.aspx.vbImports SystemImports System.DataImports System.ConfigurationImports System.WebImports System.Web.SecurityImports System.Web.UIImports System.Web.UI.WebControlsImports System.Web.UI.WebControls.WebPartsImports System.Web.UI.HtmlControlsImports System.Collections.GenericImports PrescriptionProfilerPartial Class SearchProfiler Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load End Sub Protected Sub AddEntry_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles FindEntry.Click Dim obj As New profileDB If Not (Page.IsPostBack) Then obj.GetProfiles(Rx.Text) ', BeginDate.Text, EndDate.Text) MsgBox(Convert.ToString(e)) ProfileGridView.DataBind() End If End SubEnd Class___________________________________________________________________________________________________________I would really appreciate any help with this. thanks
View Replies !
Using CONTAINS To Search
Can you use a variable with the CONTAINS statement, ie CONTAINS(Search, @SearchCriteria) I can get it to work in when using a word ie CONTAINS(Search, '"computer"') but i want to use this for a search in my asp.net 2.0 application. I have been using Like ie (Search LIKE N'%' + @SearchCriteria + N'%') but this is much slower to return the query results. Thanks
View Replies !
Search
I have asp.net C# web application I want to search jobvacancy details using jobrole. if user did not select any value I want to select all the details including null values. this Jobvacancy table has JobRole feild and it allow to insert null values, this is my stored procedure CREATE PROCEDURE JobVacancy(@JobRole varchar (50))as Select NoOfVacancies,JobRole from JobVacancy where JobRole LIKE @JobRole + '%' when I use @JobRole value as '%' then if JobRole feild has null value it did not select.but I want to select all the values including null values. how can I do this?
View Replies !
Search SQL
Hello, I want to make search function in my webby to find the number of items in the datagrid This is the function in a Class.<code>Public Function RetrieveSearch(ByVal a As String) As DataSetDim myDb As New DatabaseDim cmd As SqlCommandmyDb.OpenConn()Return myDb.GetDataset("SELECT * FROM Table WHERE Title = '%' & a & '%'")myDb.CloseConn()End Function</code>This is the code to store in the datagrid<code>DataGrid1.DataSource = str.RetrieveSearch(TextBox1.Text)DataGrid1.DataBind()</code>I think the sql statement went wrong...Anyone can help me???
View Replies !
Search DB Using SQL
I need some tutorial(html link) on how to search data on a db. like taking the string from txt box then transfer it into the sql string. i noticed some of the ppl using "& txt &" and some '%" & Replace(txt, "'", "''") & "%' which 1 to use...vr confusing...i did search on google but what i get is only simple sql tutorial...they didnt give anything like taking a string from keyboard to search..and some source code of searching db using asp.net didnt explained it...:(thats all..thank u!
View Replies !
Search
Hi I want to implement a search machine on my site. Meaning, you can type some key words in a text field. Every key word has a prefix (+,-, ) that indicates if the word must be present, is optional or is not allowed. How can i build such a query? Can anyone provide me some example query? Many thanks in advance.
View Replies !
Here Is My Search Sp!
here is a search tool SP I wrote.How many times have you wanted to search all your Stored procs or views (inadatabase) for a keyword but couldn't!?Well now you can! THis can makes life a lot easier for developers.Would you email me and let me know which part of the world my code isrunning in?thanks FardadJoin Bytes!Use sp_FindStrInObj, you can use it to search in Stored procs (P), Views(V),user-defined functions (F) or Triggers (T) or any combination oral of them in one shot.It has been developed in sql2k and I just run it under sql7. There seems tobesome problems in sql7 with system level objects!-- sp_FindStrInObj '"'-- Finds all " characters in all stored procs, functions, triggers andviews of the-- current database---- sp_FindStrInObj 'fardad' , 'P'-- Finds all occurences of 'fardad' in all stored procs of the currentdatabase------ sp_FindStrInObj '01/15/02' , 'PT'-- Finds all occurences of '01/15/02' in all stored procs & Triggers ofthe current database--================================================== =================CREATE procedure sp_FindStrInObJ@FindStr char(110) =NULL,@ObjList char(20) = NULL,@columnname sysname = NULLas---- By Fardad Kordmahaleh (fardad@acm.org), Would u Email me if you use thisspand like it?-- 8/13/03 Mostly Taken from Sp_Helptext---- sp_FindStrInObj----Finds a passed string in the body of all stored procedures (P),--user-defined functions (F), triggers (T) or views (V) of your currentDatabase.----Syntax--sp_FindStrInObj [ @FindStr = ] 'SearchString' , [ @ObjList = ] 'ObjTypes'----Arguments----[ @FindStr = ] 'SearchString'----Is the string to be searched in the object types specified by the nextargument.------[ @ObjList = ] 'ObjTypes' 'P', 'F' , 'T' , 'V' or anycombination----Optional, Default will search all stored procedures (P),user-definedfunctions (F), triggers (T)--and views (V) of your current Database. Passing a stringwith 'P', 'F' , 'T' , 'V' or any--combination of all of them will search the passed object type(s).------Return Code Values--0 (success) or 1 (failure)----Remak: It does not work with Encrypted Stored procs-- Known Problem: For Objects that are not dbo owned, where sp_helptext doesnot work this proc does not work either-- I have encountered this problem in version 7-- Does not work if Sp_helpText fails---- Eaxamples:---- sp_FindStrInObj '"'-- Finds all " characters in all stored procs, functions, triggers andviews of the-- current database---- sp_FindStrInObj 'fardad' , 'P'-- Finds all occurences of 'fardad' in all stored procs of the currentdatabase------ sp_FindStrInObj '01/15/02' , 'PT'-- Finds all occurences of '01/15/02' in all stored procs & Triggers ofthe current database--set nocount ondeclare @dbname sysname,@BlankSpaceAdded int,@BasePos int,@CurrentPos int,@TextLength int,@LineId int,@AddOnLen int,@LFCR int --lengths of line feed carriage return,@DefinedLength int/* NOTE: Length of @SyscomText is 4000 to replace the length of** text column in syscomments.** lengths on @Line, #CommentText Text column and** value for @DefinedLength are all 255. These need to all have** the same values. 255 was selected in order for the max length** display using down level clients*/,@SyscomText nvarchar(4000),@Line nvarchar(255)declare @MyLineNo int,@MaxLineNo int,@STRLoc int,@InstsFound int,@TotInstsFound int,@objname nvarchar(776),@ObjtypeCd char(2),@ObjtypeDesc char(25),@TotObjsfound int-- error checkingif @FindStr is NULLbeginselect 'You must gimme a string to find!!! Usage: sp_FindStrSpTr'+char(39)+'XYZ'+char(39)+' , '+char(39)+'FVP'+char(39)return (1)end--initselect @TotObjsfound=0select @TotInstsFound=0-- not using this so turn it offselect @columnname = null--select @ObjListCREATE TABLE #Objlist (ObjType char(2), Desctxt char(25))if @objList is Null -- defaultis all valuesbegininsert #Objlist values ('FN', 'Function')insert #Objlist values ('P' , 'Stored Procedure')insert #Objlist values ('TR', 'Trigger')insert #Objlist values ('V' , 'View')endelsebeginif charindex('FN',@objList,0)<>0 insert #Objlist values ('FN','Function')if charindex('F',@objList,0)<>0 insert #Objlist values ('FN','Function')if charindex('Function',@objList,0)<>0 insert #Objlist values('FN', 'Function')if charindex('Fun',@objList,0)<>0 insert #Objlist values('FN', 'Function')if charindex('P',@objList,0)<>0 insert #Objlist values ('P','StoredProcedure')if charindex('Proc',@objList,0)<>0 insert #Objlist values('P','StoredProcedure')if charindex('sp',@objList,0)<>0 insert #Objlist values ('P','StoredProcedure')if charindex('TR',@objList,0)<>0 insert #Objlist values ('TR','Trigger')if charindex('T',@objList,0)<>0 insert #Objlist values ('TR','Trigger')if charindex('TRigger',@objList,0)<>0 insert #Objlist values('TR', 'Trigger')if charindex('V',@objList,0)<>0 insert #Objlist values ('V', 'View')if charindex('View',@objList,0)<>0 insert #Objlist values ('V','View')if charindex('Vu',@objList,0)<>0 insert #Objlist values ('V', 'View')endif (select count(*) from #Objlist) <=0beginselect ' The object types you passed are not known, use P (procedure), F(Function), T (Trigger) or V (View)!!! Usage: sp_FindStrSpTr '+char(39)+'XYZ'+char(39)+' , '+char(39)+'FVP'+char(39)return (1)endDECLARE xxx CURSOR FORselect name, type from sysobjects where type in (select objtype from#objlist) order by type, namefor read onlyOPEN xxxFETCH NEXT FROM xxx INTO @objname , @ObjtypeCdWHILE @@FETCH_STATUS = 0BEGINSELECT @ObjtypeDesc =CASE @ObjtypeCdWHEN 'FN' THEN 'User-defined Function'WHEN 'P' THEN 'Stored Proc'WHEN 'TR' THEN 'Trigger'WHEN 'V' THEN 'View'ELSE 'unknown Type, are we at SQL Server 2010!!!!'END-- initializingselect @MyLineNo=0select @MaxLineNo=0select @STRLoc=0select @InstsFound = 0select @BlankSpaceAdded =0select @BasePos =0select @CurrentPos =0select @TextLength =0select @LineId =0select @AddOnLen =0select @LFCR =0select @DefinedLength =0select @SyscomText =Null --nvarchar(4000)select @Line = Null --nvarchar(255)Select @DefinedLength = 255SELECT @BlankSpaceAdded = 0 /*Keeps track of blank spaces at end of lines.NoteLen function ignorestrailing blank spaces*/CREATE TABLE #CommentText(LineId int,Text nvarchar(255))/*** Make sure the @objname is local to the current database.*/select @dbname = parsename(@objname,3)if @dbname is not null and @dbname <> db_name()beginraiserror(15250,-1,-1)return (1)end/*** See if @objname exists.*/if (object_id(@objname) is null)beginselect @dbname = db_name()raiserror(15009,-1,-1,@objname,@dbname)return (1)end-- If second parameter was given.if ( @columnname is not null)begin-- Check if it is a tableif (select count(*) from sysobjects where id = object_id(@objname)andxtype in ('S ','U ','TF'))=0beginraiserror(15218,-1,-1,@objname)return(1)end-- check if it is a correct column nameif ((select 'count'=count(*) from syscolumns where name =@columnnameand id = object_id(@objname) and number = 0) =0)beginraiserror(15645,-1,-1,@columnname)return(1)endif ((select iscomputed from syscolumns where name = @columnname and id =object_id(@objname) and number = 0) = 0)beginraiserror(15646,-1,-1,@columnname)return(1)endDECLARE ms_crs_syscom CURSOR LOCALFOR SELECT 'Booo 1-->' +text FROM syscomments WHERE id = object_id(@objname) and encrypted = 0 and number =(select colid from syscolumns where name =@columnnameand id = object_id(@objname) and number = 0)order by number,colidFOR READ ONLYendelsebegin/*** Find out how many lines of text are coming back,** and return if there are none.*/if (select count(*) from syscomments c, sysobjects o where o.xtypenotin ('S', 'U')and o.id = c.id and o.id = object_id(@objname)) = 0beginraiserror(15197,-1,-1,@objname)return (1)endif (select count(*) from syscomments where id = object_id(@objname)and encrypted = 0) = 0beginraiserror(15471,-1,-1)return (0)endDECLARE ms_crs_syscom CURSOR LOCALFOR SELECT /*'Booo 2--> ' +*/ text FROM syscomments WHERE id =OBJECT_ID(@objname) and encrypted = 0ORDER BY number, colidFOR READ ONLYend/*** Else get the text.*/SELECT @LFCR = 2SELECT @LineId = 1OPEN ms_crs_syscomFETCH NEXT FROM ms_crs_syscom into @SyscomTextWHILE @@fetch_status >= 0BEGINSELECT @BasePos = 1SELECT @CurrentPos = 1SELECT @TextLength = LEN(@SyscomText)WHILE @CurrentPos != 0BEGIN--Looking for end of line followed by carriage returnSELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,@BasePos)--If carriage return foundIF @CurrentPos != 0BEGIN/*If new value for @Lines length will be > then the**set length then insert current contents of @line**and proceed.*/While (isnull(LEN(@Line),0) + @BlankSpaceAdded + @CurrentPos-@BasePos + @LFCR) > @DefinedLengthBEGINSELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +@BlankSpaceAdded)INSERT #CommentText VALUES( @LineId,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,@BasePos,@AddOnLen), N''))SELECT @Line = NULL, @LineId = @LineId + 1,@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0ENDSELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N'')SELECT @BasePos = @CurrentPos+2INSERT #CommentText VALUES( @LineId, @Line )SELECT @LineId = @LineId + 1SELECT @Line = NULLENDELSE--else carriage return not foundBEGINIF @BasePos <= @TextLengthBEGIN/*If new value for @Lines length will be > then the**defined length*/While (isnull(LEN(@Line),0) + @BlankSpaceAdded +@TextLength-@BasePos+1 ) > @DefinedLengthBEGINSELECT @AddOnLen = @DefinedLength -(isnull(LEN(@Line),0) +@BlankSpaceAdded)INSERT #CommentText VALUES( @LineId,isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText,@BasePos, @AddOnLen), N''))SELECT @Line = NULL, @LineId = @LineId + 1,@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =0ENDSELECT @Line = isnull(@Line, N'') + isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N'')if LEN(@Line) < @DefinedLength and charindex(' ',@SyscomText,@TextLength+1 ) > 0BEGINSELECT @Line = @Line + ' ', @BlankSpaceAdded = 1ENDENDENDENDFETCH NEXT FROM ms_crs_syscom into @SyscomTextENDIF @Line is NOT NULLINSERT #CommentText VALUES( @LineId, @Line )--select lineid, Text from #CommentText order by LineIdselect @MyLineNo=0select @MaxLineNo= max(lineid) from #CommentTextselect @InstsFound = 0WHILE @MyLineNo <= @MaxLineNoBEGINselect @STRLoc=charindex(ltrim(rtrim(@FindStr)),text,0) from #CommentTextwhere Lineid = @MyLineNoif ( @STRLoc <> 0)begin-- select lineid as 'Line No', Text as 'Line' from #CommentText whereLineid = @MyLineNo order by LineIdselect ltrim(rtrim(str(lineid))) as 'Line No', ltrim(rtrim(Text))as 'Line' from #CommentText where Lineid = @MyLineNo order by LineIdselect @InstsFound = @InstsFound + 1endselect @MyLineNo = @MyLineNo + 1ENDselect @TotInstsFound = @TotInstsFound + @InstsFoundif ( @InstsFound > 0)beginselect str(@InstsFound)+' Instance(s) of >'+ltrim(rtrim(@FindStr))+'<werefound in '+rtrim(@ObjtypeDesc)+': '+@objname+' in'+db_name()+char(10)+char(10)+char(10)+char(10)-- select char(10)+char(10)+char(10)select @TotObjsfound = @TotObjsfound + 1end--else--begin-- select 'No Instance(s) of >'+ltrim(rtrim(@FindStr))+'< were foundin '+rtrim(@ObjtypeDesc)+'(s) in '+db_name()-- select char(10)+char(10)+char(10)--endCLOSE ms_crs_syscomDEALLOCATE ms_crs_syscomDROP TABLE #CommentTextFETCH NEXT FROM xxx INTO @objname , @ObjtypeCdENDCLOSE xxxDEALLOCATE xxx--select 'The object(s) searched were of the following types: 'set nocount onselect distinct desctxt as 'The object(s) searched were of the followingtypes: ' from #objlistdrop table #Objlistselect 'Total of '+ltrim(rtrim(str(@TotInstsFound)))+' Instance(s) of[color=blue]>'+ltrim[/color](rtrim(@FindStr))+'< were found in '+ltrim(rtrim(str(@TotObjsfound)))+'Object(s), in '+db_name()+' database.' as 'SUMMARY:'return (0)GO=========================================
View Replies !
RSS Search
Hi! I'm looking for ideas on what would the best approach to design asearch system for a RSS feeds. I will have some 50 RSS feeds (all RSS2.0 compliant) stored locally on the web server. Now I'm wonderingwhat would the best method to allow searching of these RSS files.Since the search will cater to multiple users the search system has tobe robust and efficient. Some ideas that I have for the RSS searchsystem are:1. Store all RSS files locally on the web server file system andperform file system queries. But I guess this might get slow when anumber of users try to search. Moreover, the queries may not beextensible (for example to allow boolean operations etc).2. Move the RSS data to the database and then search perform searchusing LIKE (or the more advanced indexing service features).3. Use a 3rd party full-text search engine like Lucene.4. Use something like XQuery or XPath to query the RSS files directlybut this again *might* (not sure since I haven't worked with either)get slow when a number of users try to search.Also, the RSS files I have on the web server will be updated everyhour or so.So, I have the ideas but I'm not quite sure which one would the mostsuitable and efficient. If anyone has ideas on implementing such asearch system for RSS feeds then please share your insight. Thank youguys!
View Replies !
SQL Search
Hi all! Im pretty new to all this stuff - just purchased my first SQL database so learning loads at the minute. I need to create a search facility on my site using my SQL database but wanna do it properly and (hopefully) understand how it works too!! Therefore does anyone know anywhere where I might be able to get tutorials to help me?? Many thanks Andy
View Replies !
|
TRACKER
