Full-text Serach Timing Out Only On First Query Attempts

Jan 25, 2008

I'm running into a problem with Full-Text searching. I have a procedure which uses a full-text search.
When I run it in SQL query analyzer €“ it runs immediately.
I exec this procedure from my ASP page and it returns timeout error.
After the first query attempt I can run it (executing the ASP-page) as many times as I want with different search words and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try.
I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.

I'm using Microsoft SQL Server 2005.
Any help would be greatly appreciated.
Thank you.

View 2 Replies


ADVERTISEMENT

Full Text Timing Out Only On First Query Attempts

Oct 27, 2007

I'm running into a a problem with Full Text searching. I've narrowed my code that produces the error down to this:






Code Block

SELECT Content
FROM Text
JOIN CONTAINSTABLE(Text, Content, 'lake') AS A
ON A.[KEY]=Text.ID;


and here is how I initiated the full text for the table:






Code Block

sp_fulltext_database 'enable'
sp_fulltext_catalog 'theCatalogSearch','create'
sp_fulltext_table 'Text','create','theCatalogSearch','PK_Text'
sp_fulltext_column 'Text','Content','add'
sp_fulltext_table 'Text','activate'
sp_fulltext_table 'Text','start_full'
sp_fulltext_table Text, 'Start_change_tracking'
sp_fulltext_table Text, 'Start_background_updateindex'


The first time I run it in the SQL query analyzer it sits there for about 30 seconds and then gives me "Timeout expired (error - 2147217871)." After the first query attempt I can run it as many times as I want and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try. I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.

I'm using Microsoft SQL Server 2005. The code I'm writing is pretty basic so maybe it's the way that the database is set up or the way I initiated the full text tables? Any help would be greatly appreciated. Thank you.

View 3 Replies View Related

Serach On Full Text Catalog Faill Please Help

Jul 22, 2005

I got following meesage, when i doing search, please helpMicrosoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Search on full-text catalog 'CCM_FT_Catalog' for database ID 10, table ID 987150562 with search condition '11' failed with unknown result (3a8d75c).

View 1 Replies View Related

How Can I Do Text Serach In Field With Datatype Image

Jan 18, 2005

i write a small web application to save word files in sql server and i store it as an image datatype
i need to do operation such as search within these files for any word

is there any One Can help me with that ???????????????????????????? Important

View 2 Replies View Related

Full Text Query

Aug 31, 2001

I have a problem with sql 7 full text query. Everything appears to be in place the database is enabled and the catalogue populated but a query like this
select * from risks_intercat WHERE CONTAINS(RISK_NOTES, ' "bean curd" ')
always returns this;
Search on full-text catalog 'ftc_Risks_ic' for database ID 8, table ID 329768232 with search condition ' "bean curd" ' failed with unknown result (8bcf8cc).
has anybody got any ideas why?

View 1 Replies View Related

Package Attempts To Query Source Db Constantly When Editing

Aug 16, 2006

Trying to set up a tranform task between a mysql db using and ADO.NET connection and sql server.
My query to pull from the mysql db is something like "select x,y,z from table where last_updated" > @User::LastUpdated. This command is set up as an expression for the Data Flow Task and is the value for the [DataReader Source].[SqlCommand]

I have two questions.

Why does the package attempt a query against the mysql database all the time?
And Why is the query attempting to pull the entire table instead of having any regards for my where clause?

I've even added where last_updated > greatest('2006-08-15', '" + @User::LastUpdated to attempt to get it a where clause even when the parameter isn't set yet.

What is the trick? This is not feasible when pulling from multi-million row tables.

View 2 Replies View Related

Full Text Commands In SQL Query?

Jul 2, 2007

My host does allow me to create full text catalogs. I use SQL Server Management Studio Express, so I'd have to do it "by hand" as opposed to point-clicking menus and going through wizards. Where do I go to find those commands? I've seen them before but I can't remember where to get them. They're the ones that begin with sp_.
I need the commands for:


Creating a full text catalog

View 4 Replies View Related

Full Text Query Problem

Jan 8, 2008

Is there something wrong with my query or my full text catalog? The first query returns data while the second one does not. Does it have something to do with it being a number? I can get the rows to return by searching on alpha text.

select * from gk_info
where info_desc like '%6416%'

select * from gk_info WHERE
CONTAINS (info_desc, '"6416"')

View 1 Replies View Related

SQL Query For Full-text Search!!!

Feb 8, 2008



Hello!!
I want to add the full text search feature into 2 columns of a table in my DB.What will be the query that I should execute.I have aalready the tables existing.So probably I need to ALTER alongwith the new functions!!

Thanks!!!

