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
Where can I find the list of new keywords for sql 7.0? Namely the keyword to allow duplicates when creating a table?
Thanks!
does anyone know whether the words 'comments' and 'narrative' have become reserved keywords post-SQL2000 ?
View 2 Replies View RelatedHow 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
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!
I am using db keyowrds as attributes in my table such as from, to and date. These are enclosed in [] in SQL Server Enterprise Manager. I'm just asking if doing this is a bad idea? Reason being these are the most applicable names for these attributes but don't want to run into problems further down the line.
Thanks,
_z0
I WANT TO FIND THAT WORD WHICH IS START'S FROM % KEYWORD
SELECT ENAME FROM TABLENAME WHERE ENAME LIKE '%%'
HOW CAN I FIND PLS HELP ME OUT .
Yaman
Hi,
I'm trying to do a query to do a search by keywords. For example if the user searches for "Hottest restaurants in Atlanta" i want to write a query that searches that exact phrase first in a database field called keywords and if it shows up there then that record should be displayed first.. and then after do a search for EACH one of the words in a number of fields in the database and see if it shows up.
here is what i have so far but it always returns the same thing. any ideas?
$searchArray=explode(" ",$search);
$query = "SELECT ID,name,description from restaurants WHERE keywords = '$search' OR (";
foreach($searchArray as $key){
$query = $query . " (name LIKE '%$key%' OR subType LIKE '%$key%' OR features LIKE '%$key%' OR neighborhood LIKE '%$key%' OR region LIKE '%$key%') OR";
}
$query = substr($query,0,strlen($query)-3);
$query = $query . ") order by keywords";
any ideas?
SQL server throws an exception when I try to run the following code:
ALTER TABLE contacts ADD default ntext NULL;
The reason for that is that "default" is an SQL keyword.
The way I handle this now is I put an underscore at the front:
ALTER TABLE contacts ADD _default ntext NULL
Is there a cleaner way to handle this, i.e. to keep the column name "default" and forse sql server to create a column with this name?
Thank you!
I have a problem where records in underlying tables of a dataview are being deleted (seemingly at random)
For example.
CREATE TABLE [Employee] (Id int, Name varchar(50))
CREATE TABLE [Company] (Id int, Name varchar(50))
CREATE TABLE [EmployeeCompany] (CompanyId int, EmployeeId int)
CREATE VIEW [dvEmployee]
AS
SELECT *
FROM [Employee] INNER JOIN [EmployeeCompany]
ON [Employee].[Id] = [EmployeeCompany].[EmployeeId]
CREATE TRIGGER [dvEmployeeUpdate]
ON [dbo].[dvEmployee]
INSTEAD OF UPDATE
AS
BEGIN
UPDATE EmployeeCompany
SET Status = INSERTED.Status
FROM EmployeeCompany, INSERTED
WHERE EmployeeCompany.CompanyId = INSERTED.CompanyId
AND EmployeeCompany.EmployeeId = INSERTED.EmployeeId
END
Because the column [Status] is a t-sql keyword, does the fact that the trigger contains the line "SET Status = ..." without saying "SET [Status] = ..." mean that I could lose records in the EmployeeCompany table?
Reason I'm asking is we have an already designed database that is littered with columns named the same as sql keywords (almost every table has a [Status] column, and there are many [Password] columns). When using a dataview on these tables, triggers exist that aren't putting the [] around these column names (the same as my dvEmployeeUpdate trigger above), and somehow we are seemingly randomly losing records. It is very rare, and they are getting completely deleted, and it seems to be the tables that contain the keyword columns and are used in dataviews with instead of triggers that don't put [] around the column names. Nowhere in any trigger or stored procedure is there a DELETE FROM on these tables, and the software running on the database uses only the data views, and doesn't directly access the underlying tables.
I've been going through all of the code adding the [], but my question is simply whether or not anyone has heard of this causing the deletion of any records, or whether there may be something else going on that I should be looking into?
Destination table has a column name - Partition that is a key word in the DB2 database. But database would execute the query fine if there is "" around the column name - "Partition". When an OLE DB Destination component is used, it throws an error saying column name is a reserved word and that it cannot be used. When put "" around the column name, it fails in the pre-execute phase thinking that "Partition" is the column name instead of Partition. Any ideas to fix this problem would be greatly appreciated?
View 3 Replies View RelatedHi
I have a string EX: "How are you doing" Now. I wanted this string to be splitted in to respective words and return me those words in sql server. .
Thanks!
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.
I using multiple keywords to search and filter my data by using Stored Proc and retrieve the results in GridViewStored Proc ========ALTER PROCEDURE dbo.search
(
@eName nvarchar(30), @ID nvarchar(7), @cID nvarchar(10), @eCC nvarchar(3)
)
AS
SELECT eName, ID, cID, eCC
FROM iTrns
WHERE (ID LIKE '%' + @ID + '%') and
(eName LIKE '%' + @eName + '%') and
(cID LIKE '%' + @cID + '%') and
(eCC = @eCC)
========
My code behind========protected void Button1_Click(object sender, EventArgs e)
{
GridView1.DataSource = GetTable();
GridView1.DataBind();
}public DataSet GetTable()
{SqlConnection SqlCon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["***"].ConnectionString);
SqlDataAdapter Adptr = new SqlDataAdapter();SqlCommand cmd = new SqlCommand("search", SqlCon);
cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddWithValue("@ID", TextBox2.Text);
cmd.Parameters.AddWithValue("@eName", TextBox1.Text);cmd.Parameters.AddWithValue("@cID", TextBox3.Text);
cmd.Parameters.AddWithValue("@eCC", TextBox4.Text);
Adptr.SelectCommand = cmd;
DataSet ds = new DataSet();return ds;
SqlCon.Close();
}
========
Sometimes it retrieves the whole data !! and sometimes retrieve nothing !!
Any hint ??
Thank you
--keywords table:
CREATE TABLE #KeyWord
(
[QueryId] int IDENTITY (1, 1) NOT NULL ,
[QueryMode] int NOT NULL , --keyword query mode: 1=match(OR), 0=not match(NOT), 2=must match(AND)
[SKey] nvarchar (200) NULL
)
ON [PRIMARY]
--target table:
CREATE TABLE Test
(
[PId] int IDENTITY (1, 1) NOT NULL ,
[PTitle] nvarchar (200) NULL
)
ON [PRIMARY]
================
I want to match PTitle column in table(Test) with 3 type of query-mode keys, and return all matched records, how should i do the "select..." query in store procedure?
PS. sqlserver's full text index mode is off.
Hello everyone,
I am looking for a store procedure which validates certain keywords like delete,truncate,update,insert etc. and restricts them to be used by users in all of my store procs which takes strings as inputs.
Thanks.
Hi,
I'm trying to move an asp site from an Access DB to SQL Server.
The site features a search system which allows multiple keywords to be used in up to three fields.
The way I used to manage this was very similar to how it is described below (taken from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?lngWId=4&txtCodeId=6701):
"So you're replacing all of the middle spaces with a SQL 'and' statement. In plain English, if your search phrase is "print bug", this now becomes "'%print%' AND field LIKE '%bug%'" when you concatenate the leading and trailing %'s and quotes (this is for Microsoft Access drivers, other drivers may use different wildcards)--so just append this phrase to the "WHERE field LIKE " phrase, and you're in business."
The trouble I'm having is that the search string is sitting in a Stored Procedure. How can I dynamically append to the search string in this case?
If it isn't possible, how can I go about this?
Cheers all.
__________________________________________________ ___
Code:
ASP:
item=request(item)
'****item = Replace(item, " ","%' AND item like '%")**** - needs attention
rs.Open "Exec getlist "& item &"" objConn, 3
SP:
CREATE PROCEDURE getlist
@item nvarchar(255)
SELECT item FROM publications WHERE item LIKE '%' + @item + '%'
GO
I am always changing the Sql Query Keywords to UpperCase and formatting the T-sql.
Is there a option so that i can change the keywords to uppercase automatically in the sql 2005 management studio ?
How do most of you format the sql statements?
Please let me know it will save me and most of us like me alot of time.
Your help will be most appreciated.
Thanks in Advance
Savvy
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 View RelatedSQL Code:
Original
- SQL Code
SELECT acct.USERNAME,
SUM(trans.CHARGES) - SUM(trans.CREDITS) AS [Charges - Credits],
MAX(trans.ENDPERIOD) AS [Billed Through],
acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY,
bill.firstname, bill.lastname, bill.STREET1, bill.STREET2,
bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY,
acct.PHONE1, acct.PHONE2, bill.EMAIL,
acct.BILLPERIOD, acct.PLAN
FROM TRANS trans, ACCTS acct, BILLING bill
WHERE trans.ACCTNUM = acct.ACCTNUM
and bill.ACCTNUM = acct.ACCTNUM
and bill.ACCTNUM = trans.ACCTNUM
AND acct.CLOSED = 0
AND acct.SUSPENDED = 0
GROUP BY acct.USERNAME, acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY,
bill.firstname, bill.lastname, bill.STREET1, bill.STREET2,
bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY,
acct.PHONE1, acct.PHONE2, bill.EMAIL,
acct.BILLPERIOD, acct.PLAN
HAVING SUM(trans.CHARGES) - SUM(CREDITS) > 0
ORDER BY [Billed Through] DESC
SELECT acct.USERNAME, SUM(trans.CHARGES) - SUM(trans.CREDITS) AS [Charges - Credits], MAX(trans.ENDPERIOD) AS [Billed Through], acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLANFROM TRANS trans, ACCTS acct, BILLING billWHERE trans.ACCTNUM = acct.ACCTNUM AND bill.ACCTNUM = acct.ACCTNUM AND bill.ACCTNUM = trans.ACCTNUM AND acct.CLOSED = 0 AND acct.SUSPENDED = 0GROUP BY acct.USERNAME, acct.FULLNAME, bill.COMPANY, bill.BILLTOCOMPANY, bill.firstname, bill.lastname, bill.STREET1, bill.STREET2, bill.CITY, bill.STATE, bill.ZIPCODE, bill.COUNTRY, acct.PHONE1, acct.PHONE2, bill.EMAIL, acct.BILLPERIOD, acct.PLANHAVING SUM(trans.CHARGES) - SUM(CREDITS) > 0ORDER BY [Billed Through] DESC
Incorrect syntax near the keyword 'PLAN'.
If i take out SELECT & GROUP BY acct.plan, it works fine.
I've googled a bit and found 'EXPLAIN PLAN' command, I assume it's parsing the 'PLAN' as a command and screwing stuff up. I don't get why it'd take it for a command instead of a column. How does one select a keyword as a column name? Brackets & single quotes didn't do the trick.
Hi,
I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried.
An example:
User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be
SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%')
As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures?
Kind regards,
ThaYoung1!
I use SQL Server 2005 Developer. I failed to search the records by using SQLCommand Paramater. Please find the code below.
However, when I hardcode like LIKE '%sudoku%' , it works. Could aynone help?
Thanks,
Andy.private bool searchProducts(string keywords)
{
.......
command.CommandText = "SELECT Product.ProductID, Product.Name, Product.Image, ProductPrice.Price, ISNULL(SpecialProduct.PromoDiscount,0) as PromoDiscount FROM Product INNER JOIN ProductPrice ON Product.ProductID = ProductPrice.ProductID LEFT OUTER JOIN SpecialProduct ON Product.ProductID = SpecialProduct.ProductID WHERE Product.Name LIKE '%@keywords%' ";
command.Parameters.Add("@keywords", SqlDbType.VarChar, 100);
command.Parameters["@keywords"].Value = keywords;
.............
}
Hi All,
i have some views in my database, and these views are having some columns,i want to know particular column name to be there in particular view.
For example ,just like functionality of sp_search_code 'Keyword'.
like this i want to search in views .
or else please let me know sp_search_code 'Keyword'. was used for views also.
Thanks and Regards,
G.JaganMohanrao.
Hello,
I am greener in using SQL Server 7.0. I created a stored procedure named usp_Test (please see the following codes for reference). It worked fine when passed through a single keyword into it (for example usp_Test 'Satellite'), but it did not return the query results what I wanted when passed through multiple keywords into it (for example usp_Test 'Satellite Remote'). In fact, it returned nothing but the column names specified in SELECT statement. Below is the sample of procedure how it looks like. Where am I wrong? Any idea?
CREATE PROCEDURE usp_Test
@strKeywords varchar(50)
AS
DECLARE @DelimiterPos integer
DECLARE @Delimiter char(1)
DECLARE @strWHERE varchar(1000)
SET @DelimiterPos = 0
SET @Delimiter = CHAR(32)
SET @strWHERE =""
SET @strKeywords = RTRIM(LTRIM(@strKeywords))
SET @DelimiterPos = CHARINDEX(@Delimiter,@strKeywords,1)
IF @DelimiterPos > 0
BEGIN
WHILE (@DelimiterPos > 0)
BEGIN
IF LEN(@strWHERE) > 0
BEGIN
SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + LEFT(@strKeywords,@DelimiterPos - 1)+ "%" + CHAR(13)
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + "%" + LEFT(@strKeywords,@DelimiterPos - 1) + "%" + CHAR(13)
END
SET @strKeywords = LTRIM(RIGHT(@strKeywords, LEN(@strKeywords) - @DelimiterPos))
SET @DelimiterPos = CHARINDEX(@Delimiter, @strKeywords, 1)
IF @DelimiterPos > 0
BEGIN
CONTINUE
END
ELSE
IF LEN(@strKeywords) > 0
BEGIN
SET @strWHERE = @strWHERE + " AND tblClips.Title Like %" + @strKeywords + "%" + CHAR(13)
BREAK
END
END
END
ELSE
BEGIN
SET @strWHERE = @strWHERE + "%" + @strKeywords + "%"
END
PRINT 'WHERE tblClips.Title Like ' + @strWHERE
SELECT tblClips.Title,tblTapes.ClassificationNo,tblTapes. Inventory
FROM tblClips INNER JOIN (tblClipTape
INNER JOIN tblTapes
ON tblClipTape.fkTapeID = tblTapes.TapeID)
ON tblClips.ClipID = tblClipTape.fkClipID
WHERE tblClips.Title LIKE @strWHERE
Thank you very much in advance.
Changbaoi
Hi, I am novice to Full-Text funcationality.
The problem I met is that SQL server seems not woring with too short keywords (with FREETEXT syntax). The following is the detailed description.
I index two columns, JobDes(ntext) and JobTitle(nvarchar), which mainly store Traditional Chinese(Big5). If I search with
select * from JobWanted where FREETEXT(JobTitle,'asp'), it goes well. But if I replace the 'asp' with 'as', nothing returns. What is the reason? Can I eliminate this limination?
Any help would be appreciated.
Ricky.
I'm trying to create a query that searches an entire database for keywords inside of the columns for each table within the database. For instance my tables have 2 columns one named ID and the other Permission, I'd like it be able to return all the lines that are associated with that keyword. So if I search "Schedule" it returns all the lines containing that word in it within that database.
View 6 Replies View RelatedI have a scenario of where the standard Full-Text search identifies keywords but Semantic Search does not recognize them as keywords. I'm hoping to understand why Semantic Search might not recognize them. The context this is being used in medical terminology and the specific key words I noticed missing right off the bat were medications.
For instance, if I put the following string into a FT indexed table
'J9355 - Trastuzumab (Herceptin)'
AND
'J9355 - Trastuzumab emtansine'
The Semantic Search recognized 'Herceptin' and 'Emtansine' but not 'Trastuzumab'
Nor in
'J8999 - Everolimus (Afinitor)'
It did not recognize 'Afinitor' as a keyword.
In all cases the Base of Full-Text did find those keywords and were identifiable using the dmvsys.dm_fts_index_keywords_by_document.It does show the index as having completed.
why certain words might not be picked up while others would be? Could it be a language/dictionary issue? I am using English and accent insensitive settings?
This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.
My tables:
Article table: [ArticleID, Headline, Content]
Filter table: [FilterID, Name]
Keyword table: [KeywordID, Word]
FiltersKeywords table: [FilterID, KeywordID]
(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)
In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.
Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?
Thanks, Egil.