Quoted Literal Strings Won't Force A Phrase Match

Jan 18, 2008

Hello all,

From what I've read, SQL Server is supposed to do a phrase match when you do a full text search that contains quoted literals. So, for example, if I did a full text search on the phrase "time out" and I put it in quotes, it's supposed to search for the full phrase "time out" and not just look for rows that contain the words "time" or "out." However, this isn't working for me.

Here is the query that I'm using :

SELECT *
FROM Content_Items ci
INNER JOIN FREETEXTTABLE(Content_Items, hed, '"time out"') AS ft ON ci.contentItemId = ft.[KEY]
ORDER BY ft.RANK DESC

What's it's doing is this : it's returning a bunch of rows that have the words "time" or "out" in the column called hed. It's also returning rows that have the full phrase "time out", but it's giving those rows the same rank as rows that only contain the word "time." In this case, that rank is 180.

Is there anything else I should be doing in my query, or is there some configuration option I should have turned on?

Thanks.

View 1 Replies


ADVERTISEMENT

SQL Server 2000 + Inserting Quoted Strings

Jul 27, 2006

Hello,

I am trying to insert quoted strings into a database table, however, I cannot remember how to do so. For instance, I am trying to insert the following values into a SQL table:

My Friend's
"Happy Birthday"

exactly as they are listed. How can I do that in a SQL insert statement?

Thanks,
Crystal

View 1 Replies View Related

Fuzzy Phrase Matching

Oct 3, 2007

A column in my database contains phrases such as "Extreme Golf: The Showdown" or "Welcome to Happy Land". I need to write a search engine so that users could type in phrases such as "Golf Extreme Showdown" or "Happy Land" and the correct, or closest matched results will be returned. I don't need variations of words, just phrase keyword match based search. I know I could do this by using multiple LIKE %% statements OR'd together, but this would be too performance intensive. So, I have heard I should use charindex somehow to achieve this in a stored procedeure. Does anyone have any clue how to solve this problem? Thanks!

View 7 Replies View Related

Search For A Phrase In Sprocs

Jul 10, 2007

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 11 Replies View Related

Select Text From Phrase

Apr 21, 2008

I have a repository with about 10,000 records. These records are selectable by searching by author surname, this works fine, but. the powers that be would like the data to be searchable by phrase. The db lives on a commercial host where I'm unable to create full text search catalogues.

I'd like to be able to return all results that contain both terms, or either if someone types in (for example) Breast Cancer. The search will only select from the the one field which will contain complex phrases such as "Second-line treatment of postmenopausal women with advanced breast carcinoma"

Any help on creating a select query that will do this will be greatly appreciated...

View 1 Replies View Related

Pass Phrase Question

Feb 9, 2008

I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.

I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?

I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.

Any thoughts are appreciated.

Thank you

View 1 Replies View Related

Pass Phrase Encryption Question

Feb 9, 2008

I have a database that will be used by two or more organizations. I would like to use pass phrase encryption to encrypt a couple of columns.
I'm looking for suggestions on how I might set up the db to let the organization change the pass phrase that is used for their encryption?
I don't really want to hard code it into stored procedures or select statements with parameters. I will be using SSL if that should make a difference with what you suggest.
Any thoughts are appreciated.

View 1 Replies View Related

Command Contains Unrecognized Phrase / Keyword - VFP

Oct 26, 2013

Due to limited number of characters..

no query cant be read by visual fox pro..due to error 36..

foxpro via mysql...here is the query..

