How To Extract Data From SQL Server With The Index Server Search Result?

Jan 14, 2006

I am developing a Recruitment Agency Website
I am using MS SQL Server in which I have a table called CV_Details which stores all the details of different Job Seekers. I have stored all the Word and PDF docs of the Job seekers in a 'mycatalog' using Index Server and stored the path in the CV_Details table along with rest of the cv details. Basically I want to download the cv's which i get after making a search in the doc's. I am able to get the filename, Path, Filesize by using Index Server Query.
Is it anyway possible to extract all the CV Details data by matching the path.
I want the cv details from the SQL Server of all the Related Paths at the same time not one at a time. Is it possible.
I tried alot but i'm going nowhere. I'm giving my code below. Can anyone help me please.
Thank you very much in Advance

            string strCatalog = "TestCatalog", strsearchstrings="";
            string strQuery="";
            strsearchstrings = txtsearch.Text;

            strQuery = "Select Filename,PATH from Scope() where FREETEXT('" +strsearchstrings+ "')";
            // TextBox1.Text is the word that you type in the text box to query by using Indexing Service.
            string connstring = "Provider=MSIDXS.1;Integrated Security .='';Data Source="+strCatalog;

            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connstring);
            conn.Open();
           
          
            System.Data.OleDb.OleDbCommand objcmd = new System.Data.OleDb.OleDbCommand(strQuery, conn);
            System.Data.OleDb.OleDbDataReader objRdr;

            objRdr = objcmd.ExecuteReader();
            DataGrid1.DataSource=objRdr;
            DataGrid1.DataBind();
            objRdr.Close();
            conn.Close();

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: Speed Up Text Search In Large Result Set?

Jul 14, 2015

I have a query below which filters detail field in the #TempLogins table. The details field is a text field which contains many types of text strings, some containing urls that have parts like "ResultID=5" which is what is contained in the ResultIDSearch and ResultSetIDSearch fields. The records with entries like "ResultID=5" are the ones I'm trying to filter for.

The problem I have is that the query takes way too long to run. The TempLogin table has around 200 K records and the TempSearch table has around 80 K records.

select * from #TempLogins a where exists
(select 1 from #TempSearch t1 where
a.detail like '%' + t1.ResultIDSearch + '%'
or
a.detail like '%' + t1.ResultSetIDSearch + '%')

View 1 Replies View Related

SQL Server 2000 Full Text Search (extract Pieces Of Text)

Sep 12, 2007

Hello everyone !
I want to perform Full Text Search with SQL Server 2000. My documents (.doc, .xls, .txt, .pdf) are stored in a SQL Server field which is binary (the type of the column is image).
I would like to know, how you can extract pieces of text from the documents.
Example:
I have a ASPX page with codebehind in C# making the search in a table in SQL server that is full text indexed.
I make a search looking for the word "peace", than SQL server will take care about the search and return it to me the rows that match with that. But also I'd like to extract the 50 characters before and after where sql server found the word "peace" to show in the result page.
Does anyone has any idea how to work around it ?
 Best regards.
Yannick

View 5 Replies View Related

String Index Function (substring / Charindex) - Extract Specific Characters From Data

Aug 5, 2013

SQL Query. What i need is to be able to extract specific characters from data.

Eg name

1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the results

i.e. 1.2-KPIA1-App-00001 1.2 KPIA1

*I need this in part of a script as there is thousands of rows of data.

View 4 Replies View Related

SQL Server 2012 :: Search For Maximum Value Of A Specified Index On Dataset

Oct 16, 2014

I have a dataset that I need to search for a maximum value of a specified index.

Example of dataset:

Heat Index Heat # Frame Window
1 584 110 110
1 584 102 109
1 584 95 106
1 584 190 112
2 586 100 100
2 586 150 120
2 586 170 130
2 586 112 126

I need to find the maximum value of Frame for each heat and the maximum value of Window for each heat. As you can see each heat has several data points.

Is there a way I can send the dataset to a function? I could then use a loop to test for the maximum of Frame and the maximum of Window for each heat.

Could I use an array?

View 3 Replies View Related

Data Flow Task Error To Extract Data From Sql Server To Excel

Mar 28, 2008

Hi All,

I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.

The SQL datable fileds are
i) ID - Int

ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)

While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.


After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.