View 2 Replies View Related

Full Text Query With Noise Words (I Think)

Mar 12, 2008

Hope you can ignore your personal music tastes with this post!I have a table of Artists with a Full Text Index on a few columns on that table.  My full text querying against this table works really well apart from it seems for one band - "Take That"!  I'm guessing this is because these words consitute noise words? I parse user's search terms and add an "AND" between each word. So for example my query is essentially:select * from containstable(Artists, *, '"take AND that"') As I say, this works fine for Pink Floyd etc, but not for these guys!  So either SQL Server has a preference on boy bands or I am thinking it is because these words are deemed to be noise. Anybody got any tips on how I could tackle this?  I suppose it is quite possible that there might be another band with the same problem.I do have exclusive access to the SQL box, so perhaps I could edit the noise words file......Thanks 

View 2 Replies View Related

Pages Results On Full-text Query - Sql Help Please

Mar 30, 2008

 Hi -  I am trying to add paging to my stored procedure. The stored procedure successfully executes a full-text search. Unfortunately, the paging routine below the full-text search operates on the articles table after the search has been conducted. This means that it utilizes the row numbers from the entire table rather than the row numbers from the result set. I somehow need to the paging routine starting at "WITH tempArticles AS" to operate on the search results rather than the articles table. I am too new to SQL and can't figure out how to populate a temporary table storing text search results to use in this paging routine. Can anyone help me with this? Thanks! Peter   dbo.Search_Articles        @searchText varchar(150),    @PageSize int,    @PageNumber int        AS         Declare @RowStart int     Declare @RowEnd int         if @PageNumber > 0     Begin         SET @PageNumber = @PageNumber -1;     SET @RowStart = @PageSize * @PageNumber + 1;     SET @RowEnd = @RowStart + @PageSize - 1 ;     CREATE TABLE #results (ArticleID int)    INSERT INTO #results        SELECT ArticleID     FROM articles    WHERE CONTAINS(Description, @searchText) OR CONTAINS(Title, @searchText)    UNION    SELECT ArticleID     FROM article_pages    WHERE CONTAINS(Text, @searchText)    /*this returns all matching records from the text search*/    SELECT *    FROM articles, #results    WHERE #results.ArticleID=articles.ArticleID;                     WITH tempArticles AS    ( SELECT Title,      PostDate,      UserID,      City,      Country,      Tags,      StoryID,      Approved,        ROW_NUMBER() OVER (order by PostDate) as RowNumber        FROM articles WHERE Approved = 1)           SELECT *    FROM tempArticles     WHERE RowNumber >= @RowStart and RowNumber <= @RowEnd;              END

View 4 Replies View Related

Full Text Query Starts Slow

May 2, 2006

I've created a FullTextCatalog on one of my Databases and added a full text index on one of my text columns.

The first time I run a query against the data it takes roughly 40-45 seconds to return data. After that it blazes and runs in under a second. If I don't query it for 20-30 minutes, it will take 40-45 seconds again and then fly until the next break.

Is there a configuration setting somewhere that I'm missing on this? Currently the Index is only about 5MB so it should take that long to read in when I'm querying it.

I don't think it has anything to do with size because the actual return can contain anywhere from 10-80K rows and the speed is about the same.

I'm using Standard edition on a 2003 Standard Server if that plays into the potential problem at all. We're currently downloading and installing the new Service Pack to see if that fixes it, but for some reason I'm not holding my breath. I'm assuming that there is something we need to change in the configuration.

Let me know if I'm missing any pertinent information. Thanks.

View 2 Replies View Related

Full Text Query Eventually Hangs CPU At 100%

Mar 13, 2008



For one day, this SPROC executes very quickly to return results on a Full Text catalog.





Code Snippet
ALTER Procedure dbo.sp_RSSHarvestedHeadlines_FullTextSearch
(
@ORKeywords varchar(4000) = 'xxxx',
@ANDKeywords varchar(4000) = 'xxxx',
@NOTKeywords varchar(4000) = 'xxxx',
@SourceID int = -1,
@IsHidden bit = null
)
As
set nocount on
SELECT HHL.HeadlineID,
HHL.Title,
HHL.Link,
HHL.[Description],
HHL.PubDate,
HHL.GMTDateAdded,
RSSSources.SourceTitle,
RSSSources.SourceLink
FROM RSSHarvestedHeadlines HHL
INNER JOIN RSSSources ON HHL.SourceID = RSSSources.SourceID
WHERE HHL.PublishedFlag = 0
AND (@IsHidden is null OR HHL.HideFlag = @IsHidden)
AND (@SourceID = -1 OR HHL.SourceID = @SourceID)
AND (@ORKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ORKeywords) OR CONTAINS(HHL.Description, @ORKeywords)))
AND (@ANDKeywords = 'xxxx' OR (CONTAINS(HHL.Title, @ANDKeywords) OR CONTAINS(HHL.Description, @ANDKeywords)))
AND (
@NOTKeywords = 'xxxx'
OR (
(NOT CONTAINS(HHL.Title, @NOTKeywords)
AND
NOT CONTAINS(HHL.Description, @NOTKeywords))
)
)
ORDER BY HHL.GMTDateAdded DESC, HHL.PubDate DESC