SQLExec(nPayroll,"insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,lastname,firstname,middlename,ext_name,no_eli_for_educ_grant)
select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id,m1.last_name,m1.first_name,m1.middle_name,m1.ext_name ,m2.count

[Code] ....

I did something like this but the query is not working anymore.. is there something wrong?? here is the query..

Query = ["insert into py_abcdeeh(province,municipality,barangay,household_id,entry_id,lastname,firstname,middlename,ext_name,no_eli_for_educ_grant)];
+[select m1.province,m1.municipality,m1.barangay,m1.household_id,m1.entry_id,m1.last_name,m1.first_name];
+[,m1.middle_name,m1.ext_name ,m2.count];

[Code] ....

VFP9.0 via MySQL 5.0

View 1 Replies View Related

Concatenate Strings After Assigning Text In Place Of Bit Strings

Feb 19, 2007

I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.

I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.

This is the basic logic goes soemthing like this:


select case new_accountant = 1 then 'acct/' end +

case new_advisor = 1 then 'adv/' end +

case new_attorney = 1 then 'atty/' end as String

from new_database

The output would be

Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/

So far, nothing I have tried has worked.

Any ideas?

View 2 Replies View Related

Quoted Identifier

May 14, 2003

During Index Rebuild, I get the following error:

"DBCC failed because the following set options have incorrect settings: 'Quoted_Identifier'"

Any ideas what might be causing this?

Thanks,
Ken Nicholson

View 8 Replies View Related

Quoted Identifiers In A CSV

Sep 26, 2006

There have been some posts on the difficulties of handling csv with quoted identifers.

I heard there were some custom components that aid in dealing with these as input files.

IYO, what do you consider to be the best way to deal with csv with quoted identifiers as an input to an ETL operation?

Also, if you know of any bloggers making quality (hopefully free) custom components?

Thanks!!

View 1 Replies View Related

Keyword Seach (Not Full Phrase) Parameterise Sql Statement

Apr 11, 2007

hi,
i want to do search by keywords for e.g "John Smith". should search for "John" and "Smith"
it is easy to do it using dynamic sql statement.
but i am using parameters sql.
this is my sql
"select * from emp_tbl where fname like '%' + @keyw + '%' or lname like '%' + @keyw + '%' " 
the above sql will search by full phrase
how can i make it search each word in the phrase.
 
aslo, i am searching for 70-551 exam. to upgrade my mcad to mcts.
can anybody help. 
 

View 9 Replies View Related

SQL 2012 :: Create A Script To Search For Missing Word Or Phrase

Jan 16, 2015

We compulsorily use NOLOCK in our scripts on reporting DB and development generally forget putting NOLOCKS or schema names with tables.

Creating a script to search for a missing word or a phrase in SQL Server script.

Our format is
<schema>.<table> <alias here> WITH NOLOCK <or alias here>
or
<schema>.<table> <alias here> NOLOCK <or alias here>
or
<schema>.<table> <alias here> (NOLOCK) <or alias here>

but generally developers write only <table> <alias>

View 4 Replies View Related

Quoted Identified And Odbc Connection

Oct 2, 2006

We have several sp's created to support a 3rd pary app that uses crystal inside the app with an odbc connection. We got sql row errors on any Citrix server where Use ANSI quoted identifiers was not checked. If we had them checked it works. Does this mean the sp's were created with the identifiers on and this is causing the issue?

View 1 Replies View Related

BCP Template Using Quoted Text Qualifiers

Nov 2, 2005

I'm dumping data from a table via BCP and when BCPing them back in to another table, it errors out on numeric and date fields. I'd like to place quote marks on the text fields. How do I do this using BCP?

View 4 Replies View Related

DTS 2005 And Quoted Table Names

Jul 23, 2005

We're trying to use DTS from SQL Server 2005 beta 2 to query datafrom an Informix IDS server via OLEDB.Unfortunately DTS is building queries of the form:select * from "database":"owner"."tabname"and the quoted table name is being rejected by the Informix server as asyntax error.Is there a way to keep DTS from quoting the table name in queries? Or isthis happening within the OLEDB provider?Thanks.--John HardinDevelopment and Technology group (Seattle)CRS Retail Systems, Inc.

View 1 Replies View Related

Ansi Nulls And Quoted Identified

Aug 8, 2005

Hello everyone,SQL Server 2000. I have a database in which there are several objectswhich have ansi nuls and quoted identifier turned ON. Is there a way Ican generate a script which:(1) Can identify all objects within the database that have those twoproperties turned ON and(2) Change the properties for these objects and turn the ansi nulls andquoted identifier OFF for those objects.I am trying to avoid going throuh gazillions of objects and manuallydoing this.Thanks for any help.Raziq.*** Sent via Developersdex http://www.developersdex.com ***

View 17 Replies View Related

Literal Value With IN Clause

Apr 12, 2006

Howdy,Is it okay to use a literal value with the IN clause. E.g.SELECT somefield, anotherfield.....WHERE ...etc.AND 1234 IN (SELECT userid FROM tblUsers)I was told it wasn't valid, but I'm pretty sure it worked for me. Justseeking clarification.cheers,

View 2 Replies View Related

Problem With Quoted Identifier On 2000 Sql Server

Jan 16, 2003

Hi,

If you run the following code on 7.0 server, it will send a test file in csv format.

--*********************************
create table ##temp1
(tmp varchar(15))

insert into ##temp1
select 'Erin, Brockowich'

declare @cmd varchar(255)

select @cmd = 'select * from ##temp1'

EXEC master..xp_sendmail
@recipients = '<email>', --change to your email
@query = @cmd,
@subject = 'test-test',
@attach_results = 'true',
@attachments = 'test.csv',
@width = 800

drop table ##temp1
--**********************************

When you receive an email and double click on te attachment, it will launch excel automatically and put 'Erin' and 'Brockowich' in seperate columns. which is good and that is the way I want it.

But if I run this code on sql 2000 server, it will generate a file with 'csv' extension unrecognizable by excel. If you open this 2000 attachment in notepad, you can see that the data looks like
"Erin, Brockowich" (vs Erin, Brockowich without quotes on 7.0 server ), no wonder it is unrecognizable by excel.

I have set quoted_identifier off while compiling all the user sps on 2000 server. But while sending emails from within the procedure, all the attachments still generated with quotes.

How can I get rid of the quotes? We have at least 45 routines running generating coma seperated files as a result of the query and sending emails to the clients for years. Now all of a sudden all the routines got messed up with 2000 upgrade.

Can you suggest any work around?

Thanks,
Shaili
:confused:

View 1 Replies View Related

Stored Procedure Problem Using Un Quoted Varchar

Aug 16, 2007

I have a stored procedure which returns a count of products and a limited number of rows from a query.

I am using SQL Server 2005 and calling the procedure in asp.net

The procedure is as follows





Code Snippet

GO
ALTER PROCEDURE [dbo].[GetProductsByCategoryId]
@Category VARCHAR(255),
@Range INT,
@PageIndex INT,
@NumRows INT,
@CategoryName nvarchar(255) OUTPUT,
@CategoryProductCount INT OUTPUT
AS

BEGIN

/*
Get product count
*/
SELECT @CategoryProductCount=(SELECT COUNT(*) FROM Products LEFT JOIN tblVar on Products.ProductID = tblVar.prodidvar WHERE Products.Category=@Category AND Products.Range=@Range)

/*set row variables*/
Declare @startRowIndex INT;
set @startRowIndex =(@PageIndex * @NumRows) + 1;

/* get full list of products */
With ProductEntries as (
SELECT ROW_NUMBER() OVER (ORDER BY Products.ProductID, tblVar.idvar ASC) as Row, field1, field2
FROM Products LEFT JOIN tblVar on Products.ProductID=tblVar.prodidvar
WHERE Range=@Range
AND Category = @Category
)



/*get only needed rows */
SELECT field1, field2
FROM ProductEntries
WHERE Row Between
@startRowIndex and @startRowIndex+@NumRows-1


END

The problem seems to be with the line

AND Category = @ Category
in the query to make the ProductEntries

If I take this query and run it in an SQL pane I need to enclose the argument for @Category in single quotes.
If I try to do this in the procedure it simply searchs for @Category as a string rather than the value of @Category.

The query returns and displays results with no problems without this line, and also if it is returning a result set that has no values in tblVar to join to.

Also if I run the query on just the Products table removing the left join it will return results with no problems.


Thanks to anyone who can help!

And I apologise if it is something simple but asp and SQL Server is not my usual coding platform.

View 7 Replies View Related

Replication Snapshot Failing - QUOTED IDENTIFIER

Sep 9, 2007

Hi,

My replication is failing to apply the initial snapshot because of the issue with QUOTED identifier. The snapshot files for the stored procedure are being generated using the 'SET QUOTED IDENTIFIER ON' where as my store procedure code is using the double quotes for string comparision e.g if @val = "test".

It is not possible for me to change the sps code as there are 1000+ sps exists. Is there any way to generate the snapshot files with 'SET QUOTED IDENTIFIER OFF'

View 1 Replies View Related

Quoted Field And Escape Character Issue

Nov 8, 2006

I am attempting to import a flat file and have come accross and issue that I do not know how to fix in SSIS. The issue is that some of the text fields use quoted identifiers. This is not an issue in itself. The problem is they also use quotes as escape character if quotes are on the field.

So I see instances of "" because inside the quoted field is a quote. How do i specify an escape character?

View 1 Replies View Related

'Go' Keyword Being Interpreted In Literal

Apr 1, 2008

Hi,
A really basic problem in psuedo code...

select * from mytable where email = 'test@go.com'


This throws back:

Unclosed quotation mark before the character string 'test@'.
Line 1: Incorrect syntax near 'test@'.

Unclosed quotation mark before the character string ''.

Because of the 'go'. Other addresses featuring 'go' suffer the same problem. How do I escape it?


Thanks,
Joe

View 4 Replies View Related

How To Use A Parameter In Place Of A Literal?

Oct 5, 2007

I have a stored procedure that starts like this:

.
.
.
UPDATE Employees
set depth=0, hierarchy=NULL

UPDATE Employees
set depth=1, hierarchy=right(@MaxPadLength + CAST(Employees.Parent AS varchar(255)),@DisplayPadLength)
where Child = Parent

WHILE EXISTS
(
SELECT *
FROM Employees
WHERE Depth=0
)
.
.
.
I have many tables that have the same structure as the Employees table but have different names. I would like to pass the PS a paramater with the table name I want to process. My question is what is the correct syntax to use a parameter in place of the literals for the table name?

Thanks

View 4 Replies View Related

Get Literal Month From Date

Oct 25, 2007

I am trying to extract the month from a date field, and I am able to get the integer for the month, however, when I try to convert the integer to the literal, e.g. 3 to March, I am having issues.

I have a field called PROCES, which represents a date processed, which is numeric(8).... i.e. 20061231.

I am using:
SELECT datepart(mm,cast(convert(char(8),PROCES)as datetime))
which gives me the correct integer.

When I try:
SELECT datename(mm,datepart(mm,cast(convert(char(8),PROCES)as datetime)))

I get January for all records...?

Does anyone know why, and how to fix.

Thanks

View 1 Replies View Related

Select * From Something Where Field1 = N'literal'

Jul 23, 2005

What exactly is happening when a query is sent using the N in front ofthe string to be found?Under what conditions would someone use the N' in a query?I have been testing out some chinese text. I set up some fields ofnVarchar, nText and it works with an N. Without the N, it wont work.N also works with fields of varchar and text for english.Would this ever cause a problem to a query depending on how themachines regional settings are set? Why not just put N in all of thequeryies?If anyone has some ideas, I would be grateful for any and allinformation about the N.

View 1 Replies View Related

Too Many Characters In Character Literal?

Feb 24, 2008

Hello,

I am trying to insert some values retrieved from textboxes into an Access DB using ASP.NET. When I try to run this code I get an error reading "Too many characters in character literal". What does this mean? Also, how do I break new lines, does way this look ok?



string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;

string sQuery2 = INSERT INTO Member(FirstName, LastName, StreetAdress, PostalAdress, Telephone, Email),

VALUES ('strFirstName', 'strLastName', 'strStreetAddress', 'strPostalAddress', 'strTelephone', 'strEmail');




OleDbConnection oOleDbConnection = new OleDbConnection(connectionString);

oOleDbConnection.Open();

OleDbCommand command2 = new OleDbCommand(sQuery2, oOleDbConnection);

OleDbDataReader reader2 = command2.ExecuteNonQuery();

View 8 Replies View Related

String Literal Problem

Aug 8, 2006

hi!

din't work. I need some thing in C# that can execute like this:

This works in SQl Query perfectly.

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx"/Set package.Variables[User::connectst].Properties[Value];""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""'

How do we interpret in c# currently i have some thing like this which is not correct i need to fix this to work in C#

string conn = @"""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""""";

path = @"""D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx";

jobCommand = new SqlCommand(@"xp_cmdshell 'dtexec /f " + path + "" /Set \package.Variables[User::connectst].Properties[Value]; " + conn + ""'", cconn);



Thanks,

Ja

View 5 Replies View Related

String Character Literal In C#

Aug 7, 2006

when i execute in sql this works fine:

xp_cmdshell 'dtexec /f "D:SSISProjectIntegration Services Project1ArchiveMainMultiTables.dtsx" /Conn TahoeDB;"Provider=SQLNCLI.1;Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"'

but when i execute in C# i get this value

string connect = @"TahoeDB;""Data Source=SE413695AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;""";

xp_cmdshell 'dtexec /f "D:\SSISProject\Integration Services Project1\ArchiveMainMultiTables.dtsx" /Conn "TahoeDB;"Data Source=SE413695\AASQL2005;Initial Catalog=TestDB;Provider=SQLNCLI.1;Integrated Security=SSPI;"" ' -- this thorughs up error in sql

Option "Source=SE413695\AASQL2005;Initial" is not valid.This is basically after Data there is a space till Initial and then space catalog.

what should i do ? any help

thanks,

Jas

View 3 Replies View Related

ANSII NULL AND QUOTED IDENTIFERS - Upgrade Problems

Oct 26, 2007

I inherited a system that was started in Access and moved to SQL 2000.  The business has grown and we are trying to replace our older systems with ASP.NET and Server 2005.  Currently, we are trying to make a new asp.net page for searching the database for records with matching dates or date ranges. There are several types of dates to search, so they are all optional.  Set to default as null in the proc.  For each date there is an operator field, such as equal or greater, etc.  The proc only looks at the date if the operator is set to EQ" or "IN" and ignores the date if operator set to "NO"
The proc works fine when running under Management Studio, but fails coming through a SQLDataSource to a  gridview. It works with integer and string filters, but fails when entering the same date ('07/20/2007')  that works in the testing tool.  All dates are actually stored as datetime, and  they are set as DateTime Control Parameters in the SQLDataSource.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:someConnectionString %>"
SelectCommand="spTESTSearch" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter DefaultValue="M" Name="TypeCode" Type="String" />
<asp:Parameter DefaultValue="EQ" Name="FirstPubOp" Type="String" />
<asp:ControlParameter ControlID="FirstPubDateTextBox" DefaultValue="" Name="FirstPubDate"
PropertyName="Text" Type="DateTime" />
<asp:Parameter DefaultValue="C" Name="UserName" Type="String" />
<asp:Parameter DefaultValue="NO" Name="SearchTextOp" Type="String" />
<asp:Parameter Name="SearchText" Type="String" />
</SelectParameters></asp:SqlDataSource>
 The dates are selected properly in the testing tool, with code such as :
DateDiff(day, FirstPubDate, @FirstPubDate) = 0
I think my problem is based on option settings for the databases themselves.  The old database was set to Ansii Nulls and Quoted Identiers to OFF, and the new ones were defaulted to them being ON.  I noticed that the tool also, sets those options on when creating new stored procedures.
Would this difference be causing the dates to be quoted and viewed as objects rather than strings?  What are the dangers in changing those options on the database that still gets uploads from the old SQL 2000 database and some Mac-based systems?
I welcome any suggestions on how to get my new stuff running while not breaking my old production systems.
 Thanks for the assist!
 

View 1 Replies View Related

How To Convert Literal String To Unicode ?...

Aug 6, 2007

Hi,
I want to convert literal string to unicode before insert into the database. and after insertion i want to retrive this value from data base and convert back to literal string.
pls tell me how to incode and decode literal string to unicode and from unicode to literal string.

View 2 Replies View Related

Find Literal Underscore - Not Wildcard

Mar 21, 2005

In MS Sql 2000 - is it possible to find the literal underscore character (_). Something like:

SELECT * FROM foo WHERE bar like 'a\_%'

where the query would return "a_abc", "a_cba", and "a_lksdfjlkdsjlksjdfl", but not "abc"?

I've tried:
SELECT * FROM foo WHERE bar like 'a\_%'
SELECT * FROM foo WHERE bar like ( 'a' + CHAR(95) + '%' )

Any other suggestions?

View 1 Replies View Related

Excel Export Footer Is A Literal?

May 3, 2007

When a report is exported to Excel, the footer is set to a literal "page 1 of 1" even though it's specified as



="Page " + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages)



in the rldc report definition.

Hence, if you print from Excel every page has the 'page 1 of 1' footer. Not very useful.

Is there a way to either suppress the output of the footer (only when going to Excel cause you need it when exporting to PDF) -or- get it to generate the correct excel footer of 'page 1 of ?'????

Thanks,

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved