CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005
Marco Dinatsoli
Marco Dinatsoli

Reputation: 10570

"Incorrect syntax near 'index'" when moving from SQL Server 2005 to 2008

I have sql server 2005 and I created this stored procedure

USE [HistoricalData]
GO
/****** Object:  StoredProcedure [dbo].[SummaryCampaignSupervisorInboundsCallsExtendedAvgTimeDataTest]    Script Date: 08/09/2014 14:02:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREAte Procedure [dbo].[SummaryCampaignSupervisorInboundsCallsExtendedAvgTimeDataTest](
    @VirtualCC Varchar(50),     @Role varchar(50),  
    @Language varchar(5),       @IdTimeZone Varchar(50),
    @Supervisor Varchar(5000),  @Campaign varchar(5000),
    @StartDateTime datetime,    @EndDateTime datetime,      
    @Slice Numeric(5) = 60
)
As

Set NoCount On

SELECT  @StartDateTime = dbo.GetInvRealDate(@IdTimeZone, @StartDateTime),
    @EndDateTime = dbo.GetInvRealDate(@IdTimeZone , @EndDateTime)



    SELECT  convert(varchar(10), dbo.GetRealDate(@IdTimeZone ,convert(datetime, Date + ' ' + ICS.Hour)), 120) 'Date',
        dbo.CrossTimeFrameFromDate( dbo.GetRealDate(@IdTimeZone ,convert(datetime, dateadd(minute, AvgTimeSlice, convert(datetime, Date )))), @Slice) 'Slice',
        Campaign,   

        Sum(TotalInboundAbandoned) 'TotalInboundAbandoned', 
        Sum(TotalInBoundACDCalls)'TotalQueuedCalls',
        IsNull(Sum(TotalInBoundControlAgentTime) / dbo.IsZero(Sum(TotalInBoundACDCalls),Null),0) 'AvgACDTime'

     FROM dbo.InteractionsCampaignSummary ICS (Nolock index = I_InteractionsCampaignSummary_Repo_01)
        WHERE VirtualCC like    CASE    WHEN RTrim(LTrim(@VirtualCC)) = '' THEN '%'
                    ELSE @VirtualCC 
                END
       AND Campaign like    CASE    WHEN RTrim(LTrim(@Campaign)) = '' THEN '%'
                    ELSE @Campaign 
                END
       AND TYPE = 'TAPI'
       AND convert(datetime, ICS.Date + ' ' + ICS.Hour ) >= @StartDateTime
       AND convert(datetime, ICS.Date + ' ' + ICS.Hour ) < @EndDateTime
     GROUP BY   convert(varchar(10), dbo.GetRealDate(@IdTimeZone ,convert(datetime, Date + ' ' + ICS.Hour)), 120), 
            dbo.CrossTimeFrameFromDate( dbo.GetRealDate(@IdTimeZone ,convert(datetime, dateadd(minute, AvgTimeSlice, convert(datetime, Date )))), @Slice),
            Campaign
    HAVING IsNull(Sum(TotalInBound),0)  > 0
     ORDER BY   Campaign,
            convert(varchar(10), dbo.GetRealDate(@IdTimeZone ,convert(datetime, Date + ' ' + ICS.Hour)), 120),
            dbo.CrossTimeFrameFromDate( dbo.GetRealDate(@IdTimeZone ,convert(datetime, dateadd(minute, AvgTimeSlice, convert(datetime, Date )))), @Slice)

when I tried to create this stored procedure on sql server 2008 I got this error

Msg 1018, Level 15, State 1, Procedure SummaryCampaignSupervisorInboundsCallsExtendedAvgTimeDataTest, Line 25

Incorrect syntax near 'index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Help please

I can give you any information you need

Upvotes: 1

Views: 16256

Answers (2)

Pavan Kate
Pavan Kate

Reputation: 94

FROM dbo.InteractionsCampaignSummary ICS
WITH (Nolock, index = I_InteractionsCampaignSummary_Repo_01)

Also try to keep syntax matching with oracle documentation logs, that gives you changes, You can directly consier changes from there while migrating from one sql version to another, and search for whats new in oracle pages.. Hope this helps...

Upvotes: 0

jpw
jpw

Reputation: 44881

The documentation states this:

Important:
Omitting the WITH keyword is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

And also this:

Important:
Separating hints by spaces rather than commas is a deprecated feature: This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.

So change this:

FROM dbo.InteractionsCampaignSummary ICS (Nolock index = I_InteractionsCampaignSummary_Repo_01)

to this:

FROM dbo.InteractionsCampaignSummary ICS WITH (Nolock, index = I_InteractionsCampaignSummary_Repo_01)

Upvotes: 2

Related Questions