Please do help me out.

thanks
Sanra

View 4 Replies View Related

Extract Data From SQL Server Into .XLS Or Flat File...

May 8, 2001

Hi,

I am interested in writing a stored procedure that will compute and select data from SQL Server 2000 database and store it into a .XLS or flat file.

how can i do that ?

need help.

Thanks.

-- Parag

View 3 Replies View Related

Extract Data From Middle Of String In SQL Server

Jan 11, 2008

I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:

DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_

I want to select the 8 characters after the first comma in the field, and then convert it to a date format.

I am a novice, so any help with the correct function and syntax would be greatly appreciated.

thanks,
Dave

View 8 Replies View Related

MAC Used As Search Index For Encrypted Data: How Secure?

Aug 17, 2006

I have read recommendations about searching encrypted data. Typically, they involve creating a MAC (message authentication code) table. One of the elements of that table is a HASH of the encrypted data (plus a Mac key) that is used as an index for searching. Is that HASH as secure as the encrypted data itself, or is this approach less secure? If it is less secure, then may I assume that approach is the only feasible way to search data encrypted by nondeterministic algorithms?



TIA,



Barkingdog

View 5 Replies View Related

Extract IBM UniData (Pick Database) Data Into SQL Server

Oct 31, 2006

Good afternoon SQL dudes Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational Pick nested relational multi-valued relational database) into a SQL Server?More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system) I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience. Super duper, thank you SQL troopers :)

View 2 Replies View Related

SQL Server 2012 :: How To Extract One More Piece Of Data From The Column

Dec 17, 2014

I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:

CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,

[Code] ....

All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:

CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));

I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...

I can retrieve all the column names that are in uppercase using this:

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;

but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):

ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade

[code]....

The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.

So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?

View 5 Replies View Related

Integration Services :: Extract Data From Server And Load Into Oracle Database?

Jun 23, 2015

Looking for sample ETL package to extract data from SQL Sever Database and load into Oracle Database using SQL SERVER INTEGRATION SERVICES 2008. The requirement is for full load and incremental load both.

View 5 Replies View Related

Search String And Extract To Another Table

May 4, 1999

Hi,

Is it possible to search a field in a database, extract CERTAIN data, and insert THIS data into another field in another table?

Example:

Address
---------------------------------------------------
18 BerryWood Drive, Midland, Doncaster, Y09 2JF

I want to extract the postcode from this field into another field in another table....

Example 2:

Name
-----------
Ivor Smith

I wish to strip the last name from the field, and put this into a field in another table..

David

View 10 Replies View Related

Extract Strings From Search Path Column In Audit Table

Jul 22, 2013

I have a 'searchpath' column in audit table. I need to extract the folders from the column and display it in separate columns.

Column searchpath has value like:

//content/folder[@name='AFR']/folder?[@name='AFRABC']/folder[@name='CDD']/folder[@name='Packages'] /folder[@name='Deployment']/package[@name='XXX spend name']/model

I need to extract the folder names and display it in separate columns in a new table this way:

AFR AFRABC CDD Packages Deployment XXX spend name

View 6 Replies View Related

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 Replies View Related

SQL Server 2014 :: Index Dates To Numbers With A Large Data Set?

Jun 16, 2015

I am trying to index dates to numbers with a large data set.

The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.

Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.

Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4

View 9 Replies View Related

How Do I Copy The Index Statistics To A Development Database Server Without Data.

Jul 16, 2007



I want to be able to reproduce my production execution plans on development with copying data.

View 1 Replies View Related

SQL Server 2014 :: Semantic Search Not Finding Keywords Identified By Full-Text Search?

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

Converting DB2 Timestamp Data To SQL Server 2005 - Problems With Unique Index

May 24, 2007

I am attempting to move a timestamp data column from DB2 to SQL Server 2005. Normally not a big deal but the column is part of unique index.



The DB2 timestamp has seconds of ss.ssssss but SQL Server only has ss.sss.



Most all the times entered into this column are a from an automated process so they are really close together timewise.



Here is what I have come up with so far:

1. Fast Load OLEDB with a batch of 10,000 records at a time

2. On the fail of the batch redirect rows to a regular table load OLEDB insert task

3. On the fail of the single insert redirect rows to script that ups the seconds one tick.

4. Attempt one last insert of the modified rows

