Openquery() Search With NOT LIKE
May 20, 2008
Hi All,
I want to use the following code to use 'NOT LIKE' clause for my File system search here is the code:
SELECT Docs.FileName
FROM OPENQUERY(OPINIONSERVER, 'SELECT Filename FROM SCOPE() WHERE FREETEXT(''Any text not to search'')') AS Docs
I want to use the above code for my html file system search similar to:ColumnName NOT LIKE N'%1971%'
The confusing part for me is that in normal queries we use the column name to search in, but while searching in the file system using the FREETEXT() function how we exclude the words user dont want to search.
I am using Dotnetnuke.
View 2 Replies
ADVERTISEMENT
Aug 9, 2006
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
View 3 Replies
View Related
Mar 29, 2008
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 Related
Jul 21, 2015
Our 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] ....
View 4 Replies
View Related
Nov 6, 2014
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?
View 0 Replies
View Related
Jul 24, 2007
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?
View 1 Replies
View Related
Mar 30, 2013
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]....
View 14 Replies
View Related
Sep 14, 2004
Hi All,
Does anyone know the syntax for an insert statement using Openquery in a stored procedure? All the examples I've seen are Select statements, but I want to send data to a linked server.
Would I be better off using DTS??
Thanks,
Greg
View 1 Replies
View Related
Dec 13, 2007
How to create linked server to Dbf,
How to openquery util step by step
View 1 Replies
View Related
Sep 19, 2007
HI,
Can we use OPENQUERY with a parameter? Something like this:
SELECT * FROM OPENQUERY(@SOURCE_SERVER_NAME, 'Select * from dbo.FEED')
Please let me know at the earliest. Thanks a lot,.
Mannu.
View 3 Replies
View Related
Dec 11, 2006
How can I search throught DOCX (MS Word 2007) documents by SQL Server 2005 Full Text Search engine?
Should I something download?
View 6 Replies
View Related
Sep 14, 2004
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
View 1 Replies
View Related
Mar 25, 2015
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.
View 0 Replies
View Related
Jul 6, 2007
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.
View 7 Replies
View Related
Nov 1, 2015
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.%';
View 12 Replies
View Related
Apr 18, 2008
Hi everybody,
I am having a problem using a servername with '' in the openquery statement. I'd really appriciate if someone could suggest how I should be using it. Here is the query:
select * from openquery(sqldev est,'SELECT COUNT(*) FROM t_login WHERE username=''Tom''') into count
thanks in advance
devmetz
View 2 Replies
View Related
Jul 20, 2001
I'm trying to use the date() function in an openquery statement in query analyzer and I keep getting an illegal symbol ")" error. The statement is :
select *
from openquery([Big Blue], 'select cde_date, cde_item from acch1 where cde_date < date()')
The objective is to find records where cde_date is prior to today. The syntax works fine if I execute it on the mainframe in QMF and the ODBC connection works fine if I hard code the value. What I need is the ability to use the function so I can run the query on subsequent days without having to edit it.
Any suggestions??
View 1 Replies
View Related
Nov 20, 2002
Hello,
if I am running this query and getting the error the below error
SELECT * FROM OPENQUERY(AS400ODBC,'
SELECT CBH_CREDIT_MEMO_NMBR,CBH_CREDIT_MEMO_DATE,CBH_CUST _TAX_CODE,
CBH_TAXABLE_TOTAL,CBH_CUST_NMBR FROM CREDITBLDH CR1 WHERE CR1.
CBH_CREDIT_MEMO_DATE = (SELECT MAX(CR2.CBH_CREDIT_MEMO_DATE) FROM CREDITBLDH CR2 WHERE CR1.CBH_CUST_NMBR = CR2.CBH_CUST_NMBR)')
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0666 - Estimated query processing time 7211 exceeds limit 600.]
What can I do for this?.
I can able to run fine without subquery
You suggestion appreciated.
T's,
Ravi
View 1 Replies
View Related
Jan 22, 2003
Hi,
I am using the below SQL query for Oracle
SELECT FP_MONTH
FROM CDWD..CDW.ACCOUNT_SALES
WHERE LAST_MODIFIED_DT > '2002-12-01 00:00:00.000'
OR SOURCE_CONTROL_DT > '2002-12-01 00:00:00.000'
My questions are
1. If I am using this date fileter it is taking long time than without filter
Why ?.
2. How I can write Open query for the above query?.
I am expecting valuable advice.
Thanks,
Ravi
View 3 Replies
View Related
Feb 5, 2003
Hi,
I need your help to solve this error.
I am running the open query against Oracle server and this shows blow.
UPDATE Lp.dbo.CB
SET oldest_invoice_date = x.oldest_invoice_date
FROM ( SELECT MIN(INVOICE_DATE) as oldest_invoice_date,
I.ACCOUNT_NUM
FROM CDWD..CDW.INVOICE I
WHERE I.INVOICE_NUM >= 0
AND INVOICE_DATE IS NOT NULL
GROUP BY (I.ACCOUNT_NUM)
)x
WHERE account_num = x.ACCOUNT_NUM
I am getting the below error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'oldest_invoice_date'.
Anybody give solution what I am doing wrong?.
Thanks,
Ravi
View 3 Replies
View Related
Aug 25, 2004
Does anyone know how to use the openquery() method with dynamic SQL? I've tried these two different approaches with no success. DECLARE @sql nvarchar(4000)SET @sql = 'select producer_id from producer where producer_id = ' 'A' ' ' select producer_id from openquery([sybtest], @sql) -------------------------------------------DECLARE @producer_id char(1)SET @producer_id = 'A' select producer_id from openquery([sybtest], 'select producer_id from producer where producer_id = ' ' ' + @producer_id + ' ' ' ' )
View 4 Replies
View Related
Oct 11, 2005
I am trying to do the following:
SELECT ExpireDate
FROM OPENQUERY([10.22.10.79], Expire Date
From Product Where [ExpireDate] > 2005-12-31')
GO
However the above sql statement doesn't get the dates greater than the date provided unless there are quotes around the date. How do I add a variable that will cover this date and include the identifiers to get the correct records
View 4 Replies
View Related
Jan 29, 2004
Hi,
Here's what I did:
1) I declared a new VARCHAR(2000) variable called CQUERY like this:
DECLARE @CQUERY VARCHAR(2000)
2) I put a string query in the variable:
SET @CQUERY = 'SELECT ...'
Now, when I try to execute the OpenQuery method using that variable, it fails.
Here's the call:
SELECT * FROM OPENQUERY(OracleSource, @CQUERY)
I get the following error:
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near '@CQUERY'.
Don't tell me I can't use a variable instead of a static query? What am I doing wrong?
Thanks,
Skip.
View 5 Replies
View Related
Jun 12, 2008
Hi,
I am trying to execute a ServerB stored Procedure which takes Int as paramter using OpenQuery in ServerA.But this doesnt seems to working.
Please Help !!!!
DECLARE @param1 INT
SET @param1 1
SELECT *
FROM OPENQUERY(ServerB,'DBNAME.dbo.SP_NAME ''@param1''')
View 3 Replies
View Related
Jul 23, 2005
In SQL Server 2000 I have set up an Oracle linked server. When I runthe following query it runs fine:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT * FROM ORACLETABLENAME')However the following query does not work:-SELECT*FROMOPENQUERY(LINKEDSERVERNAME,'SELECT FIELDNAME FROM ORACLETABLENAME')This error is returned:-Server: Msg 7321, Level 16, State 2, Line 1An error occurred while preparing a query for execution against OLE DBprovider 'MSDAORA'.[OLE/DB provider returned message: ORA-00904: "FIELDNAME": invalididentifier]OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Preparereturned 0x80040e14].Basically select * works ok, but if I specify the field(s) I need thenit errors. I have tried entering the field names in upper and lowercase but it makes no difference.My real problem is that some dates in the Oracle database are pre 1753which SQL server does not recognise so I need to convert (decode) them.Any help would be appreciated.ThanksChris
View 2 Replies
View Related
Mar 31, 2008
Hi,
To access data from Linked server, which of the following is good Performance-wise:
1. Use of OPENQUERY to access data from Linked server
2. Using Direct query to access data using the four part Name of Linked server
Thanks in Advance.
View 1 Replies
View Related
Oct 23, 2007
I have a dataming stored procedure. it works fine on it own, thanks to help from this forum. However, when i try to run aggregate funtions on the table it returns using sql server I get:
"The OLE DB provider "MSOLAP" for linked server "DM" indicates that either the object has no columns or the current user does not have permissions on that object."
This works:
CALL Assemby.Namespace.MyFunction('[model]', 'db','table',0)
And this works:
SELECT SUM([prediction]) as value, count([prediction]) as count FROM OPENQUERY(DM,'
--DMX query that sproc produces and executes
')
but this:
SELECT SUM([prediction]) as value, count([prediction]) as count FROM OPENQUERY(DM,'
CALL Assemby.Namespace.MyFunction(''[model]'', ''db'',''table'',0)
')
gives this:
The OLE DB provider "MSOLAP" for linked server "DM" indicates that either the object has no columns or the current user does not have permissions on that object.
Could anyone point me in the right direction?
Thanks,
Adam
View 3 Replies
View Related
Feb 18, 2007
Hello,
I've got a problem with OPENQUERY. When I use SQL Server Management Studio, I don't have any errors (I'm logged as Admin via Windows Auth.). When I try to use Adomd via ASP.NET (user - ASPNET with admin role, which is set in Man. Studio), I've got an error: Errors in the high-level relational engine. A connection could not be made to the data source specified in the query. Any idea? Other querries work fine (i mean that querries which don't use OPENQUERY statement).SELECT
t.[Name],
[DecisionTreeModel].[Party],
PredictProbability([Party])
From
[DecisionTreeModel]
PREDICTION JOIN
OPENQUERY([VotingRecordsRDS],
'SELECT
[Name],
[Permanent Tax Cuts],
[Campaign Finance Overhaul]
FROM
[dbo].[VotingRecords]
') AS t
ON
[DecisionTreeModel].[Permanent Tax Cuts] = t.[Permanent Tax Cuts] AND
[DecisionTreeModel].[Campaign Finance Overhaul] = t.[Campaign Finance Overhaul]
ORDER BY PredictProbability([Party]) DESC
Thank you in advance for any help with this.
MM
View 4 Replies
View Related
May 19, 2008
We are running the following query against a MYSQL database that runs a third party software.
SELECT Email, CONVERT(INT, count) AS clicks, date AS Last_Clicked_Date
FROM OPENQUERY(MYSQL, 'SELECT Email, SUM(count) count, MAX(date) date FROM tracking WHERE action="click" GROUP BY Email')
We are upgrading that software to the latest version. This requires a migration from MYSQL to PostGres.
There are some schema changes involved
Current MYSQL field name New PostGres field name
email email_address
date tracking_date
action action_name
I've modified the query to use the new fields
SELECT Email_address, CONVERT(INT, count) AS clicks, date AS Last_Clicked_Date
FROM OPENQUERY(MYSQL, 'SELECT Email_address, SUM(count) count, MAX(tracking_date) date FROM tracking WHERE action_name="click" GROUP BY Email_address')
and I get the following error message -
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "test" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "test".
When I run this query it works -
select email_address, tracking_date
from openquery(test, 'select * from tracking')
What am I doing wrong?
Thanks
View 19 Replies
View Related
Feb 13, 2008
Hi All,
I would like to say Thank you in advance, i have a big problem with a deadline coming on friday, Here is my problem, i want to access data from remote server, the server name is "SeverName" just for some reason, and my table name is T1, T2, T3, T4. and My Comlumn Names are, Col1, Col2, .......Col11. Here is the code i used and the error i got: Note that T1=Table one, DB =DataBase, Col = Column.
Select
Col1 ,
Col2,
Col3,
Col4,
Col5,
Col6,
Col7,
Col8,
Col9,
Col10,
Col11
From
Openquery (ServerName , '
Select
T1.Col1,
CAST(substring(T1.Col2,1,255) AS Varchar(255)) AS Col2,
CAST(substring(T1.Col3,1,255) AS Varchar(255)) AS Col3,
CAST(substring(T1.Col4,1,255) AS Varchar(255)) AS Col4,
T1.Col5,
CAST(substring(T1.Col6,1,4000) AS Varchar(4000)) AS Col6,
T1.Col7,
CAST(substring(T1.Col8,1,255) AS Varchar(255)) AS Col8,
T3.Col9 As Col9
CAST(substring(T2.Col10,1,255) AS Varchar(255)) AS Col10,
T4.Col11 AS Col11,
FROM
DB.T1 a
Inner Join
DB.T2 b
on b.T2ID = a.T1ID
Inner Join
DB.T3 c
on c.T3ID = a.T1ID"
Inner Join
DB.T4 d
On d.T4ID = a.T1ID
')
Here is the error i got:
€œ[OLE/DB provider returned message: No query has been assigned to this statement.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IColumnsInfo::GetColumnsInfo returned 0x80004005: ].
Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. €œ
Please help, anyone who could have any alternative it will be fine, if the code is wrong let me know, what ever reason i will take it.
Thank you again,
Sami
View 10 Replies
View Related
Aug 21, 2007
We have a MS SQL database with an Oracle linked server 'ALTTEST'
We can Select, Insert, Delete and Update tables on the Oracle Db using OpenQuery, but how do I apply a table lock with a transaction?
I've tried applying the code below, but it doesn't work.
Any help appreciated.
BEGIN TRAN
SELECT * from openquery(ALTTEST,'select LAST_PIN_NUMBER from sys_params') WITH (TABLOCKX)
COMMIT
View 5 Replies
View Related
Jan 22, 2006
I'm performing a particular word Search in MS Word, Text, PDF docs and displaying the results through Index Server linked to SQL Server when it is matched. For which I'm using Openquery in the stored procedure which works fine in Query Analyzer of the SQL Server but doesn't work ( displays none of the results) when i call it from the ASP.NET Page. I am not able to figure out Where and What is the problem? The Stored Proc which is i'm using is shown below Any help will be greatly appreciated. Thanks for your time and help in Advance
CREATE PROCEDURE SelectIndexServerCVpaths ( @searchstring varchar(100) ) AS SET @searchstring = REPLACE( @searchstring, '''', '''''' ) IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'FileSearchResults') DROP VIEW FileSearchResults EXEC ('CREATE VIEW FileSearchResults AS SELECT * FROM OPENQUERY(FileSystem,''SELECT Directory, FileName, DocAuthor, Size, Create, Write, Path FROM SCOPE('''' "c:inetpubwwwrootsap-resourcesUploads" '''') WHERE FREETEXT(''''' + @searchstring + ''''')'')') SELECT * FROM CVdetails C, FileSearchResults F WHERE C.CV_Path = F.PATH AND C.DefaultID=1 GO
which works with followin stat in Query Analyzer Exec SelectIndexServerCVpaths @searchstring = 'The Search text'
but doesn't work when i connect it to a Datagrid in my ASP.NET Page objcmd = new SqlCommand("SelectIndexServerCVpaths", objConn); objcmd.CommandType = CommandType.StoredProcedure; objcmd.Parameters.Add("@searchstring",strsearchstrings); objConn.Open(); objRdr = objcmd.ExecuteReader(); dgcvs.DataSource=objRdr; dgcvs.DataBind(); objRdr.Close(); objConn.Close();
View 9 Replies
View Related
Mar 27, 2001
Does anybody know how to pass variables to openquery statement? I executed the following statement against DB2 mainframe
and got an error message.
Thanks in advance
Hung-Ban
declare @deptname varchar(20)
select @deptname = 'HEAD OFFICE'
select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG
where DEPTNAME=@deptname")
Server: Msg 7399, Level 16, State 1, Line 4
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703
]
View 1 Replies
View Related
