I am trying to create a new full text index on some table and getting the follwing error
"The Microsoft Search service
cannot be administered under the present user account"
i have already checked that Microsoft Search service is properly installed with the server version of SQL Server and this is running under the system account
could anyone plz comeout with the solution to this problem]
thanks in advance
bhavya
Looks like SQL decided to make an index, is this typical? Where can I look at the parameters for this? Thanks
From SQL Profiler:
create statistics hind_239_3 on [calljrnl] ( [cj_created_by] )
Hi,
like I said in other posts, I'm new to sql server, worked with informix for a long time. Right now I got into an "argument" with the present "dba", the indexes for all the tables in the database are being rebuilt, he wanted to start a monthly process, I told him that he can't do processing because when an index is built the table is locked, now, since I'm new at sql server I would like to know from the experts..... can you run process in sql server against tables when:
- indexes are being created
- the structure of a table is being modified
- the database is being shrunk
If we had been talking about informix then I would have probably slapped him if he tried to "teach" me about how things work, I'm pretty sure its the same in SQL but I think its better to have complete assureness of what I'm talking about before the slapping starts :)
Thanks in advance
Luis Torres
I have 2 questions,
1. I am trying to create an index, which is running for a long time. Is there an option to know the progress so far or how much more time it is going to take?
2. Is there anyway to find out how much space is required to rebuild an index?
------------------------
I think, therefore I am - Rene Descartes
Hi -
I could not find an SQL script, which would generate index creation DDL for a specific table/index. Query Analyzer does it, but I need to do it programmatically. Does anyone have a working script?
Thank you.
Does Microsoft SQL Server keep a record of an index creation date andlast modified date in a system table somewhere?
View 2 Replies View RelatedI was reading this blog below about Indexed Views and needed some clarification.
When the author choose to create the unique clustered index he excluded the PER.BusinessEntityID columns and only included, (CustomerID, SalesOrderID, ProductID).
Why didn't the PER.BusinessEntityID get added?
[URL] ....
CREATE VIEW Sales.vSalesSummaryCustomerProduct
WITH SCHEMABINDING
AS
SELECT CUST.CustomerID ,
SOH.SalesOrderID ,
SOD.ProductID ,
SUM(SOD.OrderQty) AS TotalOrderQty ,
[Code] .....
Hi
I'm trying to use a user defined alias type in a table creation, and everything works as expected, right until the moment where I rightclick on [Indexes] and click [new index] to create a new index.
Then I get this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
Object reference not set to an instance of an object. (SqlManagerUI)
------------------------------
BUTTONS:
OK
------------------------------
This is the script I use to create the table:
exec sp_executeSql N'CREATE SCHEMA [TestSchema] AUTHORIZATION [dbo]'
GO
CREATE TYPE [TestSchema].[type_TestType]
FROM uniqueidentifier ;
GO
Create table [TestSchema].[Periode3]
(
Periode3_UID uniqueidentifier NOT NULL ROWGUIDCOL,
rowVersion timestamp NULL,
owner uniqueidentifier not null,
validFrom datetime
NOT NULL CONSTRAINT
DF_Periode_modified3 DEFAULT getdate()
);
Alter table [TestSchema].[Periode3]
add PeriodeType [TestSchema].[type_TestType];
now if I replace the 2 last lines with:
Alter table [TestSchema].[Periode3]
add PeriodeType uniqueidentifier;
everything works as expected.
Where did I go wrong ?
regards
Jens Chr
What do I have to do to get this to work?
C:>aspnet_regsql.exe -A m -E
Start adding the following features:Membership
...............An error has occurred. Details of the exception:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under thedefault settings SQL Server does not allow remote connections. (provider: NamedPipes Provider, error: 40 - Could not open a connection to SQL Server)
Unable to connect to SQL Server database.
I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?
View 2 Replies View RelatedCan anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country.
Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds?
Thanks ...
Hi,
Does anyone have any good tips for reducing the amount of disk space required for new clustered index creation.
I have just finished building a rather large table (48gbs) and durring the clustered index creation I came very close to running out of disk space.
What I am already doing:
1.Recovery Model simple
2. index padding 0
3. fillfactor 0
Any help would be appreciated
daLoonster
I am using Full Text Index to index emails stored in BLOB column in a table. Index process parses stored emails, and, if there is one or more files attached to the email these documents get indexed too. In result when I'm querying the full text index for a word or phrase I am getting reference to the email containing the word of phrase if interest if the word was used in the email body OR if it was used in any document attached to the email.
How to distinguish in a Full Text query that the result came from an embedded document rather than from "main" document? Or if that's not possible how to disable indexing of embedded documents?
My goal is either to give a user an option if he or she wants to search emails (email bodies only) OR emails AND documents attached to them, or at least clearly indicate in the returned result the real source where the word or phrase has been found.
Script they use to generate indexes in SQL 2005.
I have 2 databases on a separate instance. I want to script out all indexes from database1 then execute it on database2.
How to accomplish this task efficiently.
is there a setting that will ebnable uniform extent allocation uponcreation of index/table by default ?if there isn't any default setting can you code it in?thanks,Doron
View 1 Replies View RelatedI have a Performance issue in ssrs report , query takes execution time 1 to 2 min. When I add the non clustered index for one of the table columns which takes only 2 sec to execute the query. But as from my environment I couldn't able to add the index due to these changes will effect for all reports. Without adding indexes which takes 2 min to run that report. My question is:
Is there any way to add the non-clustered index on the report itself like if a non clustered index exists "execute the query" else create a non clustered  index and run that report with newly created index in report itself like a temp table concept. How to add this concept in this below query:Final Query without non clustered index, takes 2 min to execution time ---
with cte1
( MovementDate,
MovementTypeDesc,
CDCNumber,
MovementTime,
LocationReportMovement,
OtherLocationCode,
[code]....
We are running SQL Server 2014 Enterprise Edition (64-Bit) on Windows 2012 R2 Standard (64-Bit).
1. When to create indexes, before or after data is added? Please address Clustered and Non-Clustered Indexes.
2. To move indexes to it's own filegroup, is it best to create the NON-Clustered Indexes on the separate filegroup with code similar to the example below?
CREATE NONCLUSTERED INDEX IX_Employee_OrganizationLevel_OrganizationNode
ON HumanResources.Employee (OrganizationLevel, OrganizationNode)
WITH (DROP_EXISTING = ON)
ON TransactionsFG1;
GO
I have read the following links that states that if you create the Clustered Index on a separate filegroup, it would also move the base table to that particular filegroup. (So I take it that you ONLY can move NON-CLustered Indexes to a separate filegroup.)
Placing Indexes on Filegroups:
[URL]
By default, indexes are stored in the same filegroup as the base table on which the index is created. A nonpartitioned clustered index and the base table always reside in the same filegroup. However, you can do the following:
• Create nonclustered indexes on a filegroup other than the filegroup of the base table.
Move an Existing Index to a Different Filegroup:
[URL]
Limitations and Restrictions
• If a table has a clustered index, moving the clustered index to a new filegroup moves the table to that filegroup.
• You cannot move indexes created using a UNIQUE or PRIMARY KEY constraint using Management Studio. To move these indexes use the CREATE INDEX statement with the (DROP_EXISTING=ON) option in Transact-SQL.
Quick question about the primary purpose of Full Text Index vs. Clustered Index.
The Full Text Index has the purpose of being accessible outside of the database so users can query the tables and columns it needs while being linked to other databases and tables within the SQL Server instance.
Is the Full Text Index similar to the global variable in programming where the scope lies outside of the tables and database itself?
I understand the clustered index is created for each table and most likely accessed within the user schema who have access to the database.
Is this correct?
I am kind of confused on why you would use full text index as opposed to clustered index.
Thank you
Goldmember
I would like to know how to config SQL Server 2000 Alert to be triggered by new file creation (i.e. txt file).
Maybe by monitoring windows 2000 log file (not sure)??
Any help or suggestion will be highly appreciated.
http://msdn2.microsoft.com/en-us/library/ms188917.aspx
This script keeps failing on line 22 on a ) but i do not see where it is erroring.
I put in USE DBNAME
and run it and it fails.
Any suggestions
The script under D:
D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the VIEW DATABASE STATE permission. This example specifies DB_ID as the first parameter without specifying a database name. An error will be generated if the current database has a compatibility level of 80 or lower. To resolve the error, replace DB_ID() with a valid database name. For more information about database compatibility levels, see sp_dbcmptlevel (Transact-SQL).
Copy Code
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
I have a job that runs nightly, rebuild index. job runs fine every night but every few weeks it fails.
USE msdb
GO
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@SortInTempdb = 'Y',
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'
This calls the Sp that does the Reindex. It fails at the update statistics with a very generic message. like " Command: UPDATE STATISTICS [xxxx_DB].[dbo].[xxxx_xxx] [_WA_Sys_00000007_49C3F6B7] [SQ... The step failed."
I suspect it has more error but this is all it is showing me when I right click on the job history. therefore, I updated the job step in the advance tab with log to a txt file. Am I on the right track or there is another way to see error some where else.
I looked at the logs but they didn't show any thing.
We have a new database with cdc enabled on all of its tables. This causes the index maintenance task to fail with following message:
"Executing the query "EXEC DBName.dbo.IndexDefrag_sp" failed with the following error:  "The unique index 'PK_TableName' on source table '[dbo].[TableName]' is used by Change Data Capture.  To alter or drop the index, you must first disable Change Data Capture on the table.  The transaction ended in the trigger. The batch has been aborted.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly" We would like to run the index maintenance without losing the cdc data. We plan on installing SP2 on SQL Server 2008 R2 soon, would that solve the issue? Disabling the cdc prior to index maintenance and then re-enabling back upon completion; would delete the data as I found in most discussions, but we would like to retain it.Â
Got this error trying to install the service pack on one of my boxes. Any help?
02/19/2007 11:19:59.890 Copy Engine: Error, unable to install MSP file: c:d758bb9ef6e34b2a3f5f9e0f043495cfHotFixSQLFilessqlrun_sql.msp
02/19/2007 11:19:59.937 The following exception occurred: Unable to install Windows Installer MSP file Date: 02/19/2007 11:19:59.937 File: depotsqlvaultstablesetupmainl1setupsqlsesqlsedllcopyengine.cpp Line: 800
02/19/2007 11:20:09.453 Watson: Param1 = Unknown
02/19/2007 11:20:09.500 Watson: Param2 = 0x2b18
02/19/2007 11:20:09.546 Watson: Param3 = Unknown
02/19/2007 11:20:09.578 Watson: Param4 = 0x0
02/19/2007 11:20:09.625 Watson: Param5 = copyengine.cpp@800
02/19/2007 11:20:09.671 Watson: Param6 = Unknown
02/19/2007 11:20:09.703 Watson: Param7 = SQL9
02/19/2007 11:20:09.750 Watson: Param8 = Hotfix@
02/19/2007 11:20:09.812 Watson: Param9 = x86
02/19/2007 11:20:09.921 Watson: Param10 = 3042
02/19/2007 11:20:10.062 Installed product: SQL9
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View Relatedhello
in Full Text Search
Are there method when add record in Field for properties "Full Text Index " , update catalogs ?
thanks
I am trying to enable full text index on all of my databases but notices that it is grayed out. Also the service Full Text Index service msftesql.exe is not installed. I have tried running the install again but it says nothing has changed on the machine so it just stops the install... Hope someone can help me.
View 4 Replies View RelatedWhat is a full-text index? Please be gentle. Sorry for not looking itup in the help or on the Web. Be kind.
View 1 Replies View RelatedCould Full Index option only be configured during installation? When Itry sp_fulltext_table on a table, I get the message that full text isnot enabled for the system.--sharif
View 1 Replies View RelatedI am having an issue creating full indexes on both instances of an ActiveActive SQL Server 2000 cluster. I get the following error when trying to create the catalog:
Access is denied to $SQL PATH$, or path is invalid. Full-text search was not installed properly.
Does anyone have any suggestions that I may use to create the indexes?
Recently converted from 7 to sql 2k.
Running NT 4.0, sp6a. and sql 2k. I have a DTS job that works just fine if i go to 'design view' and then 'execute' it. But if I schedule it, the following error appears.. My question is... why? Also I modified the path from the current below (denapp02cmc$cd01.txt to the local path of the server as well... still came up with the same problem. ?? TIA! ::
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from Results to denapp02cmc$cd01.txt Step DTSRun OnError: Copy Data from Results to denapp02cmc$cd01.txt Step, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied.
First of all I’m new to MS SQL, I did work with mySQL
Table name db (real db has 12 columns)
Id c1 c2 c3
1 tom john olga
2 tom john olga bleee
I enabled full text index on all columns
Problem when I do search like this:
SELECT * FROM db WHERE CONTAINS(*,'�tom� AND “john�')
It will return only one row (id 2) – I understand that the full text search does look only at one column at a time because it did not return row #1
Anyway I thought that I can add extra column c4 and when user enters new data it will save data from columns c1, c2, c3 to c4 (varchar(750)) and then I will do search only on c4 – this way it will work the way I want.
1) Is there any better way to do this?
2) How do I sort results by “rank� with SQL
I was wondering if anyone has successfully removed the Full Text Index service?
View 2 Replies View RelatedI have a table with 13,000,000 records. I want to generate a full-text index on one column (a varchar 2000). I am able to define the full-text index, but when I click on "Start Full population", there is virtually no activity (no disk activity, no CPU activity, very little to indicate anything is happening.
When I check the properties of the catalog, it shows 1 MB size and 0 records in the catalog. The status of the catalog is "idle" and the display in EM shows that the last full population occurred at (about) the time that I generated the population request. I have generated the request by using EM (right click on table) and through SQL Agent with the same result (no catalog generated).
I am running SQL 2000 (SP4) on Windows 2000 (SP4) with 4 GB RAM and sufficient disk space available. I have enabled the full-text service and verified that it is running (I have stopped and restarted it as well).
I have worked with Full Text indexes before and never had any kind of issue before. Any thoughts or suggestions would be welcome.
Regards,
hmscott
CREATE TABLE [OMBRE_AUDIT_LOG] (
[LOG_SEQ_NBR] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[APP_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USER_ID] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[USER_ORGANIZATION] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ACTION_START_DATE] [datetime] NOT NULL ,
[ACTION_END_DATE] [datetime] NULL ,
[ACTION_CODE] [int] NOT NULL ,
[VIEW_NAME] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[USER_DEF_TRACKING_NBR] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CMD_XML_STREAM] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[REC_CREATE] [datetime] NULL CONSTRAINT [DF_OMBRE_AUDIT_LOG_REC_CREATE] DEFAULT (getdate()),
[REC_UPDATE] [datetime] NULL ,
[ATTENTION] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REASON] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_OMBRE_AUDIT_LOG] PRIMARY KEY CLUSTERED
(
[LOG_SEQ_NBR]
)
)
GO