Search All Code For Specific Keyword
Mar 14, 2008
This will work on SQL Server 2005 and later.
Since the code is building an XML string, keywords overlapping the magic 4000 character limit are fetched!SELECTp.RoutineName,
'EXEC sp_helptext ' + QUOTENAME(p.RoutineName) AS [Exec]
FROM(
SELECTOBJECT_NAME(so.ID) AS RoutineName,
(SELECT TOP 100 PERCENT '' + sc.TEXT FROM SYSCOMMENTS AS sc WHERE sc.ID = so.ID ORDER BY sc.COLID FOR XML PATH('')) AS Body
FROMSYSOBJECTS AS so
WHEREso.TYPE IN ('C', 'D', 'FN', 'IF', 'P', 'R', 'RF', 'TF', 'TR', 'V', 'X')
) AS p
WHEREp.Body LIKE '%YourKeyWordHere%'E 12°55'05.25"
N 56°04'39.16"
View 5 Replies
ADVERTISEMENT
May 27, 2007
I am trying to implement a band search on my web site (concert listings) and would like it to behave a bit more intelligently than a standard match on the band name.
At the moment I have a stored procedure that just selects every show that features a band with exactly the same name as the search term. What I'm now trying to do is when the user enters a band name containing the '&' character I would also like to search using the word and 'and'. For example, if they search for 'Rise & Fall', they should get details on all shows featuring 'Rise & Fall' OR 'Rise And Fall'. Is it possible to do this within my stored procedure?
View 2 Replies
View Related
Jan 31, 2008
Greetings, I am a php developer, and running a little bit out of deadline in a project. Can someone provide me with a VERY simple way to implement search by keyword in C#? I have already implemented a search page (according to firstname, lastname etc) that works on a drop down menu (where you have the option to choose seach by keyword) . So, I need to change something in my SQL query to make this work. I already knew from my SQL experience that the simplest and probably the SLOWEST and MOST UN EFFICIENT one was using LIKE. I don't mind using it but I can't since I will end up having something close to that: SELECT * FROM users WHERE keyword_entered LIKE @keyword; (or '@keyword) which does not work. however SELECT * FROM users WHERE keyword_entered LIKE 'somename%'; does work! I guess the trick is in putting the % after the keyword. ( I would have done that in php by putting the entered keyword in a string and than add to it % and pass it to the SQL query and I dunno how to do that in .NET)any ideas?
View 7 Replies
View Related
Apr 18, 2008
Hi,
I have a table like
ProductId, Description, Description2
where Description and Description2 are text datatypes.
I'm trying to return all records where myKeyword exists as a singular word in either of these two fields.
Should I create a child table where each word in each of these fields has its own row for each product and query against that or is there an efficient way of querying this result without creating the extra child table?
Many thanks for any pointers
Dan
View 4 Replies
View Related
Apr 11, 2005
I’m working on a project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are ‘text’ fields that hold paragraphs of text. The user interface has a ‘general search’ option so that they can enter a number of key words and the database will return a count of the records found containing the keywords.
At the moment I split the input and then build a query based on their input. For instance if they enter ‘hello world’ the input is split into two strings ‘hello’ and ‘world’. I then build the query in a loop and get a query like so:
Select Count(ID) as myCount FROM myTable WHERE (colOne like ‘%hello%’ AND colOne like ‘%world%’) OR (colTwo like ‘%hello%’ AND colTwo like ‘%world%’) OR (colThree like ‘%hello%’ AND colThree like ‘%world%’)
Unfortunately this query runs EXTREMELY slowly and just seems wrong. Is there a more efficient way I should be doing these types of searching? This method works ok on 100 records, but this is the first time I have worked on such a large database.
Is it also possible to search a text column and look for exact matches?
For instance I have 2 records with their textfield containing:
Rec 1: the news for today is blah blah. Rec 1: this is a new item
If I currently search for ‘new’ (select colID from myTable where colOne like ‘%new%’) I will get both these records, but I’d really only like to pull out the second record.
Any help would be great appreciated! :)
View 1 Replies
View Related
Sep 11, 2003
Hi,
I am in the middle of developing procedure for keyword search for our website.
Input parameter is a string consisting keywords in comma delimited format.
This is the example of data I get from business group which I use to populate my key_search table.
product_id keyword
1 Microsoft, training
1CA, DBA
1CA, developer
1CA, network admin
1AZ, DBA
1AK, developer
1MN, DBA
1MN, developer
1OH, developer
2Microsoft, training
2AZ, DBA
2AZ, developer
2IL, developer
2MN, DBA
2CA, developer
2NY, business analyst
2NY, DBA
2NY, developer
2NY, programmer
3Oracle, training
3finance
4Oracle demo
4logistic
4Oracle Financials
4Financial Analyzer
They have provided search string examples like 'Microsoft, DBA, CA'
or 'CA'
or 'Microsoft, developer' or 'training'
I have script ready to remove comma from the string and store words from the input string in a temporary table.
But this is the easiest part.
The Confusing part now for me is to write the optimal code for retrieving the exact match from the key_search table as there is no limit on how many words can be in the string, it can be 1, 2, 3 or more.
Any suggestions on how should I handle this search?
View 6 Replies
View Related
Jul 23, 2005
Is it possible to write a Stored Procedure that takes a string ofsearch keywords as argument and returns the recordset? At the mo I ampassing the WHERE String as argument.I got this technique from an Extreme Ultradev tutorial by Rick Curtisit looked quite ok:http://www.princeton.edu/~rcurtis/u...tutorial12.htmlI have to admit, one of the main reason for passing the WHERE string isthat I do not know how to do the string splitting / parsing and puttingtogether in a Stored Procedure. I bet T-SQL would be just as powerfulas VBScript if I just knew it well enough.What I liked about having built them on the web script was theflexibility allowing to potentially build an advanced search withouthaving to change the stored procedure - but this is not crucial I couldalways write several stored procedures or add parameters to the SP.Here is what I have achieved in this way:User can enter one ore more keywords separated by space.Search algorithm returns results across a number of fields where ALLsearch words are contained in any of these.Search results will always be formatted a certain way and displayed ina html table no matter how the search procedure / criteria is varied.Here is the algorithm (that now works in ASP)1. split search string into separate keywords2. build where condition based on single keyword, concatenating allsearched fields (" AND f1+' '+f2+{' '+f<n>} LIKE %<keyword>%")3. concatenate all these where conditions and pass to stored procedure.4. stored procedure takes care of all other logic (e.g. Joins, whichfields are searched etc.). It uses a string variable @SQL to build thecomplete search string and then doesexecute (@SQL);to create the recordset.I bet there is a way to move 1. 2. and 3. into the SP (and I would feelbetter if it was) but I don't have the expertise to do this. If anybodywants to help me this is very welcome.I can also post my original code to clarify, just want to avoid toolong posts.CheersAxel
View 4 Replies
View Related
Nov 10, 2006
Hi
I have a "columnname" and i have 120 triggers defined on tables in my database. I wanted to know as is there any way to find out how many triggers out of these are defined on the "columnname" like if i do any updatesedit to this "columnname" then how many triggers out of 120 defined are fired cascadingly. Also, this "columnname" is mentioned in 43 tables in my database. Let me know if you were not able to get my question.
Thanks!
View 2 Replies
View Related
Mar 14, 2008
Hii
I want to search for a keyword in the database.
The database has approximately 30 tables and the amount of data in tables is very large. Most of the tables hold 25000 rows. The search procedure for searching a keyword that i want to use is as below. When i executed the stored proc it took 20 minutes. What i want to know is full-text search a better option than this or is there any other way out.
CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
AND TABLE_NAME = PARSENAME(@TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
EXEC SearchAllTables 'FileName'
View 7 Replies
View Related
Sep 25, 2013
I'm using full text search in sql server 2008 with contain keyword.
My issue is while I'm trying
select * from tbl_item_master where contains (ITM_FULLTEXT,'red and white')
above syntax gives me required output but while I'm trying
select * from tbl_item_master where contains (ITM_FULLTEXT,'red and the and white')
above syntax does not gives me output. I'm using system stop list and "the" is noise keyword.
I want to stop searching "the" but want result as per first syntax.
I do get warning " The full-text search condition contained noise word(s)."
Is it possible?
View 1 Replies
View Related
Nov 30, 2015
I have a table name like "WebSearchPage" which contains near about 16 millions records and structure is likeID, PID, Pagename, Title, MetaDesc, Meta Keyword, BodyDesc..Now I have a input parameter a "Keyword" which can be single or multiple words("Cricket/international cricket").Now I have to check if that input "keyword" is exists in any column(Pagename, Title, MetaDesc, MetaKeyword, BodyDesc) from "WebSearchPage" ..then I have to select that row..
View 3 Replies
View Related
Sep 8, 2006
Hi Guys, I hope someone here can help me. I am writing a stored procedure that simply searches for a given value across multiple databases on the same server. So far all well and good.Now, the problem is if the user types in more than one word into the search field.I have put a partial section of code here, there is obviously more, but most of it you wouldn't need to see. SELECT @sql = N'SELECT @count = COUNT('+ @dbname +'.dbo.orders.order_id) FROM '+ @dbname +'.dbo.orders '+
N' INNER JOIN '+ @dbname +'.dbo.customer ON '+ @dbname +'.dbo.orders.cust_id = '+ @dbname +'.dbo.customer.cust_id '+
N' WHERE '+ @dbname +'.dbo.customer.forename LIKE ''%'+ @SearchStr + '%'' OR '+ @dbname +'.dbo.customer.Surname LIKE ''%'+ @SearchStr + '%'''
EXEC sp_executesql @sql, N'@count int OUTPUT', @count = @results OUTPUT Now this code works perfectly well if the user only enters one word, however i need to make sure that the Stored procedure will function if the user enters 2 words, such as John Smith. I need the procedure to search the forename for 'john' & 'Smith' and the same for the surname. It should also work if the user type 'John Michael Smith' - if you understand.I am really struggling with this one.Thanks in advance.Darren
View 2 Replies
View Related
Jun 6, 2008
I have been informed that all my keyword search solutions are susceptible to SQL injection attacks. Does anyone have links discussing basic ' multiple ' keyword search solutions? I would think this is a very common routine (perhaps so much so than only newbies like myself do not know it). I have read the posts about escaping ', doing replace " ' ", " '' ", using parameters and yet every multiple keyword solution I come up with is said to be injection prone.
Example: visitor enters: Tom's antiquesinto a TextBox control and the C# code behind securely generates the below call to the database.
SELECT L_Name, L_City, L_State, L_Display FROM tblCompanies WHERE L_Kwords LIKE '%' + 'Tom's' + '%' AND L_Kwords LIKE '%' + 'antiques' + '%' AND L_Display = 1 RETURN
I understand that concantenting string parts using an array and then passing the sewn together string to a stored procedure exposes it to injection. I hope that my single keyword routine below is secure, if it is not then I am not understanding how parameterized SP are supposed to be constructed to protect against injection.string CompanyName;CompanyName = TextBox1.Text;PROCEDURE CoNameSearch @CompanyName varchar(100)AS SELECT DISTINCT L_Name, L_Phone, L_City, L_State, L_Zip, L_Enabled, L_Display FROM tblLinksWHERE (L_Name LIKE @CompanyName + '%') AND L_Enabled = 1 AND L_Display = 1 ORDER BY L_NameRETURN
View 5 Replies
View Related
Apr 8, 2008
Hi,
When using FREETEXTTABLE the RANK returned seems to be partially based on keyword density. Has anyone come across a method of compensating for this keyword density so that good matches found in a lot of text and a small amount of text return the same RANK?
For an example look at the site I'm working on when someone searches for "whale watching" - http://www.yougodo.com/Search.aspx?ks=whale+watching - you can see that we are showing poor quality results at the top as our first sort order is based on RANK.
If we could remove the keyword density factor from RANK then this would allow second order sorting criteria to come in to play and move the more valuable results to the top.
Any help, pointers, advice would be greatly appreciated,
Gavin.
www.gavinharriss.com
View 1 Replies
View Related
Apr 8, 2015
I am searching for the key word 'Platform Customer Support' using full text search. My code is as below
Set @KeywordSearch = 'Platform Customer Support'
Select AA, BB, CC, DD from SM9..TableName A Right Outer Join SM9_Experiment..TableName C
On A.IncdTouchedGSF like '%' + C.SM9GroupName + '%'
Where
(
Contains(A.[Description], @KeyWordSearch)
And A.OpenTime Between @StartDate and @EndDate
And C.Classification = @GroupNameClassification
)
The code is throwing:
Msg 7630, Level 15, State 3, Line 46
Syntax error near 'Customer' in the full-text search condition 'Platform Customer Support.
View 2 Replies
View Related
May 18, 2007
Dear all
I am pretty new to SQL server 2005 and have the following issue:
I get the date from the asp applciation in format dd/mm/yyyy and then try to do a search for all records that might match that date in sql server 2005. The date is storred in ther database in datetime format.
I am having trouble composing the stored procedure to do that.
Any help would be apreciated.
Sincerely
d
View 2 Replies
View Related
May 26, 2006
Hi All,I'm familiar with the syntax that looks like this:SELECT * FROM Users WHERE Email LIKE '%aol%'Which would return all users that whose Email column contains "aol".However, if I wanted to do that sort of a search using a prepared statement, how would I do it?I can't simply doSELECT * FROM Users WHERE Email LIKE '%@MatchAgainst%'Can somebody clue me in?
View 1 Replies
View Related
Sep 16, 2015
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
[Code] ....
View 9 Replies
View Related
Sep 14, 2007
SQL 2000.I need a query that will search a field in the table that is 14characters long and begins with a number.I have a client that was allowing people to store credit card numbers,plain text, in an application. I need to rip through the table andreplace every instance of credit card numbers with "x" and the last 4digits. I got the replace bit going, but I am stuck on how to searchfor a string in a field of a specific length.Any ideas?Thanks,--Mike
View 5 Replies
View Related
May 14, 2015
One of my models has order data, cost per order/invoice ID and then dimensions on Fiscal Year, category, etc...the usual.
A user wanted to search it for an exact order amount. (They knew for example that one of our accounts was not balancing by single order worth $746.13 and assumed it must be an order that was placed but never marked shipped that slipped through the cracks).
Now, in the model I have "order amount" as a field and then a measure that sums that.
I could expose that "order amount" field as a label and let them filter on it in Excel (and that works).
However, I haven't had any luck filtering on the actual measure "Total Order Amount". Such as OrderID-> View Filter -> "Total Order Amount" equals 746.13.
I assume this is due to a few things:
Measure calculates at different levels so filtering on a measure is difficult as you would have to place all the "slicers" and set them first before the measure would "exist" at a level where it could be $746.13. Orders by year would have $746.13 as part of it's year sum, but wouldn't exist as a stand alone line item orders by year 2015 might be 2 million.
Orders by category might exist at 500,000, 8,000, 15,146.36, etc... but not $746.13.
So I would need OrderID on there as a column so the measure could return at the value of $746.13 for one row for it to match the filter?
Basically:
1. Why it can't really filter on a measure?
2. Is there a better way to accomplish this other than exposing the actual column in the fact table "order amount" as it feels like that could cause all kinds of confusion if other users try to slice/filter on that not realizing exactly what it is meant to be?
View 3 Replies
View Related
Apr 16, 2008
Anyone please suggest me that
How to search specific word present multiple times from a paragraph stored in sqlserver database using asp.net 2.0
With Regards
Tapan
View 4 Replies
View Related
Nov 14, 2005
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 2 Replies
View Related
Mar 17, 2004
I have been using the following code on a search page for some time, is has worked very well. We recently changed our database to support multiple addresses for each client. So I added the INNER JOIN on the tblClientAddresses. But now when I try to search on a ID I get an ambiguous cloumn name error on the ID. Can anyone see how I could correct this?
Thanks for any suggestions,
Sub BindDataForPaging(ByVal sortExpr As String)
Dim MyConnection As SqlConnection
Dim MySQLAdapter As SqlDataAdapter
Dim DS As DataSet
Dim ConnectStr As String
Dim WhereClause As String
Dim SelectStatement As String
If SearchLastName.Text = "" And SearchFirstName.Text = "" And _
SearchID.Text = "" And SearchCompanyName.Text = "" And _
SearchSal1.Text = "" And SearchComment.Text = "" And SearchAddress.Text = "" Then
Message.Text = "You didn't enter any search parameters. Try Again."
Exit Sub
End If
WhereClause = "Where "
If SearchLastName.Text <> "" Then
WhereClause = WhereClause & "[LastName] Like '" & _
SearchLastName.Text & "%" & "' AND "
End If
If SearchFirstName.Text <> "" Then
WhereClause = WhereClause & "[FirstName] Like '" & _
SearchFirstName.Text & "%" & "' AND "
End If
If SearchID.Text <> "" Then
WhereClause = WhereClause & "[ID] = " & _
SearchID.Text & " AND "
End If
If SearchCompanyName.Text <> "" Then
WhereClause = WhereClause & "[CompanyName] Like '" & _
SearchCompanyName.Text & "%" & "' AND "
End If
If SearchSal1.Text <> "" Then
WhereClause = WhereClause & "[Sal1] Like '" & _
SearchSal1.Text & "%" & "' AND "
End If
If SearchComment.Text <> "" Then
WhereClause = WhereClause & "[Comments] Like '" & "%" & _
SearchComment.Text & "%" & "' AND "
End If
If SearchAddress.Text <> "" Then
WhereClause = WhereClause & "[Address] Like '" & "%" & _
SearchAddress.Text & "%" & "' AND "
End If
If ClientTypeDrop.SelectedItem.Text <> "" Then
WhereClause = WhereClause & "[CLientType] Like '" & "%" & _
ClientTypeDrop.SelectedItem.Text & "%" & "' AND "
End If
If Right(WhereClause, 4) = "AND " Then
WhereClause = Left(WhereClause, Len(WhereClause) - 4)
End If
SelectStatement = "Select *,A.Address FROM tblClients INNER JOIN dbo.tblClientAddresses A ON dbo.tblClients.ID = A.ID " & WhereClause & " ORDER BY " & sortExpr
Try
ConnectStr = ConfigurationSettings.AppSettings("ConnectStr")
MyConnection = New SqlConnection(ConnectStr)
MySQLAdapter = New SqlDataAdapter(SelectStatement, MyConnection)
DS = New DataSet
MySQLAdapter.Fill(DS)
MyDataGrid.DataSource = DS
MyDataGrid.DataBind()
Catch objException As SqlException
Dim objError As SqlError
For Each objError In objException.Errors
Response.Write(objError.Message)
Next
End Try
End Sub
View 4 Replies
View Related
May 2, 2005
Hello. I have gotten my search page to work when a user enters in the entire loan number. I want to give them the option to enter in a partial loan number. I have the following code for Access database.
If Page.IsPostBack Then
MySearch = "loanno Like '*" & txtSearch.Text & "*'"
Else
txtSearch.Text = ""
End If
I need to convert this for a SQL database search. I tried to substitute the * with % and I am still getting an error. Does anyone know how to fix this problem.
Also, It would be nice to make MySearch reference a stored procedure. This would make the page much more flexible and powerful. I am open to any recommendations. Thank you.
View 4 Replies
View Related
Jul 6, 2005
I have code on a website that does a search to an Access database based on what the person enters as the search criteria. My SQL code uses a like % statement for all the fields in the search criteria. The problem is that the first and third search fields do no return the search criteria that is asked for.....The 2nd search field (Description) returns whatever is asked for..... I did read up and found that the data must be character for the like statement. Anyone have any other clues or where I can read up on this???????HTML SEARCH CODE: <table ALIGN="CENTER" width="408" bgcolor="#FFFFFF" cellspacing="0" cellpadding="5"> <tr> <td width="400" bgcolor="#3366cc" colspan="2"> <form method="post" name="DaForm" action="Makeit.asp" align="center"> <div align="center"><center><table border="1" width="350" cellpadding="3" bgcolor="#3366cc"> <tr> <td align="left" width="189"><select name="TypeSearch" size="1"> <option selected value="PartNumber">Part Number</option> <option value="Description">Description </option> <option value="Mfg">Manufacturer</option> </select></td> <td width="310" align="right"><input type="text" size="30" name="DaInBox"></td> </tr> <tr> <td colspan="3" align="center" width="507"><div align="left"><p><input type="submit" name="B1" value="Search!"><input type="reset" name="B2" value="Clear"> </td> </tr> </table> </center></div> </form> </td> </tr></table>Makeit ASP COde:
<%@ LANGUAGE="VBSCRIPT" %>
<html>
<head>
<title>Your Search Results!</title>
<meta name="Microsoft Border" content="lb, default">
</head>
<body>
<%
Dim MySql
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open("parts")
MySql = "SELECT * FROM parts"
If Request.Form("TypeSearch") = "Part Number" Then
MySql = MySql & " WHERE PartNumber LIKE '%" &_
Request.Form("DaInBox") & "%'"
End If
If Request.Form("TypeSearch") = "Description" Then
MySql = MySql & " WHERE Description LIKE '%" & _
Request.Form("DaInBox") & "%'"
End If
Dim rs
Set rs = new Server.CreateObject("ADODB.Recordset")
rs.Open MySql, cn, 0,1
%>
<% If rs.BOF and rs.EOF Then%>
<h2 align="center">We did not find a match!</h2>
<%Else%>
<%If Not rs.BOF Then%>
<h2>Here are the results of your search:</h2>
<table BORDER="0" width="100%" cellpadding="3">
<tr>
<th bgcolor="#0366cc"><font face="Arial" color="#000000">Part Number </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Description </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Manufacturer </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Qty </font></th>
<th bgcolor="#3366cc"><font face="Arial" color="#000000">Condition </font></th>
</tr>
<%
Do While Not rs.EOF
%>
<tr>
<td><%=rs("PartNumber")%>
<%=rs("Description")%>
</td>
</tr>
<% rs.MoveNext
Loop
%>
</table>
<%End If%>
<%End If%>
<%
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>
<p> </p>
</body>
View 1 Replies
View Related
May 10, 2000
I have a series of stored procedures defined in a SQL7 database and want search through all their code to find a particular text value (say the contents of a raiserror message for exampl). How can I do this without having to go into each one seperately and scroll through the code? Is it possible to do it from within SQL itself?
Thanks!
Steve Rock
View 1 Replies
View Related
Dec 27, 2006
I'm writing a search for our site and I'm running into a few problems.When I run the query like this, it runs just fine:Declare @Keywords varchar(2000)Select @Keywords = 'modern trial advocacy canada'Select product_id, name, count(name) hitsFROM estore_productsINNER JOIN sequenceON estore_products.name like '%' +Substring(' ' + @keywords + ' ',seq,CharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq)+ '%'WHEREseq <= len(' ' + @keywords + ' ') andSubstring(' ' + @keywords + ' ', seq - 1, 1) = ' ' andCharIndex(' ' , ' ' + @keywords + ' ' , seq) - seq 0Group by estore_products.product_id, nameORDER BY Hits DESCBut when I add another column (for example a column called description)from the select statement I get this error:The text, ntext, and image data types cannot be compared or sorted,except when using IS NULL or LIKE operator.Long story short, what do I need to do to select more columns for thefinal output?Thanks,Ryan
View 4 Replies
View Related
Oct 11, 2006
I'm trying to create a form that allows someone to find an
address from a postal code search in the database. My query works exactly as I’d
like in query builder using the following select:
Select [FIELD_LIST] from addresses WHETE POSTCODE LIKE ‘%’+@POSTCODE+’%’
I then pass the user entered post code to the select statement
which is executed. However, I’m getting some odd behaviour. Assuming there is
an address in the database with the post code
“LS11 0ES”...
If I search for LS11, nothing is returned;
If I search for LS11%, nothing is returned;
If I search for %LS11%, nothing is returned;
If I search for %LS11%%, the data is returned.
If I search for %LS%1%%, the data is returned (as is LS21
0ES etc. etc.).
However, I want the user to be able to enter shorter search
strings and it to pull all the data back out, so they can enter a substring
such as LS and it will pull out all the data without the users needing to enter
the full pattern of % symbols.
If I go into query builder (in visual web developer) and
enter just “LS” it works as I’d want, but not when pulled from a web page.
Any ideas?
Thanks
View 3 Replies
View Related
Jul 20, 2005
I'm trying to execute a full-text query from a vb.net web application.The problem I have is that in SQL Server, the syntax for a full-textsearch isSELECT *FROM tableWHERE CONTAINS( *, ' "searchstring" ')For whatever reason, VB won't run that search string unless I eliminatethe double sets of quotes:SELECT *FROM tableWHERE CONTAINS( *, "searchstring")This will not work for a search phrase - only single words, and it alsowill not work for a wild card:SELECT *FROM tableWHERE CONTAINS ( *, "search*")The above does not work - I must assume that the double quotes areneeded for both wild card searches and exact phrase searches -The problem of course is that in vb code the SQL string is alreadyenclosed in search quotes - the single quotes are then used to indicatedouble quotes within the search.I have tried using two single quotes on either side of the string, butit doesn't seem to work. ie:"SELECT * FROM table WHERE CONTAINS ( ' '" & textbox1.text & "' ')"Does anybody have any ideas?Trevor Fairchild*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Jun 30, 2014
Below is sample SQL:
SQL_1: Creates a database
SQL_2: Creates 2 stored procedures
Now, I need to search database SP`s for Table2 t2 ON t2.CID = t1.CID and ALTER them with Table2 t2 ON t2.CID = t1.CID.
INNER JOIN Table3 t3 ON t3.CID = t1.CID
WHERE CustGroup = 'Employees'SQL_1:
USE [master]
GO
[code]...
View 6 Replies
View Related
Aug 9, 2006
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 3 Replies
View Related
May 23, 2015
I need to select specific values from all rows where the value of a specific column is "Active"
This part works: SELECT LastName, FirstName, MiddleInit, ClientId FROM dbo.Client
But I want to add: WHERE StatusType = (Active) and how to do this.
View 4 Replies
View Related
Jun 30, 2015
We have a "main" SQL 2014 server who imports XML files using SSIS in a datacenter. In remote sites (which are warehouses), there is an instance of SQL 2014 Express. A merge replication is setup, as every operations done on each site must be "forwared" to the main database, as some XML files are generated as output for an ERP system.
Now, the merge replication replicate all the data to the server on each sites. But a specific site don't need the data of every other sites, only the data relevant to itself (which is the warehouse code). Is there a way to replicate only the data relevant to each individual sites to the subscribers? Or is there a better way than replication to accomplish this?
View 2 Replies
View Related
