Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
SQL Index Error

Answered SQL Index Error

  • Sunday, January 06, 2013 7:50 PM
     
     
    Sign In to Vote
    0

    I have database envisage. There is an index or criteria change error when I try to process something on client machine

    screenshot attached. Any SQL guru who could help how to fix this please

    Muhammad Mehdi

     

All Replies

  • Sunday, January 06, 2013 7:55 PM
    Moderator
     
     
    Sign In to Vote
    0
    How many rows are in the Hd_Call table and which indexes do you have in that table and also what is the SQL Server version?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, January 06, 2013 8:06 PM
     
     
    Sign In to Vote
    0

    Hi Naomi

    this is SQL server 2008 R2

    Number of Columns on HD_call table is 176675

    How I can check what indexes do I have on hd_call table

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 8:37 PM
     
     
    Sign In to Vote
    0

    Number of columns or number of rows?

    Can you try this:

    Select top 1 from HD_Call where Call is not null and Assignee=**** and Completed=0 and DateStart<=07 January 2013 8.40 (maybe this date, tomorrow, crash your query). Anyway, try just to undesrtand if the problem is in the criteria and so avoid one of the possibilite..


    • Edited by DIEGOCTN Sunday, January 06, 2013 8:37 PM
    •  
     
  • Sunday, January 06, 2013 8:40 PM
     
     
    Sign In to Vote
    0

    Before i run this query

    I will put new screenshot as all cleint machines are getting this error and criteria is not related to one user

    everyone getting error when they browse one part of database which has all open outstanding jobs

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 8:45 PM
     
     
    Sign In to Vote
    0

    Hello Muhammad,

    These are all not SQL Server system error messages, that are individual errors thrown by the application, so no one of us can guess, why the application show you the error message. You should better contact the hotline / support of the software vendor of this application.

    Olaf Helper

    Blog Xing

     
  • Sunday, January 06, 2013 8:50 PM
     
     
    Sign In to Vote
    0

    No software vendor mate. forum is the only support for me for database issues.

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 8:55 PM
    Moderator
     
     
    Sign In to Vote
    0
    In SSMS right click on the table and chose script as create and post this script here. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, January 06, 2013 8:55 PM
     
     
    Sign In to Vote
    0
    But someone created the application "Envisage Support Centre": http://envisage-support-centre.software.informer.com/ , there is also a forum available, may you can get there a better answer.

    Olaf Helper

    Blog Xing

     
  • Sunday, January 06, 2013 9:03 PM
     
     
    Sign In to Vote
    0

    I will do it into two portion as the characters are more than 6000

    ___________________________________________________

    USE [Envisage]
    GO

    /****** Object:  Table [dbo].[HD_Call]    Script Date: 01/07/2013 10:00:55 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[HD_Call](
     [Call] [varchar](15) NOT NULL,
     [Customer] [varchar](15) NULL,
     [Equipment] [varchar](20) NULL,
     [Contact] [varchar](50) NULL,
     [CallType] [varchar](15) NULL,
     [Status] [varchar](15) NULL,
     [Priority] [varchar](15) NULL,
     [Product] [varchar](15) NULL,
     [Category] [varchar](15) NULL,
     [callDriver] [varchar](15) NULL,
     [Assignee] [varchar](15) NULL,
     [LoggedBy] [varchar](15) NULL,
     [SourceType] [varchar](15) NULL,
     [EscalationCode] [varchar](15) NULL,
     [EscalationStep] [varchar](15) NULL,
     [EscalationPoint] [datetime] NULL,
     [Escalated] [bit] NOT NULL,
     [EscalateActive] [bit] NOT NULL,
     [Store] [varchar](15) NULL,
     [OrderNo] [varchar](20) NULL,
     [Reference] [varchar](15) NULL,
     [DateRaised] [datetime] NULL,
     [DateStart] [datetime] NULL,
     [DateAlert] [datetime] NULL,
     [DateDue] [datetime] NULL,
     [DateTotal] [datetime] NULL,
     [DateCompleted] [datetime] NULL,
     [Completed] [bit] NOT NULL,
     [PercentComplete] [smallint] NULL,
     [Request] [text] NULL,
     [ShortRequest] [varchar](60) NULL,
     [Action] [text] NULL,
     [ShortAction] [varchar](60) NULL,
     [KeyWords] [varchar](255) NULL,
     [KnowledgeFlag] [bit] NOT NULL,
     [KnowledgeID] [varchar](15) NULL,
     [VendorLog] [varchar](50) NULL,
     [Brand] [varchar](15) NULL,
     [Model] [varchar](15) NULL,
     [ProductType] [varchar](15) NULL,
     [CustGroup] [varchar](15) NULL,
     [CustAltGroup] [varchar](15) NULL,
     [EquipGroup] [varchar](15) NULL,
     [EquipAltGroup] [varchar](15) NULL,
     [CyclicCall] [bit] NOT NULL,
     [CyclicMonths] [bit] NOT NULL,
     [CyclicNumber] [smallint] NULL,
     [CustName] [varchar](50) NULL,
     [InvoiceText] [varchar](50) NULL,
     [QuoteFlag] [bit] NOT NULL,
     [QuoteEstimate] [bit] NOT NULL,
     [QuoteDate] [datetime] NULL,
     [QuotePrinted] [bit] NOT NULL,
     [QuoteAccept] [bit] NOT NULL,
     [BillingRunFlag] [bit] NOT NULL,
     [PrintFlag1] [bit] NOT NULL,
     [PrintFlag2] [bit] NOT NULL,
     [SeenFlag] [bit] NOT NULL,
     [SysDateChanged] [datetime] NULL,
     [SysLocal] [bit] NOT NULL,
     [HasSubCall] [bit] NOT NULL,
     [DateEscalated] [datetime] NULL,
     [BulletinBoard] [bit] NOT NULL,
     [AssigneeGroup] [varchar](15) NULL,
     [CCRReason] [text] NULL,
     [CCRAReason] [text] NULL,
     [CCRAffects] [text] NULL,
     [CCCExpected] [text] NULL,
     [CCCActual] [text] NULL,
     [CCCDate] [datetime] NULL,
     [CCCADate] [datetime] NULL,
     [TechDetails] [text] NULL,
     [SalesRegion] [varchar](50) NULL,
     [ShortTechDetails] [varchar](60) NULL,
     [PromisedDate] [datetime] NULL,
     [ResolutionCode] [varchar](50) NULL,
     [Resolved] [bit] NOT NULL,
     [ForecastEffort] [int] NULL,
     [ActualEffort] [int] NULL,
     [FinalAction] [text] NULL,
     [IsChargeable] [bit] NOT NULL,
     [IsNavisionJob] [bit] NOT NULL,
     [NavisionTransferDate] [datetime] NULL,
     [IsQuickCall] [bit] NOT NULL,
     [ITILStatus] [varchar](1) NULL,
     [blackberrySent] [bit] NOT NULL,
     CONSTRAINT [pk_HD_Call1] PRIMARY KEY CLUSTERED
    (
     [Call] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 9:04 PM
     
     
    Sign In to Vote
    0


    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_17]  DEFAULT (0) FOR [Escalated]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_18]  DEFAULT (0) FOR [EscalateActive]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_19]  DEFAULT (0) FOR [Completed]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_20]  DEFAULT (0) FOR [KnowledgeFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_21]  DEFAULT (0) FOR [CyclicCall]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_22]  DEFAULT (0) FOR [CyclicMonths]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_23]  DEFAULT (0) FOR [QuoteFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_24]  DEFAULT (0) FOR [QuoteEstimate]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_25]  DEFAULT (0) FOR [QuotePrinted]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_26]  DEFAULT (0) FOR [QuoteAccept]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_27]  DEFAULT (0) FOR [BillingRunFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_28]  DEFAULT (0) FOR [PrintFlag1]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_29]  DEFAULT (0) FOR [PrintFlag2]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_30]  DEFAULT (0) FOR [SeenFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_31]  DEFAULT (0) FOR [SysLocal]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_32]  DEFAULT (0) FOR [HasSubCall]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF__hd_call__Bulleti__7F36D027]  DEFAULT (0) FOR [BulletinBoard]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_Resolved]  DEFAULT (0) FOR [Resolved]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_IsChargeable]  DEFAULT (0) FOR [IsChargeable]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_IsNavisionJob]  DEFAULT (0) FOR [IsNavisionJob]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_IsQuickCall]  DEFAULT (0) FOR [IsQuickCall]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_blackberrySent]  DEFAULT (0) FOR [blackberrySent]
    GO

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 9:15 PM
    Moderator
     
     
    Sign In to Vote
    0

    I also need your SQL Server version and the indexes. The above doesn't show indexes, so go to Tools\Options\SQL Server Object Explorer (last item) and then scroll down and set Script indexes to true and also Script foreign keys to true.

    Or put output of 

    execute sp_helpindex 'HD_Call'

    BTW, your Call column doesn't allow NULL, so this condition is not needed Call IS NOT NULL

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Sunday, January 06, 2013 9:32 PM
     
     
    Sign In to Vote
    0

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 9:33 PM
     
     
    Sign In to Vote
    0

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 9:33 PM
     
     
    Sign In to Vote
    0

    USE [Envisage]
    GO

    /****** Object:  Table [dbo].[HD_Call]    Script Date: 01/07/2013 10:31:01 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [dbo].[HD_Call](
     [Call] [varchar](15) NOT NULL,
     [Customer] [varchar](15) NULL,
     [Equipment] [varchar](20) NULL,
     [Contact] [varchar](50) NULL,
     [CallType] [varchar](15) NULL,
     [Status] [varchar](15) NULL,
     [Priority] [varchar](15) NULL,
     [Product] [varchar](15) NULL,
     [Category] [varchar](15) NULL,
     [callDriver] [varchar](15) NULL,
     [Assignee] [varchar](15) NULL,
     [LoggedBy] [varchar](15) NULL,
     [SourceType] [varchar](15) NULL,
     [EscalationCode] [varchar](15) NULL,
     [EscalationStep] [varchar](15) NULL,
     [EscalationPoint] [datetime] NULL,
     [Escalated] [bit] NOT NULL,
     [EscalateActive] [bit] NOT NULL,
     [Store] [varchar](15) NULL,
     [OrderNo] [varchar](20) NULL,
     [Reference] [varchar](15) NULL,
     [DateRaised] [datetime] NULL,
     [DateStart] [datetime] NULL,
     [DateAlert] [datetime] NULL,
     [DateDue] [datetime] NULL,
     [DateTotal] [datetime] NULL,
     [DateCompleted] [datetime] NULL,
     [Completed] [bit] NOT NULL,
     [PercentComplete] [smallint] NULL,
     [Request] [text] NULL,
     [ShortRequest] [varchar](60) NULL,
     [Action] [text] NULL,
     [ShortAction] [varchar](60) NULL,
     [KeyWords] [varchar](255) NULL,
     [KnowledgeFlag] [bit] NOT NULL,
     [KnowledgeID] [varchar](15) NULL,
     [VendorLog] [varchar](50) NULL,
     [Brand] [varchar](15) NULL,
     [Model] [varchar](15) NULL,
     [ProductType] [varchar](15) NULL,
     [CustGroup] [varchar](15) NULL,
     [CustAltGroup] [varchar](15) NULL,
     [EquipGroup] [varchar](15) NULL,
     [EquipAltGroup] [varchar](15) NULL,
     [CyclicCall] [bit] NOT NULL,
     [CyclicMonths] [bit] NOT NULL,
     [CyclicNumber] [smallint] NULL,
     [CustName] [varchar](50) NULL,
     [InvoiceText] [varchar](50) NULL,
     [QuoteFlag] [bit] NOT NULL,
     [QuoteEstimate] [bit] NOT NULL,
     [QuoteDate] [datetime] NULL,
     [QuotePrinted] [bit] NOT NULL,
     [QuoteAccept] [bit] NOT NULL,
     [BillingRunFlag] [bit] NOT NULL,
     [PrintFlag1] [bit] NOT NULL,
     [PrintFlag2] [bit] NOT NULL,
     [SeenFlag] [bit] NOT NULL,
     [SysDateChanged] [datetime] NULL,
     [SysLocal] [bit] NOT NULL,
     [HasSubCall] [bit] NOT NULL,
     [DateEscalated] [datetime] NULL,
     [BulletinBoard] [bit] NOT NULL,
     [AssigneeGroup] [varchar](15) NULL,
     [CCRReason] [text] NULL,
     [CCRAReason] [text] NULL,
     [CCRAffects] [text] NULL,
     [CCCExpected] [text] NULL,
     [CCCActual] [text] NULL,
     [CCCDate] [datetime] NULL,
     [CCCADate] [datetime] NULL,
     [TechDetails] [text] NULL,
     [SalesRegion] [varchar](50) NULL,
     [ShortTechDetails] [varchar](60) NULL,
     [PromisedDate] [datetime] NULL,
     [ResolutionCode] [varchar](50) NULL,
     [Resolved] [bit] NOT NULL,
     [ForecastEffort] [int] NULL,
     [ActualEffort] [int] NULL,
     [FinalAction] [text] NULL,
     [IsChargeable] [bit] NOT NULL,
     [IsNavisionJob] [bit] NOT NULL,
     [NavisionTransferDate] [datetime] NULL,
     [IsQuickCall] [bit] NOT NULL,
     [ITILStatus] [varchar](1) NULL,
     [blackberrySent] [bit] NOT NULL,
     CONSTRAINT [pk_HD_Call1] PRIMARY KEY CLUSTERED
    (
     [Call] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO


    USE [Envisage]
    /****** Object:  Index [Assignee]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [Assignee] ON [dbo].[HD_Call]
    (
     [Assignee] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [CallType]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [CallType] ON [dbo].[HD_Call]
    (
     [CallType] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [Customer]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [Customer] ON [dbo].[HD_Call]
    (
     [Customer] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [Equipment]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [Equipment] ON [dbo].[HD_Call]
    (
     [Equipment] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [IDX_ITILStatus]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [IDX_ITILStatus] ON [dbo].[HD_Call]
    (
     [ITILStatus] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [LoggedBy]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [LoggedBy] ON [dbo].[HD_Call]
    (
     [LoggedBy] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [Priority]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [Priority] ON [dbo].[HD_Call]
    (
     [Priority] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [Product]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [Product] ON [dbo].[HD_Call]
    (
     [Product] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO


    USE [Envisage]
    /****** Object:  Index [Status]    Script Date: 01/07/2013 10:31:01 ******/
    CREATE NONCLUSTERED INDEX [Status] ON [dbo].[HD_Call]
    (
     [Status] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_17]  DEFAULT (0) FOR [Escalated]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_18]  DEFAULT (0) FOR [EscalateActive]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_19]  DEFAULT (0) FOR [Completed]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_20]  DEFAULT (0) FOR [KnowledgeFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_21]  DEFAULT (0) FOR [CyclicCall]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_22]  DEFAULT (0) FOR [CyclicMonths]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_23]  DEFAULT (0) FOR [QuoteFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_24]  DEFAULT (0) FOR [QuoteEstimate]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_25]  DEFAULT (0) FOR [QuotePrinted]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_26]  DEFAULT (0) FOR [QuoteAccept]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_27]  DEFAULT (0) FOR [BillingRunFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_28]  DEFAULT (0) FOR [PrintFlag1]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_29]  DEFAULT (0) FOR [PrintFlag2]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_30]  DEFAULT (0) FOR [SeenFlag]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_31]  DEFAULT (0) FOR [SysLocal]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_32]  DEFAULT (0) FOR [HasSubCall]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF__hd_call__Bulleti__7F36D027]  DEFAULT (0) FOR [BulletinBoard]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_Resolved]  DEFAULT (0) FOR [Resolved]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_IsChargeable]  DEFAULT (0) FOR [IsChargeable]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_IsNavisionJob]  DEFAULT (0) FOR [IsNavisionJob]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_IsQuickCall]  DEFAULT (0) FOR [IsQuickCall]
    GO

    ALTER TABLE [dbo].[HD_Call] ADD  CONSTRAINT [DF_HD_Call_blackberrySent]  DEFAULT (0) FOR [blackberrySent]
    GO

     

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 9:42 PM
    Moderator
     
     
    Sign In to Vote
    0

    Since you're using SQL 2008 R2, I suggest the following index

    CREATE NONCLUSTERED INDEX [Assignee] ON [dbo].[HD_Call] 
    (
     [Assignee] ASC, Date_Start ASC) WHERE Completed = 0 INCLUDE (Call)
    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO

    This filtered index will cover your query. 

    Also, if you run the select statement and include execution plan, it may suggest you an index. Post that suggested index definition.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog



     
  • Sunday, January 06, 2013 9:45 PM
     
     
    Sign In to Vote
    0

    I get an error after executing statement

    Msg 156, Level 15, State 1, Line 3

    Incorrect syntax near the keyword 'WHERE'.

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 9:54 PM
    Moderator
     
     
    Sign In to Vote
    0

    Try moving WHERE after INCLUDE columns. See

    http://msdn.microsoft.com/en-us/library/ms188783.aspx

    CREATE NONCLUSTERED INDEX [Assignee] ON [dbo].[HD_Call] 
    (
     [Assignee] ASC, Date_Start ASC) INCLUDE (Call)

    WHERE Completed = 0 

    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    GO

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


     
  • Sunday, January 06, 2013 9:59 PM
     
     
    Sign In to Vote
    0

    I changed statement and error change to include from where

    Msg 102, Level 15, State 1, Line 5

    Incorrect syntax near 'INCLUDE'.

    ____________________________

    use

    Envisage

    CREATE

    NONCLUSTERED INDEX [Assignee] ON [dbo].[HD_Call]

    (

    [Assignee]

    ASC, Date_Start ASC INCLUDE (Call)WHERE Completed = 0


    )

    WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]


    GO

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 10:10 PM
     
     
    Sign In to Vote
    0

    That's your version of SSMS. We want to see the output from "SELECT @@version".

    Before you start to create indexes all over town, you need to check for blocking. There could be an oprhaned transaction that causes this pain. Start one of these long-running operations, and while its running, run sp_who from a query window. If you see a value in the Blk column, that spid is blocking the spid on that row. In that case you need to investigate what the blocker is doing, and somehow terminate that activity.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Sunday, January 06, 2013 10:24 PM
     
     
    Sign In to Vote
    0

    the select @@version

    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64)   Jun 11 2012 16:41:53   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    I Ran sp_who

    there are two rows showing value into blk column

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 10:31 PM
    Moderator
     
     
    Sign In to Vote
    0
    I posted you the command and corrected my original message also. Look closer at the syntax. The column names for the index should be in ().

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, January 06, 2013 10:31 PM
     
     
    Sign In to Vote
    0

    So forget all about indexes for the moment. You have a blocking problem. The blocking spid is running DBCC, but it is in its turn blocked by spid 53.

    You need to unwind that blocking chain, until you arrive at a spid that is not blocked. Then you need to find out where that spid comes from and what is is doing.

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Sunday, January 06, 2013 10:38 PM
     
     
    Sign In to Vote
    0

    What I would recommend is that you take one of the queries you are having problems with and run that query in SSMS directly.  You want to include Actual Execute Plan for the query.

    Once you have that - you can post the actual execute plan here and we can use that to help identify what indexes would help for those queries.  My suspicion is that your problem is the DateStart column.  This query has to perform a table scan to filter on that column - and even if you add a query may not help because it would still need to perform a scan.

    If you can limit the query by including a start and end date ranges - then an index may help.

    Jeff Williams

     
  • Sunday, January 06, 2013 10:44 PM
     
     
    Sign In to Vote
    0

    I restarted the Services for SQL Server

    Run sp_who command and now the number 72 and 53 is gone from blk column

    the error is gone as well

    I can browse the jobs

    this might not be permanant fixed

    does it call anything special what this might caused it as it looks like there was blockage not letteing clients to browse the jobs

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 10:47 PM
    Moderator
     
     Answered
    Sign In to Vote
    0
    You also may want to install SP2 for your SQL Server version to be on the latest SP.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, January 06, 2013 10:51 PM
     
     
    Sign In to Vote
    0

    added into to do list

    do you think might caused by bug into SQL

    How I can avoid it in future

    Muhammad Mehdi

     
  • Sunday, January 06, 2013 11:02 PM
     
     Answered
    Sign In to Vote
    0

    added into to do list

    do you think might caused by bug into SQL

    In one word: NO!

    How I can avoid it in future


    No idea. You restarted SQL Server, and thereby you also lost all possibilities to investigate what caused the blocking to happen. There are a number of possibilities:

    1) Someone started a transaction in SSMS, but never committed.
    2) The application experienced a timeout, and then neglected to rollback an outstanding transction.
    3) The application was running a long-running update operation.
    4) Someone was manually running a long-running query.

    And the list goes on. But it does not include bugs in SQL Server. (But the there could certainly be bugs in Envisage.)

    I would not restart SQL Server in a situation like this, since the blocker may be a process that is performing a legit operation, and killing that process could lead to data loss or data inconsitency if the application is poorly designed. (Mind you, I am completely unfamiliar with Envisage.)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
     
  • Sunday, January 06, 2013 11:08 PM
     
     
    Sign In to Vote
    0

    thanks for everyone

    I will carry on with this forum if happend again to investigate further the cause of it

    Muhammad Mehdi