Hello,
I have a web application that I need to search based on what the user entered in the input box.
e.g when the user enters in the box something like "Brain Boom"
I need to search the column in the DB table where there is anything word like
Brain or has Boom or all the above. How will I accomplish this?
Thanks
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
Hi - I'm short of SQL experience and hacking my way through creating a simple search feature for a personal project. I would be very grateful if anyone could help me out with writing a stored procedure. Problem: I have two tables with three columns indexed for full-text search. So far I have been able to successfully execute the following query returning matching row ids: dbo.Search_Articles @searchText varchar(150) AS SELECT ArticleID FROM articles WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText) UNION SELECT ArticleID FROM article_pages WHERE CONTAINS(Text, @searchText); RETURN This returns the ArticleID for any articles or article_pages records where there is a text match. I ultimately need the stored procedure to return all columns from the articles table for matches and not just the StoryID. Seems like maybe I should try using some kind of JOIN on the result of the UNION above and the articles table? But I have so far been unable to figure out how to do this as I can't seem to declare a name for the result table of the UNION above. Perhaps there is another more eloquent solution? Thanks! Peter
View 3 Replies View RelatedOur clients want to be able to do full text search with a single letter. (Is the name Newton, Nathan, Nick?, Is the ID N1, N2...). Doing a single character full text search on a table work 25 out of 26 times. The letter that doesn't work is 'n'. the WHERE clause CONTAINS(full_text_field, ' "n*" ') returns all rows, even rows that have no 'n' in them anywhere. Adding a second letter after the "n" works as expected.
Here is an example
create table TestFullTextSearch (
Id int not null,
AllText nvarchar(400)
)
create unique index test_tfts on TestFullTextSearch(Id);
create fulltext catalog ftcat_tfts;
[Code] ....
I 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?
would you use sql server "full text search" feature as your site index? from some reason i can't make index server my site search catalog, and i wonder if the full text is the solution. i think that i wll have to you create new table called some thing like "site text" and i will need to write every text twice- one the the table (let's say "articles table") and one to the text. other wise- there is problems finding the right urlof the text, searching different tables with different columns name and so on...
so i thought create site search table, with the columns:
id, text, url
and to write every thing to this table.
but some how ot look the wrong way, that every forum post, every article, album picture or joke will insert twice to the sqr server...
what do you think?
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.
Example:
Say I want to find "Terminator" (1984).
I want to be able to use "Term" or "ator" as search results and still return the proper record.
Here is my Stored Procedure creation sql:
CREATE PROCEDURE sps_searchTitles(@searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@searchTerm*"')
GO
--- The above does not appear to properly check both prefix ("Term---") and suffix ("---ator") terms.
I am trying to accomplish what is similarly done with LIKE '%term%'.
thanks, YM
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3
FROM tbl1 t1, tbl2 t2
WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
I have Sql server 2005 SP2.
I enabled it for Full Text search. Substring search where i enter *word* doesn't return any row.
I have a table testtable where description has word Extinguisher.
If i run a query with *ting* it doesn't return any row.
select * from testtable where contains(description,'"*xting*"') ;
But it works if i do
select * from testtable where contains(description,'"Exting*"') ;
The Full text search document says it supports substring search.
Is it an issue with sql server 2005?Please help.
I am using Sql Server 2014 Express edition.I have a table with a varchar(max) column. I have created a full text search that use the stoplist "system". column has this struct: xxx.yyy.zzz.... where xxx, yyy, zzz... are numbers, like 123.345.123123.366456...I can have rows like that:
123.345
123.345
123.345.444
123.345.555
123.345.666
123.345.444.777
123.345.444.888
123.345.555.999
I am trying this query:
select * from Mytable where
contains(MyColumn, '123.345.')
I gues the contains would return all the rows with column contains 123.345, but this does not return all the expected rows, only one row.I have tried to replace "." with "-" but the result is the same.I have also tried with '123.345.*. In this case I have got more results, but no all the exptected rows.If I use this query:
select * from MyTable where
MyCOlumn like '123.345.%';
Can anyone tell me how to search an SQL database for a given key word in a textbox? I basically have a database that has a qualifications column and this column needs to be searched for the data given in the textbox. Which is the best method to search for the data? Is it a simple SQL query or an XML based search engine type? Can anyone give any suggestions regarding this? If XML is efficient then how do I use it to query my database, as I'm pretty new in XML based searching.Thanks
View 5 Replies View Relatedhi there,
i am doing a school project and i need to have this search engine that will search the data that i have stored inside the database and display the results out
can anyone help?
thanks
for example:
SELECT * from [table1] WHERE CONTAINS([msgcomment], '"fast" NEAR "performance"')
would always slower than
SELECT * from [table1] WHERE [msgcomment] = '%fast%performance%'
Why? and how can it be solved? can you help me?
:)
My site uses a text box to allow visitors to search products. I'm trying to design the SQL Statement to allow search's on full words, part words, and words/phrases regardless of the order the words are in.
E.g.
megger
meg
mft megger (proper order in the database is megger mft)
mft1710 (using 1710 should find the product)
This is my select statement (classic ASP)
Code:
<%
Dim RSResults__param5
RSResults__param5 = "xxx"
If (Request("searchme") <> "") Then
RSResults__param5 = Request("searchme")
End If
[Code] ....
I'm experiencing an anomaly with my Full text index.
Setup : SQL Server 2005
Indexed Table:
Assets
ID - int (PK, auto increment)
Ref - varchar(50)
[code]....
I have re-built the full-text index, no change.
Hi,
I am trying to create a block of sql statements that will read the sysdatabases table, and store the value of the database in a variable.
I want to use a cursor to step through all these databases (excep of course the control databases).
For each of the databases I want to run a backup log followed by dbcc shrinkfile. For dbcc shrinkfile, I need to change the database context.
What I am finding is that I cannot use the variable to hold the database name.
here is the code
-------------------------
SET NOCOUNT ON
DECLARE @db_name varchar(100)
DECLARE @db_filename varchar(100)
DECLARE list_dbs Cursor
FOR select [name] from master..sysdatabases
OPEN list_dbs
FETCH NEXT FROM list_dbs into @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
select name = @db_filename from @db_name..sysfiles
backup log @db_name with no_log
use @db_name
dbcc shrinkfile (@db_filename, 25)
FETCH NEXT FROM list_dbs into @db_name
END
CLOSE list_dbs
DEALLOCATE list_dbs
-----------------------
I get a syntax error where ever the @db_name is used (on the select and on the use).
Any suggestions?
Thanks,
Jim
Hi,
I need to build up dynamically cursor and fetch variables in a script. I use exec(@sql) to declare the cursor and my hope was I could use the same method for doing the fetch. Unfortunately I run into a
Must declare the scalar variable "@c1".
Here's the part of the code:
... snippet
DECLARE @c1 varchar(max)
DECLARE @c2 char(3)
DECLARE @c3 char(3)
DECLARE @c4 char(3)
DECLARE @c5 char(3)
DECLARE @c6 char(3)
DECLARE @c7 char(3)
DECLARE @c8 char(3)
DECLARE @c9 char(3)
DECLARE @c10 char(3)
DECLARE @c11 char(3)
DECLARE @c12 char(3)
DECLARE @c13 char(3)
DECLARE @c14 char(3)
DECLARE @c15 char(3)
DECLARE @c16 char(3)
DECLARE @c17 char(3)
DECLARE @c18 char(3)
DECLARE @c19 char(3)
DECLARE @c20 char(3)
DECLARE @c21 char(3)
DECLARE @c22 char(3)
DECLARE @c23 char(3)
DECLARE @c24 char(3)
DECLARE @sql VARCHAR(MAX)
DECLARE @insert VARCHAR(MAX)
DECLARE @fetch varchar(max)
SET @sql ='DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET(' +
@apo+@int_provider_name+@apo+ ',' +
@apo+@int_provider_string+@apo + ',' +
@apo+@int_query_string+@apo +
') FOR READ ONLY'
PRINT @sql
EXEC(@SQL)
OPEN c1
set @fetch = 'FETCH c1 INTO '+@int_fetch
print @fetch
EXEC (@fetch)
... snippet
Output:
DECLARE C1 CURSOR FOR SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:work2007schenker.xls','SELECT * FROM [Datei$]') FOR READ ONLY
FETCH c1 INTO @c1,@c2,@c3,@c4,@c5,@c6,@c7,@c8,@c9,@c10,@c11,@c12,@c13,@c14,@c15,@c16,@c17,@c18,@c19,@c20,@c21,@c22,@c23,@c24
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@c1".
How can I declare the dynamically built 'fetch into' variables (@c1 etc.) so they are in the context of dynamic sql ?
Thanks: Peter
Hi,I am having a SQL Server 2005 problem with my Insert statement. I amsending a command via my website to my database. It comes up with anerror I'll put below. The code is here:"INSERT INTO tblUsers (userName) VALUES ( userNameTest)"This is the error I get:The name "userNameTest" is not permitted in this context. Validexpressions are constants, constant expressions, and (in some contexts)variables. Column names are not permitted.Now, userName is a varchar field in the database. What is wrong?Kivak
View 2 Replies View Related
There are some concept about context switch block me .
---executor: dbo
create user u1 without login
go
create table t
(
col int
)
go
create proc proc1
with execute as 'u1'
as
insert into t values(1)
go
exec proc1
I think proc1 can't be run successfully beacuse the executor of proc1 is u1 and
I didn't grant any permission to user u1. So it will return a error message like The INSERT permission was denied on the object 't', database 'tempdb', schema 'dbo'
To my surprise, the "exec p1" command could run successfully..
Why?
I'm currently creating a database that will only allow data operations through stored procedures, ie users will not be able to directly modify tables. I'd like to use an Insert trigger which will run in response to a stored procedure that inserts records. The trigger will check business logic and additionally modify records in a couple of other tables. Given that I've disallowed direct access to the tables, will it run or will the security set-up prohibit that?Thanks in advance for any answers.
View 5 Replies View RelatedI was trying out the Building an End-to-End Application (VB.Net) exercises on Web Matrix Guided Tour and encountered the mentioned errors. Please help. Thanks.
The code is as follows :
<%@ Page Language="VB" Debug="true"%>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Welcome.Text = "Hello, " + User.Identity.Name
If Not Page.IsPostBack Then
' Databind the master grid on the first request only
' (viewstate will restore these values on subsequent postbacks).
MasterGrid.SelectedIndex = 0
BindMasterGrid()
BindDetailGrid()
End If
End Sub
Sub MasterGrid_Select(Sender As Object, E As EventArgs)
BindDetailGrid()
End Sub
Sub MasterGrid_Page(Sender As Object, E As DataGridPageChangedEventArgs)
If MasterGrid.SelectedIndex <> -1 Then
' unset the selection, details view
MasterGrid.SelectedIndex = -1
BindDetailGrid()
End If
MasterGrid.CurrentPageIndex = e.NewPageIndex
BindMasterGrid()
End Sub
Sub BindMasterGrid()
' TODO: Update the ConnectionString and CommandText values for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
Dim CommandText As String = "select OrderID, OrderDate, CustomerName from Orders"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlDataAdapter(CommandText, myConnection)
Dim ds As New DataSet()
myCommand.Fill(ds)
MasterGrid.DataSource = ds
MasterGrid.DataBind()
End Sub
Sub BindDetailGrid()
' get the filter value from the master Grid's DataKeys collection
If MasterGrid.SelectedIndex <> -1 Then
' TODO: update the ConnectionString value for your application
Dim ConnectionString As String = "server='WRPBI'; user id='sa'; password='sa';database=MatrixOrders;Integrated Security=SSPI"
' TODO: update the CommandText value for your application
Dim filterValue As String = CStr(MasterGrid.DataKeys(MasterGrid.SelectedIndex)).Replace("'", "''")
Dim CommandText As String = "select OrderDetailID, ProductName, Quantity, UnitPrice from OrderDetails where OrderID = '" & filterValue & "'"
Dim myConnection As New SqlConnection(ConnectionString)
Dim myCommand As New SqlCommand(CommandText, myConnection)
myConnection.Open()
DetailsGrid.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End If
DetailsGrid.DataBind()
End Sub
</script>
And this is the compilation error :
Cannot generate SSPI context.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot generate SSPI context.
Source Error:
Line 49:
Line 50: Dim ds As New DataSet()
Line 51: myCommand.Fill(ds)
Line 52:
Line 53: MasterGrid.DataSource = ds
Source File: C:WorksWebWISHDefault.aspx Line: 51
Stack Trace:
[SqlException: Cannot generate SSPI context.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +311
System.Data.SqlClient.SqlConnection.Open() +383
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
ASP.Default_aspx.BindMasterGrid() in C:WorksWebWISHDefault.aspx:51
ASP.Default_aspx.Page_Load(Object Sender, EventArgs E) in C:WorksWebWISHDefault.aspx:14
System.Web.UI.Control.OnLoad(EventArgs e) +55
System.Web.UI.Control.LoadRecursive() +27
System.Web.UI.Page.ProcessRequestMain() +731
Hi all,
I have an application that executes the USE command as it's first order of businees with the database. According to the SQL Books On-line, all USE commands will result in the following message being generated:
Error 5701
Severity Level 10
Message Text:
Changed database context to '%.*ls'.
However, my application has never reported this message, execept for at one customer site. I cannot figure out why at this one particular site, the customer is seeing this message each time the application starts. Once they ok the message, everything else with the application is fine, but it is a nuisance for them (gotta love customers!).
Any ideas why this would start happening?
I am writing a trigger to audit changes to certain columns. In the trigger I
would like to record to the "Audit" table as much information about the
current execution context as possible (current user, login, nt user,
inputbuffer, etc.). I couldn`t find the way to find out what is the
currently executed* stored procedure. @@PROCID returns object id for the
trigger itself. Any help would be highly appreciated as this is an urgent
production environment issue.
regards,
Anatol
Hi,
We have a sqlserver 7 on windows nt 4.
We'd just change a startup account for sqlserver agent to a
not domain admin for security reason. The startup account for sqlserver service is
still domain admin. We are now getting the following error every 5 second
in our application event log.
"SQLServeragent security context does not have server-autorestart privileges"
Is there a way to get rid of this error without putting a domain admin as
a startup account for sqlserver agent?
Ted
I have two questions about date formatting in MS SQL 2008.
1. I have column name called DATETIME which have the value like this: 2013-11-12 05:00 what function should I have to use to get only 05:00?
2. I have some rows as below picture:
how can I get the maximum value of "Active PDP Context" and the Time column becomes only 2014-02-03 00:00:00.000?
All of a sudden I cannot connect to sql server from Visual Studio. Nor I can't conenct to Sql server through IIS and it gives me the same error saying "Login failed: Cannot Generate SSPI Context". Lots of my development projects got stuck because of the SSPI Issue. I know there was a network upgrade from Windows NT (users) to Active directory. I can only access the sql server from the host machine.if someone can guide or give me a quick run down on things to look for that would be wonderful.
View 2 Replies View RelatedUm, still trying to transpose MySQL into T-SQL.
Inserting info into a table, where one of the columns meets a certain criteria.
insert into employee (/*emp_id,*/ fname, lname, start_date,
dept_id, title, assigned_branch_id)
values (/*null,*/ 'Michael', 'Smith', '2001-06-22',
(select dept_id from department where name = 'Administration'),
'President',
(select branch_id from branch where name = 'Headquarters'));
But I'm getting this error:
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Any help would be greatly appreciated.
I have a SSIS package developed by a different user which does a lot of DML. This package sits on the server.
The package needs to be executed on a regular basis.
I have given RO access for a regular user on production DB, he is executing the package from his client desktop.
I was expecting this execution fail, since the package is doing lot of INSERTS , where the user has ONLY RO access.
I understand from the above experience that there is an “execution context� for SSIS execution. Can someone tell me how can I define the execution context for SSIS?
------------------------
I think, therefore I am - Rene Descartes
HiMy company uses a Windows 2000 server with MS SQL Server 2000.We have many clients with administrative software that use the MS SQLserver, the problem is that we experimented randomly the error"Cannot Generate SSPI Context" and the only way to work with DB is logout the client.I'm looking the MS Technical sheet but there's no resolving hints thatcan help.Anyone has a similar problem ?Thanks.
View 1 Replies View RelatedWin Server 2003
SQL Server 2000 SP4
SharePoint Server
Win Server 2000 (Domain Controller / Active Drectory)
Clients: Win XP / Access XP
I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.
The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified:
1) date and time
2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2))
3) Not using cached credentials
we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.
Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?
Hi There
I am trying to loop through databases to gather information.
However if i loop though datbases in a cursor, by using a select from sysdatabases, once i am in the cursor and SET @Sql = 'use ' + DBName and EXEC(SQL), the database context never changes from the one the cursor is executing in.
Is there a way to loop through database contexts and execute sql ?
Thanx