5. If fail, then store the record off to a delimited text file



I am hoping to get the number of records that wind up in the delimited text file to be a very small number and not in the 1,000+.



Any help would be appreciated.

View 5 Replies View Related

Create Site Search Using Sql Server Full Text Search

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

How Can I Search Throught DOCX (MS Word 2007) Documents By SQL Server 2005 Full Text Search Engine?

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

Search Result .......need Some Help

Dec 21, 2006

Dear Experts
Here is my code:
SqlConnectioncn = new SqlConnection(ConfigurationManager.ConnectionStrings["SimvipConnectionString"].ConnectionString);
 protected void Page_Load(object sender, EventArgs e)
{
 SqlCommand cmd = new SqlCommand("SP_SEARCH", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@TypeOfSim", Request.QueryString["NID"]));
cmd.Parameters.Add(new SqlParameter("@CategoryID", Request.QueryString["CID"]));
cmd.Parameters.Add(new SqlParameter("@Price", Request.QueryString["L"]));
cmd.Parameters.Add(new SqlParameter("@Price2", Request.QueryString["S"]));
cmd.Parameters.Add(new SqlParameter("@Title", Request.QueryString["keyword"]));
cmd.Connection.Open();
cmd.ExecuteNonQuery();
 SqlDataAdapter sqlAdapter1 = new SqlDataAdapter();
sqlAdapter1.SelectCommand = cmd;
 DataSet ds = new DataSet();
sqlAdapter1.Fill(ds, "ds");
 
CollectionPager1.DataSource = ds.Tables["ds"].DefaultView;
CollectionPager1.BindToControl = DataList1;
DataList1.DataSource = CollectionPager1.DataSourcePaged;
}
My problem is when there is no result(no item in database), the DataList1 will display the text "some text will display here"  instead of empty page.
In FormView  I know the option <EmptyDataTemplate>. But in the DataList I don't know how to do this. I am a beginner.
Can anybody show me how....Thanks a lots
 

View 1 Replies View Related

Combine Rows In Search Result

Aug 20, 2007

In Sql Server 2005 Express I have this table:CREATE TABLE [dbo].[Sections](
    [SectionID] [int] NOT NULL,
    [DocumentNo] [smallint] NULL,
    [SequenceNo] [smallint] NULL,
    [SectionNo] [smallint] NULL,
    [DocumentTypeID] [smallint] NULL,
    [SectionText] [ntext] NULL)
 Each paragraph of text (SectionText) is in its own row
(SectionNo)  Each primary document has a DocumentTypeID of 1 with
three subdocument types (2=Index,  3=Background, 4=Report).I run this query and return a collection of single rows from various documents grouped together by DocumentNo:     SELECT *
    FROM Sections
    WHERE CONTAINS (SectionText, 'exercise')
    ORDER BY DocumentNo
For each row that contains the search term, I would like to
return the full document (all rows as parapraphs within one row of
returned data).  In other words, I want to reconstitute the full
document as it existed prior to being inserted into the database with
paragraph separation. For exampe, if the search term is in row 3
of DocumentNo=5, DocumentTypeID=2, I want to return all the rows of
that document in one block of text that retains paragraph format
(preferablly with a line break and carriage return between
paragraphs). How can this be done?

View 2 Replies View Related

Add Index To Select Result

Mar 20, 2007

Eliko writes "i would like to add a coloumn to a select result, so there will be another coloumn with indexed running numbers for each record.
how can i do it?

thank you
eliko"

View 1 Replies View Related

Index On Result Of Function

Apr 27, 2007

I have a table with about 28 million records in it. Each row has an ID (PK), logged (datetime), IP varchar(15)



The data grows at about 14 million records per year. I'm going to be running queries on the table that extract the MONTH or YEAR from the logged column. In Foxpro tables I would have created indexes on YEAR(logged) and MONTH(logged) so my queries would run faster. Is this possible/necessary in SQL Server?

View 5 Replies View Related

NO Records In Result Set When Non-alphanumerics Are Used In Search Criteria

Nov 5, 1999

I'm trying to figure out why I am not getting any result set back from a search that includes non-alphanumeric
or non-printable characters. For instance, if I have a table with a 20 character name column with names with
beginning ranges from A-Z, why doesn't the following return any rows:
select * from table where name < CHAR(126).
In the ASCII character set, 126 is a tilde (~) which is numerically above the alphanumeric ranges 1-9,a-z, and
A-Z. Shouldn't all records that sort lower in the character range be included in the result set ?

I'm assuming this has something to do with the default collation sequence being used which somehow does
not include characters outside the alphanumeric range. Any ideas ?

View 4 Replies View Related

Transact SQL :: Combine 3 Tables In One Search Result

Jul 1, 2015

I need a query to publish the front page of a blog.  Each blog post needs to show BlogTitle, BlogText, PublishDate, PublishBy, Primary Image and number of comments.  I would like to be able to do this in one sql statement, if possible.

 The table structure is below, you can assume the first image returned from the image table is the primary image.  

CREATE TABLE [dbo].[Blogs](
[ID] [int] IDENTITY(1,1) NOT NULL,
[BlogTitle] [nvarchar](200) NULL,
[BlogText] [nvarchar](max) NULL,
[Tags] [nvarchar](200) NULL,

[Code] ....

View 3 Replies View Related

SELECT Result Without Index Sorting

Mar 16, 2004

I want to SELECT the result from table, but i want the result return in record entry order, instead of sort by index or ORDER BY certain field.

View 14 Replies View Related

Search Index

Aug 18, 2004

Dear Experts

I had created an index on of my table in SQLSERVER 2000 !
and after some time i had added a field on the table for which
i had created index now i want to reindex the table ,
but now i don;t know the name of the indextable which i had made,
I tried to search it on the ENTERPRISE MANAGER
but i can;t get !
Can any one help me to find out the list of all the
TABLE NAME / and the INDEX


With Regards
Manish Kaushik

View 1 Replies View Related

Extract Reports From Server

Nov 19, 2007

I have created and deployed almost 30 reports to a ssrs server.

After that, due to a stupid mistake with source control, my entire project was deleted and the reports are gone.

Fortunately the reports are still deployed on the server.

Is there anyway i can salvage the reports from the report server ?

I need to know how to extract the rdl of each report from the server.

Thank you for your help

View 6 Replies View Related

Need A Faster Paging In A Wesite Search Result Page

Jan 2, 2007

have over million rows in the our table and we are looking forward to increase the speed of our query .Any ideas?set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[mainSearch]
@startRowIndex int,
@maximumRows int,
@rowCount int out,
@countedRow int,
@QUERY nvarchar(400)
AS
SELECT _ID,_NAME,_TYPE,_CREATEDATE,ESTATETYPE,ESTATEDISPLAYPRICE,ESTATEDISPLAYPRICECURRENCY,ESTATEDISTRICT,ESTATECITY,ESTATEROOMCOUNT,NUMBEROFPICTURES FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY _CREATEDATE DESC) AS ROWRANK,*
FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL
ON ADTBL._ID = KEY_TBL.[KEY]
Where (_DELETIONSTATUS=0)

)
AS RANKEDADDS