But somethign happens overnight and in the morning the sproc times out. While running (even from a new query window on the SQL 2005 server itself) it utilizes 100% CPU until it times out.


When I pass default parameters to the sproc (not using any part of the query that uses Full Text) the sproc returns every record in the database very quickly. No hang ups.

But the moment I add any text in say the @ORKeywords parameter, for example, the sproc utilizes 100% CPU for maybe 15 seconds and then times out.

By accident I discovered that I can fix this temporarily by copying the database. I don't use the new copy or anything. Just the act of copying the database fixes it. The sproc then executes normally, and quickly. But the next morning it's back to slow again.

Note, over night I am adding about 1000 records to the table.

Would automatic updates to the FT Catalog choke on 1000 records?

Also note that one of the fields being cataloged is a "Text" field (blob). Would that cause this?

Would what text is being added to the table matter? What if an invalid character was added (like some european character or a control character)? Would FT indexing hang up on that?

I am at a loss. Any ideas?


View 5 Replies View Related

Full-Text Search Query Question - Performance

Jul 20, 2005

I have a table with 3M rows that contains a varchar(2000) field withvarious keywords. Here is the table structure:PKColumnImageIDFullTextColumnThere is an association table:ImageIDContractIDNow, I want to do a query where the ContractID = x and Contains someword in the FullTextColumn. There is an association table that mapsImages to Contracts - so I can't use the trick of putting the Contractcode in the FullTextColumn.I'm finding that first the FTS service is performing a search on theKeyword (which can take a long time if 100K rows are returned) thenjoining to the association table for the particular contract.Is there anyway to make this faster by telling the FTS service, onlysearch this subset of rows for the keyword based on the contract.Sorry if this sounds convoluted. Appreciate any help you can suggest.Thanks!

View 1 Replies View Related

SQL Server 2005: Full Text Query Failed

Sep 3, 2007

Hi guys,

I am getting a really weird error message when executing a full-text query on SQL server 2005:

-------------------------
Microsoft OLE DB Provider for SQL Server error '80040e14'
The execution of a full-text query failed. "The form specified for the subject is not one supported or known by the specified trust provider."
-------------------------

Just to give a bit of background: we recently moved our database from a machine with SQL Server 2003 to a different computer with SQL Server 2005. This is when the error started showing up.

The query is not particular complex:

SELECT * FROM myTable M INNER JOIN FREETEXTTABLE(myTable, *, 'keyword') ct ON ct.[KEY] = M.Resource_ID

It's the "Freetexttable" bit that creates the error message. I have done some research on google, but I can't seem to find a solution.

Has anybody come across this error before? Any ideas on how I could fix it?

View 14 Replies View Related

Disable Noise Words Checking In Full-text Query

Sep 8, 1999

how to completly disable noise words checking in full-text query?
(noise dictionaries already cleared!)

View 1 Replies View Related

Full-text Query (Freetexttable) Returning Duplicate Rows

Aug 31, 2007

We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:




Code Snippet
SELECT *
FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL
ORDER BY RANK DESC






vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.

This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.

I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?

View 4 Replies View Related

Msg 7619 Full-text Query Failed. Service Is Not Running.

Mar 5, 2007

I am getting following error when query using a full-text index:
 
select *
from workitemlongtexts
where Contains(words, 'test');
 
==error message==
Msg 7619, Level 16, State 1, Line 1
The execution of a full-text query failed. "Service is not running."
 
I have verified that
1. msftesql service is running,
2. I even rebuilt the full-text index and it didn't help
3. my full-text crawl job is running fine
4. my DB is full-text enabled
 
Can someone explain what "Service" the error refers to? I am using SQL 2005 Ent SP1.

View 1 Replies View Related

Execution Of A Full-text Operation Failed. A Clause Of The Query Contained Only Ignor

Jan 31, 2006

Hi.

I get the following error

Quote: Execution of a full-text operation failed. A clause of the query contained only ignored words.

because I try to search words that are 2 characters like "me" or like " on "

why can't I use words that are 2 characters in sql query by using the contains function?

