FREETEXT And CONTAINS Keywords Generate `not A Recognized Function Name`
Sep 14, 1998
I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.
View 2 Replies
ADVERTISEMENT
Oct 16, 2007
Hi,
We have this syntax from Microsoft for Replace function:
REPLACE ( string_expression1 , string_expression2 , string_expression3 )
I want to search for two keywords and not only one:
images and images/ (second one is with back slash.
Is it possible to include something like:
images/ OR images in expression2 like
REPLACE ( string_expression1 ,'images/ OR images', string_expression3 )
I want something like that, how do I get that please?
Thank you.
View 4 Replies
View Related
Feb 4, 2008
Can anybody know ,how can we add builtin functions(ROW_NUMBER()) of Sql Server 2005 into database library.
I get this error when i used into storeprocedure :
ROW_NUMBER() function is not recognized in store procedure.
i used MS SQL SERVER 2005 , so i think "ROW_FUNCTION()" is not in MS SQL SERVER 2005 database library.
I need to add that function into MS SQL SERVER 2005 database library.
Can anbody know how we can add that function into MS SQL SERVER 2005 database library?
View 4 Replies
View Related
Oct 8, 2004
I am using MS SQL Server 2000. I am reading right off of microsofts web site under Trasact-SQL Reference, on how to use the MIN function. Yet when I try to use it, I get the error:
'min' is not a recognized function name
Here is the sql statement copied right out of Query Analyzer:
select name, count(name), min(
case when not(var6 = '') then 7 else
case when not(var5 ='') then 6 else
case when not(var4 ='') then 5 else
case when not(var3 ='') then 4 else
case when not(var2 ='') then 3 else
case when not(var1 ='') then 2 else 1 end end end end end end as cols
)
from syitabs
where dupef=0
group by name
View 2 Replies
View Related
Mar 2, 2006
I am trying to run the following code in SQL Server 2005:
DECLARE @cleartext NVARCHAR(100)
DECLARE @encryptedstuff NVARCHAR(100)
DECLARE @decryptedstuff NVARCHAR(100)
SET @cleartext = 'XYZ'
SET @encryptedstuff = EncryptByPassPhrase('12345', @cleartext)
SELECT @encryptedstuff
SET @decryptedstuff = DecryptByPassphrase('12345', @encryptedstuff)
SELECT @decryptedstuff
and am recieving an error:
Msg 195, Level 15, State 10, Line 5
'EncryptByPassPhrase' is not a recognized function name.
Msg 195, Level 15, State 10, Line 7
'DecryptByPassphrase' is not a recognized function name.
It appears as though this EncryptByPassPhrase and DecryptByPassphrase as supported in 2005 T-SQL commands but when I execute this code in SQL Server Studio it errors out.
Anyone know why?
View 1 Replies
View Related
Nov 27, 2007
I have read all of the messages related to the above problem and none have solved the issue.
We have migrated a SQL 2000 database to SQL 2005. Detached from one and attached to the other and then based on some of the conversation I also used the SQL Server 2005's copy option and copied the database. Detached the old one and renamed the new database which was created in the copy process to the old name. Basically getting back to where I started. By the way there are no triggers generated during the process (as was mentioned), nothing to delete as some of the discussion was suggesting.
Big databse, lots of tables and stored procedures. All screens seem to work except when we try to update some data using a stored procedure which takes the data and updates the table. End up getting the above error during the execution. All used to work fine with SQL Server 2000. I have taken the SQL out of the stored procedure and executed manually and it worked but leaving the stored procedure alone and calling it with the updated data we get the INITCOLVS problem. The database is also set to be compatible to 90 so that suggestion also has not fixed the problem. Looking for additional suggestions and solutions. Some one was talking about making a dummy INITCOLVS function, have not done this yet but don't particularly like this suggestion, like to know the cause and the proper solution to the problem.
Again in summary:
Migrated SQL 2000 database to 2005
Have done the copy and attach process to make sure the whole conversion to SQL Server 2005 has taken place.
Have set the compatibility of the database to 90
Updates cause the above error.
Thanks in advance.
Don
View 1 Replies
View Related
Mar 21, 2013
how to format the last part of this query which comes from Access.:
SELECT Max(TITLOC.TitleLocID) AS MaxOfTitleLocID, TITLES.TitleID, TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID
FROM (TITLES_WARNING_SENT_qry RIGHT JOIN TITLES ON TITLES_WARNING_SENT_qry.TitleID = TITLES.TitleID) INNER JOIN TITLOC ON TITLES.TitleID = TITLOC.TitleID
WHERE TITLES_WARNING_SENT_qry.TitleID Is Null
GROUP BY TITLES.CustLName, TITLES.CustFName, TITLES.RecDT, TITLES.TitleID, TITLES.TitleID
HAVING (TITLES.RecDT<Date()-31)
I am getting:
'Date' is not a recognized built-in function name.
It is probably something simple but how would I go about converting this part "HAVING (TITLES.RecDT<Date()-31)" to something SQL Server is happy with?
View 4 Replies
View Related
Nov 10, 2014
I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4 'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me?
SELECT
TT.PartNumber
,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]
,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])
FROM dbo.vt_TroubleTicket TT
WHERE TT.Closed = '-1'
and TT.DateClosed between '1/1/2013' and '1/1/2014'
and (TT.PartNumber = '12345')
GROUP BY TT.PartNumber
View 3 Replies
View Related
Jan 28, 2008
why I am getting this error on the SQl Server 2005 but not getting it on the SQL Server Express? and how to ifx?
Code Snippet
Msg 195, Level 15, State 10, Procedure sp_check_sp, Line 14
'OBJECT_SCHEMA_NAME' is not a recognized built-in function name.
this is my code:
Code Snippet
USE shefa
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_check_sp]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SELECT DB_NAME(st.dbid) DBName
, OBJECT_SCHEMA_NAME(st.objectid, dbid) SchemaName
, OBJECT_NAME(st.objectid, dbid) StoredProcedure
, MAX(cp.usecounts) Execution_count
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
WHERE DB_NAME(st.dbid) IS NOT NULL AND cp.objtype = 'proc'
GROUP BY cp.plan_handle, DB_NAME(st.dbid),
OBJECT_SCHEMA_NAME(objectid, st.dbid),
OBJECT_NAME(objectid, st.dbid)
ORDER BY MAX(cp.usecounts)
END
View 4 Replies
View Related
Aug 15, 2007
The row_number functions doesn't seem to be operable in our version of SQL Server 2005 SP2.
We have completely tried everything, including using the sample database, and Query posted in the 2005 SQL Server 2005 Book, which is as follows:
SELECT empid, qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownum
FROM dbo.Sales
ORDER BY qty;
I get the following error:
Msg 195, Level 15, State 10, Line 2
'ROW_NUMBER' is not a recognized function name.
Below you will find the versions of SQL we are using of 2005.
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
Please help!! Thanks in Advance,
Jason
View 4 Replies
View Related
Jan 30, 2007
Hello All,
Can anyone tell me what is error is and how to resolve it?
Thanks.
View 4 Replies
View Related
Dec 24, 2007
Hello I am Prasad , I have written one store procedure as below. But It gives error message ROW_NUMBER() function is not recognized. what's the fault or what should i change.
CREATE PROCEDURE GetProductsOnCatalogPromotion(@DescriptionLength INT,@PageNumber INT,@ProductsPerPage INT,@HowManyProducts INT OUTPUT)AS-- declare a new TABLE variableDECLARE @Products TABLE(RowNumber INT,ProductID INT,Name VARCHAR(50),Description VARCHAR(5000),Price MONEY,Image1FileName VARCHAR(50),Image2FileName VARCHAR(50),OnDepartmentPromotion bit,OnCatalogPromotion bit)-- populate the table variable with the complete list of productsINSERT INTO @ProductsSELECT ROW_NUMBER() OVER (ORDER BY Product.ProductID),ProductID, Name,SUBSTRING(Description, 1, @DescriptionLength) + '...' AS Description, Price,Image1FileName, Image2FileName, OnDepartmentPromotion, OnCatalogPromotionFROM ProductWHERE OnCatalogPromotion = 1-- return the total number of products using an OUTPUT variableSELECT @HowManyProducts = COUNT(ProductID) FROM @Products-- extract the requested page of productsSELECT ProductID, Name, Description, Price, Image1FileName,Image2FileName, OnDepartmentPromotion, OnCatalogPromotionFROM @ProductsWHERE RowNumber > (@PageNumber - 1) * @ProductsPerPageAND RowNumber <= @PageNumber * @ProductsPerPage
View 4 Replies
View Related
Apr 4, 2014
I am having a hard time getting a variable recognized in a function. The variable is not being seen properly in the charindex function.
@ExtType contains = X
@PhoneNo contains = +1 (202) 123-9876 X012
select @intPos = charindex(@ExtType,Upper(@PhoneNo))
View 1 Replies
View Related
Sep 8, 2007
Unfortunatly this appers when i am trying to execute the following Query :
SELECT CurrentDate AS EXPR2
FROM [Date]
WHERE (DAy(CurrentDate) = 2)
It seems it doesn't support the (Day) function...Is there any solution or work around for that ?
View 1 Replies
View Related
Mar 20, 2007
Koroner writes "Hi all. I'm very new to SQL programming so when I started coding I already expected some problem like the one I now describe to present itself soon.
I'm not an English native speaker, pardon me for making any mistake.
I'm currently programming in ASP.NET using Visual Studio.
I've defined a function which consists of one quite simple query calling itself in a recursive way. The problem occurrs when running a view to check whether the function works prorperly, since the "nesting limit exceeded" error (not exact words) pops up.
I didn't know there was such a limit.
Searching the Web I've learnt there is a way to modify this limit, that is using the OPTION clause and the MAXRECURSION hint.
But the fact is whenever I add the OPTION clause to the SELECT of my function Visual Studio tells me that there is a syntax error near "OPTION" keyword. I'm sure VS is not recognizing the "MAXRECURSION" keyword, because there are no syntax errors and the word is not colored in blue (unlike other keywords).
What puzzles me most is that I tried to define a recursive SELECT provided with OPTION clause within a view or an on-the-fly query and all works fine there.
Thanks for any reply."
View 7 Replies
View Related
Nov 5, 2007
I don't know whether this is the right place to ask this question. I didn't get the answer from sql forums may be someone here can help. I need to use freetext on two columnssomething likefreetext(column1+column2,@Search) this will give error "Incorrect syntax near '+'."Any One know the correct syntax. Thanks
View 1 Replies
View Related
Jan 24, 2005
I have a vb page, which is a simple front end so I can edit text from anywhere.
The person who is going to use it knows no html, and so I am trying to get freetextbox to work.
I am getting the following error:
Server Error in '/' Application.
--------------------------------------------------------------------------------
ExecuteNonQuery: CommandText property has not been initialized
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.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Source Error:
Line 81:
Line 82: mySelectCmd.Connection.Open()
Line 83: mySelectCmd.ExecuteNonQuery()
Line 84: MyConnString.Close()
Line 85:
The pages code is below:
<%@ Page Language="VB" Debug="True" validateRequest="False"%>
<%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data" %>
<script runat="server">
Dim MyConnString As SqlConnection
Dim mySelectCmd As SqlCommand
Dim mySelectQuery As String
dim myExecuteQuery As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then
ReadMyData()
end if
end sub
Public Sub ReadMyData()
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable
Dim dr As DataRow
dt = New DataTable
dt.Columns.Add(New DataColumn("id", GetType(Integer)))
dt.Columns.Add(New DataColumn("title", GetType(String)))
dt.Columns.Add(New DataColumn("text", GetType(String)))
dt.Columns.Add(New DataColumn("section_no", GetType(String)))
mySelectQuery = "select * from content_text"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
MyConnString.Open()
Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader()
Try
While myReader.Read()
dr = dt.NewRow()
dr(0) = myReader("id")
dr(1) = myReader("title")
dr(2) = myReader("text")
dr(3) = myReader("section_no")
dt.Rows.Add(dr)
End While
Finally
myReader.Close()
MyConnString.Close()
End Try
Pubs.DataSource = DT
Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = -1
ReadMyData()
End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs)
Pubs.EditItemIndex = CInt(e.Item.ItemIndex)
ReadMyData()
End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@title, text=@text, section_no=@section_no where id=@id"
mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer
dim e_title, e_text, e_section_no as string
e_Id = Pubs.DataKeys(CInt(E.Item.ItemIndex))
e_title = CType(e.Item.FindControl("e_title"), TextBox).Text
e_text = CType(e.Item.FindControl("e_text"), FreeTextBox).Text
e_section_no = CType(e.Item.FindControl("e_section_no"), TextBox).Text
mySelectCmd.Connection.Open()
mySelectCmd.ExecuteNonQuery()
MyConnString.Close()
pubs.EditItemIndex = -1
ReadMyData()
End Sub
</script>
<html>
<head>
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-family: Arial, Helvetica, sans-serif; font-size:8pt}
-->
</style>
</head>
<body>
<form runat="server">
<asp:datagrid
id="Pubs"
GridLines="Both"
CssClass="style1"
DataKeyField="id"
Border="0"
CellPadding="2"
font-name="Arial"
font-size="9pt"
OnEditCommand="Pubs_Edit"
OnUpdateCommand="Pubs_Update"
OnCancelCommand="Pubs_Cancel"
Autogeneratecolumns="false"
Showfooter="true"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="8pt"
HeaderStyle-BackColor="#B7CEDF"
Font-Bold="True"
ItemStyle-Font-Name="Arial"
ItemStyle-Font-Size="8pt"
ItemStyle-VerticalAlign="top"
runat="server">
<columns>
<asp:templateColumn>
<HeaderTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left">Word</td>
<td width="15%" align="left">Title</td>
<td width="70%" align="left">Text</td>
<td width="10%" align="left">Section Number</td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table width="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%" align="left"><asp:LinkButton ToolTip="Delete record" CommandName="Delete" runat="server"><img src="images/deleteicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Edit record" CommandName="Edit" runat="server"><img src="images/editicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="15%" align="left"><asp:Label ID="title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><asp:Label ID="text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="server" /></td>
<td width="10%" align="left"><asp:Label ID="section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</ItemTemplate>
<EditItemTemplate>
<table width ="100%" border="0" cellpadding="2" cellspacing="2" class="style1">
<tr>
<td width="5%"><asp:LinkButton ToolTip="Cancel edit record" CommandName="Cancel" runat="server"><img src="images/cancelicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton><asp:LinkButton ToolTip="Update record" CommandName="Update" runat="server"><img src="images/saveicon.gif" alt="" width="12" height="12" border="0"></asp:LinkButton></td>
<td width="20%" align="left"><asp:TextBox Width=200 CssClass="style1" ID="e_title" Text='<%# DataBinder.Eval(Container.DataItem, "title") %>' runat="server" /></td>
<td width="70%" align="left"><FTB:FreeTextBox ToolbarStyleConfiguration="Office2000" id="e_text" Text='<%# DataBinder.Eval(Container.DataItem, "text") %>' runat="Server" /></td>
<td width="5%" align="left"><asp:TextBox Width=50 CssClass="style1" ID="e_section_no" Text='<%# DataBinder.Eval(Container.DataItem, "section_no") %>' runat="server" /></td>
</tr>
</table>
</EditItemTemplate></asp:templateColumn>
</columns>
</asp:datagrid>
</form>
</body>
</html>
View 5 Replies
View Related
Jul 9, 2007
Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.
I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."
I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.
The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).
I do not have replication running and I don't know where to look next.
Server:
SQL 2005 sp1
Windows 2003 Server
Any ideas would be greatly appreciated.
Russ
View 2 Replies
View Related
Nov 4, 2005
I tried the following select:
select * from patientprofile where contains (BillingNotes, '572576-02')
And got the errormessage:
Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'patientprofile' because it is not full-text indexed.
It shows BillingNotes as a text column
Is there a differnet way I can search this note field for records that contain that text value (572576-02)
View 3 Replies
View Related
Nov 5, 2007
Is there a way I could achieve something like this.
freetext(FirstName+LastName,@Name) FirstName and LastName are two columns in my database table.
What I am trying here is allowing the user to type their first name , last name or both. and write a search which can find there record for all the three cases.
Please Advice.
View 1 Replies
View Related
Aug 7, 2007
I'm attempting to query the MS Index Server. I allow the users to supply the search text and when doing that it is best to pass the text into the query via a parameter to protect against SQL Injection attacks. However, for some reason I cannot get the FREETEXT predicate to work with a parameter. The FREETEXT predicate works fine when the query is concatenated together. I'd like an example of the FREETEXT predicate working with MS Index Server (Windows 2003).
The following line works just fine: cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '" + searchText + "') order by rank desc "
I'd like the following lines to work:cmd.Parameters.AddWithValue("@SearchText", searchText)
cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, @SearchText) order by rank desc "
So now I'm wondering if the MS Index Server doesn't support the use of a parameter in this way or if I somehow managed to get the syntax wrong.
Thoughts?
View 2 Replies
View Related
Feb 22, 2000
I've been running Freetexts on ASP through IIS. The problem with this is that the CPU usage is very big.
I've wanted to use Stored Procedures on SQL 7.0 to produce results out of Freetext. But i've been having problems. Please help:
CREATE PROCEDURE sp_match
@asp_str varchar(50)
as
select * from employees_table where
freetext(*, @asp_str )
return
View 1 Replies
View Related
May 15, 2007
Hi,
We are using FULLTEXT searching for searching the title column in SQL 2005 table.
We are aware that using CONTAINS we cannot search middle of the string like the way we do using LIKE '%text%'.
Is there any alternative way of acheiving this functionality using FULLTEXT search or any other ways of doing this keeping in mind performance.
Any feedback on this is greatly appreciated.
thanks and regards,
Krishna.
View 3 Replies
View Related
Aug 23, 2007
Hi,
I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name]
Can I use FREETEXTTABLE instead? How do I go about doing it?
View 2 Replies
View Related
Dec 10, 2007
Hi
I was looking at this link : http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=792720&SiteID=17&pageid=0
I am using SQL SERVER 2005.
I have noiseeng.txt and noiseenu.txt.
I have stopped all the MSSQLSERVER, MSSQLAGENT,BROWSER, EXPRESS and FULL SEARCH.
Then I removed all the numbers from the noiseeng.txt and noiseenu.txt.
I restarted the services again.
I tried to run this query
DECLARE @SearchWord nvarchar(30);
SET @SearchWord = 'MS';
SELECT ProductDescription
FROM Products
WHERE FREETEXT(ProductDescription, @SearchWord);
I can't seem to get any records out and it won't pick the two that I am actually looking for.
In the Products table, it has several rows containing:
MS1234
MS_1234
MS-1234
MS 1234
MS/1234
I am trying to retrieve the above records using just 'MS', but it just doesn't work at all.
Any suggestions, help or examples please - would be most appreciated.
Thanks
Newbie
View 4 Replies
View Related
Apr 20, 2008
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
can anyone give any code snippets?
View 1 Replies
View Related
Sep 12, 2007
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
View 1 Replies
View Related
Aug 11, 2007
Hi,
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Any thoughts appreciated.
Thanks
View 3 Replies
View Related
Aug 20, 1999
Where can I find the list of new keywords for sql 7.0? Namely the keyword to allow duplicates when creating a table?
Thanks!
View 1 Replies
View Related
Sep 21, 2007
does anyone know whether the words 'comments' and 'narrative' have become reserved keywords post-SQL2000 ?
View 2 Replies
View Related
Apr 25, 2008
When designing a table, or column name. I am sure its best to avoid using table names or column names that use reserved words such as
"status" and "message"
What happens when we name a column or table using these words? Any tiny performance hit ? I am just wondering whats happening behind the scenes.
Thanks!
mike123
View 8 Replies
View Related
May 16, 2008
How can I find distinct ID'd in this table that has similar keywords to an ID I pass in like for example I pass in 1. It has 3 keywords. I would bring back 14 and 4 because 14 has bike and 4 has VAN
ID Keyword
1car
1boat
1van
14test
14car
14bike
4VAN
5 scooter
View 8 Replies
View Related
Dec 27, 2006
Hey
I got a SQL DB with three tables, where two of the collums of the main table gets its data from two other tables, ok? Now, on one page, called "search" the user should be able to search in the DB, and on another page the user should be able to register some keywords, my question is how can the user register several keywords in the same textbox, using perhaps a ; as a separator between the words? I hope you understand what I mean..
Thx!
View 2 Replies
View Related