WHERE ROWRANK > @startRowIndex AND ROWRANK <= @startRowIndex + @maximumRows -1

if(@countedRow < 1)
SET @rowCount =
(
SELECT COUNT(_ID) FROM ADDS AS ADTBL JOIN CONTAINSTABLE(ADDS_FTS,(ADDS_VALUE),@QUERY) as KEY_TBL
ON ADTBL._ID = KEY_TBL.[KEY]
Where _DELETIONSTATUS=0
)
else
SET @rowCount = @countedRow

RETURN
 

View 2 Replies View Related

Store Result[index] From Table In Local Var

Apr 30, 2008

Hi,

Can I take the [index] result from a table and store it in a local var?
For example a table

declare @keyWords(
word varchar(40),
wordLen smallint
)

word len
---- ---
test 4
t 1
e 1

declare @index smallInt = 2
SET @keyWord = SELECT TOP @INDEX word FROM @KeyWords

This obviously is not correct for several reasons (TOP returns multiple results, TOP doesn't work with local var etc)

View 2 Replies View Related

How To Extract A Schema From A Sql Server Database

Apr 4, 2008

Hi,
  I need to extract or get the schema from tables in my databse and what is the best way to do it. Is there any third party tool to do it??
 
Any idea will be appreciated..
Thanks
Karen

View 4 Replies View Related







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