View 1 Replies View Related

Why My Query Is Slow When I Execute Full Text Search Service For Firts Time

Dec 18, 2007

first sorry my english,

when i execute a query for the first time whith full text service from visual studio, show me the error 'server not responding' and when i execute this query for second time works perfectly.

View 1 Replies View Related

SQL Server 2012 :: Query To Search Full-text Indexed Table And Return Results

Apr 19, 2014

I have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)

Query

DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);

SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');

SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result

[Code] ....

View 2 Replies View Related

SQL Query Timing Out

Jun 11, 2008

Hi
I have a query which executes in 1 second and returning 14 rows whenever I execute it through Management Studio but when I execute it from a web page I am getting a timeout message and the stack trace is pointing towards the line which calls the stored procedure. The query is quite complex and there quite a few joins on tables and views including one to a lookup table, whenever I take out join onto the look up table the web page runs ok without timing out. I join the Lookup table (Parts) using a Left Outer Join similar to below. The syntax of the Join is fine but I cant find why it would cause the query to time out when executed from a webpage but is fine when I execute it from Management Studio
 select *
from tables/view LEFT OUTER JOIN
Parts on Parts.PartNumber=tables/View.PartNumber
 I've tried recompiling the Parts table but it didnt make any difference.
 Any help would be much appreciated.
 Cheers,
Frankie

View 3 Replies View Related

Query Inexplicably Timing Out

Feb 29, 2008

I wrote a simple VB.NET utility program that uses a While...End While loop to go through all of the records in a table. Each time throgh that loop, an UPDATE command is assembled and assigned to the .CommandText property of an SqlCommand object. The command updates a record in another table by an ID field--based on the contents of the first table as well as the result of a lookup in another table. The update command is then executed each time within the loop via the .ExecuteNonQuery() method of the SqlCommand object. Again, it's important to point out that the update command updates only *one* record each time it is executed.

This program had been working fine until yesterday--when the query began timing out on the .ExecuteNonQuery() command. I have a counter within the loop and have been able to determine that the timeout occurs on the 3,359th time the query is called. The specific message is:


Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Re-running the program causes the same result at the same record.

I copied the query (whose ID updates the same 3,359th record) and executed it in SQL Server Management Studio; it timed out there, too. When I set the "Execution time-out" value to 0 (i.e., unlimited), the query ran for over an hour before I manually canceled it.

I can update all other tables in this database. I can also view (i.e., SELECT) the record that the update query is timing out on. I can also update any of the first 3,358 records in the SQLMS. However, the query continues to time out on the 3,359th record--and on any other record after that point.

The program was written on, and is running on, the SQL Server computer. The server has plenty of drive space. Both the database file and the log file for this database seem properly set to autogrow. I cycled the MSSQLSERVER service, but the query still times out at the same point. Rebooting the server did not resolve the problem, either. I don't see any errors in the Windows event logs.


What other methods can I use to try to resolve this problem?

View 9 Replies View Related

Timing Difference Between Query Analyzer And Profiler

Aug 1, 2014

We are trying to troubleshoot some website performance issues and found some queries taking 2 to 3 seconds when the request comes from the web, and captured by the Profiler.

The same queries, when run in the Query Analyzer take 0 seconds.

What could be the reasons for this difference, I mean why it takes 2 - 3 seconds shown by the Profiler, when it's 0 second in Analyzer?

View 3 Replies View Related

Server Trigger Timing Out Access Append Query

Oct 17, 2013

I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.

The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.

I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.

I have tested the trigger to do a simple task, and this works.

I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables

When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.

I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!

View 10 Replies View Related

Adding A Full Text Search Across Multiple Tables (with Text Fields)

Sep 7, 2007

Hi, i'm trying to do a full text search on my site to add a weighting score to my results.  I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields.  I figured since i needed to search the CategoryName field i would create an indexed view.  I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

View 2 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

Help W/ Stored Procedure? - Full-text Search: Search Query Of Normalized Data

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

Transact SQL :: Server Text Field Not Returning Full Text

Apr 21, 2015

I have a column in a table that has a type TEXT,when I pull the length of a row it returns 88222 but when I select from that column it dows not show all the text in the result set.

View 3 Replies View Related

Searching Database Text W/o Using Full-text Indexing

Mar 31, 2004

I am using the following plumbing code to search a database column for a keyword. I can't use full-test indexing so I came up w/ this work around. But It has many flaws so I'm looking for a better way. Thx in advance.

'Open sql connection
SqlConnection1.Open()

Dim datareader2 As SqlClient.SqlDataReader
datareader2 = cmdFindRowsWithKeyword.ExecuteReader
Dim strMsg As String
Dim intRowToFlag As Integer
Dim strRowsToGet As String
Dim strKeywordAsTyped As String
Dim strKeywordAllCaps As String
Dim strKeywordAllLower As String
Dim strKeywordFirstLetterCap As String
Dim FirstLetter As String

While datareader2.Read

intRowToFlag = datareader2(0).ToString
strMsg = datareader2(1).ToString

'Assign keyword as typed to variable
strKeywordAsTyped = txtSearchFor.Text
'Assign keyword as typed to variable then convert it to all uppercase
strKeywordAllCaps = txtSearchFor.Text
strKeywordAllCaps = strKeywordAllCaps.ToUpper
'Assign keyword as typed to variable then convert it to all lowercase
strKeywordAllLower = txtSearchFor.Text
strKeywordAllLower = strKeywordAllLower.ToLower
'Assign keyword as typed to variable then convert it so just the first letter is in uppercase
strKeywordFirstLetterCap = txtSearchFor.Text
FirstLetter = strKeywordFirstLetterCap.Chars(0)
FirstLetter = FirstLetter.ToUpper
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Remove(0, 1)
strKeywordFirstLetterCap = strKeywordFirstLetterCap.Insert(0, FirstLetter)

'If the string contains the keyword as typed in all caps all lowercase or w/ the 1st letter in caps then flag that row.
If strMsg.IndexOf(strKeywordAsTyped) <> -1 Or strMsg.IndexOf(strKeywordAllCaps) <> -1 Or strMsg.IndexOf(strKeywordAllLower) <> -1 Or strMsg.IndexOf(strKeywordFirstLetterCap) <> -1 Then

cmdFlagRowsWithKeyword.Parameters("@recid").Value = intRowToFlag
SqlConnection2.Open()
Dim datareader3 As SqlClient.SqlDataReader
datareader3 = cmdFlagRowsWithKeyword.ExecuteReader
datareader3.Close()
SqlConnection2.Close()

End If
End While
datareader2.Close()

View 2 Replies View Related

Link Server Doesn't Work Except Through Query Analyzer: MSSQL Freezing / Timing Out

Jan 7, 2007

Environment:Server1 (Local)OS Windows 2000 ServerSQL Server 2000Server2 (Remote)OS Windows 2003 ServerSQL Server 2000(Both with most recent service packs)Using Enterprise Manager, we have set up the Link Server (LINK_A) inthe Local Server 1 to connect to Server 2.The SQL we need to run is the following:INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxx;When we run this from the Query Analyzer, it completes with no problemsin a few seconds.Our problem:When we add the DTS Package as the ActiveX Script (VB Script) to theLocal Package, it times out at "obj_Conn.Execute str_Sql"Dim Sql, obj_ConnSet obj_Conn = CreateObject("ADODB.Connection")obj_Conn.Open XXXXobj_Conn.BeginTransstr_Sql = "INSERT INTO table1("str_Sql = str_Sql & "column1"str_Sql = str_Sql & ", column2"str_Sql = str_Sql & ")"str_Sql = str_Sql & " SELECT A.column1"str_Sql = str_Sql & ", A.column2"str_Sql = str_Sql & " FROM LINK_A.catalog_name.dbo.table2 AS A"str_Sql = str_Sql & " WHERE A.column1 0"str_Sql = str_Sql & ";"obj_Conn.Execute str_Sql----------------------------------------------------------When we make a Stored Procedure and run the following SQL, it freezes.INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxWe've also tried the following with the same results;INSERT INTO table1(column1,column2)SELECT A.column1, A.column2FROM [LINK_A].[catalog_name].[dbo].[table2] AS AWHERE A.column1 xxxxThe same thing happens when we try to run the "SELECT" by itself.SELECT TOP 1 @test=A.column1FROM LINK_A.catalog_name.dbo.table2 AS AWHERE A.column1 xxxxORDER BY A.column1What is going wrong here, and how do we need to change this so that itruns without timing out or freezing?

View 2 Replies View Related

Full Text And Text Columns

Oct 21, 2007

Can you not add a text column to a full text index?? If I change it to a nvarchar it works fine but if I change it to a text column it wont index. Anyone know how to fix this?

View 1 Replies View Related

Can Text Be Included In Full Text

Mar 29, 2007

I have a text column which my users use extensively for like queries. The table has 1.3 Millon rows and has seen som eperformance issue ie it does a table scan and blocks other processes.

I was wondering if anyone how to handle this coulmn can I create indexes or better can text column support full text indexing.

Anyone who can shed any ideas as what might be the best possible solution will be great

AdAnup

View 1 Replies View Related







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