INDEX error while pushing data from distributer

Question

INDEX error while pushing data from distributer

rishidinesh942

SSC Journeyman

Points: 78
More actions
September 7, 2017 at 8:00 am

#328242

Hi
I am doing replication in SQL server 2014
While pushing data from subscriber i am getting this error
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. (Source: MSSQLServer, Error number: 1018)
And i checked out my SQL code where it occured.
my code is given below
please help me to sort out this problem
BEGIN
    DECLARE
        @intResult        INTEGER
    --    Always start with the default value
    SELECT @intResult = @intDefaultValue
    --    When the input is not null and is a number
    IF    @strToConvert IS NOT NULL
    AND    ISNUMERIC( @strToConvert ) = 1
    BEGIN
        --    Truncate the decimal portion?
        IF    CHARINDEX( '.', @strToConvert ) > 0
        AND    @blnTruncateDecimalPlaces = 1
            SELECT @strToConvert = LEFT( @strToConvert, CHARINDEX( '.', @strToConvert ) -1 )
        --    Space characters are not significant
        SELECT @strToConvert = LTRIM( RTRIM( @strToConvert ) )
        --    Check the specified length is not exceeded
        IF    CHARINDEX( '.', @strToConvert ) = 0
        AND    LEN( @strToConvert ) <= @intMaximumDigits
            SELECT @intResult = CAST( @strToConvert AS INTEGER )
    END
    --    Yield the result to the caller
    RETURN @intResult
END

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

rishidinesh942 - Thursday, September 7, 2017 8:00 AM
Hi
I am doing replication in SQL server 2014
While pushing data from subscriber i am getting this error
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. (Source: MSSQLServer, Error number: 1018)
And i checked out my SQL code where it occured.
my code is given below
please help me to sort out this problem
BEGIN
    DECLARE
        @intResult        INTEGER
    --    Always start with the default value
    SELECT @intResult = @intDefaultValue
    --    When the input is not null and is a number
    IF    @strToConvert IS NOT NULL
    AND    ISNUMERIC( @strToConvert ) = 1
    BEGIN
        --    Truncate the decimal portion?
        IF    CHARINDEX( '.', @strToConvert ) > 0
        AND    @blnTruncateDecimalPlaces = 1
            SELECT @strToConvert = LEFT( @strToConvert, CHARINDEX( '.', @strToConvert ) -1 )
        --    Space characters are not significant
        SELECT @strToConvert = LTRIM( RTRIM( @strToConvert ) )
        --    Check the specified length is not exceeded
        IF    CHARINDEX( '.', @strToConvert ) = 0
        AND    LEN( @strToConvert ) <= @intMaximumDigits
            SELECT @intResult = CAST( @strToConvert AS INTEGER )
    END
    --    Yield the result to the caller
    RETURN @intResult
END

I do not think that this code could generate the error you are seeing. The message suggests that you are using query hints in your code â€“ and there are none in the code provided.

rishidinesh942 SSC Journeyman Points: 78 More actions · Answer 2

Phil Parkin - Thursday, September 7, 2017 8:10 AM
rishidinesh942 - Thursday, September 7, 2017 8:00 AM
Hi
I am doing replication in SQL server 2014
While pushing data from subscriber i am getting this error
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. (Source: MSSQLServer, Error number: 1018)
And i checked out my SQL code where it occured.
my code is given below
please help me to sort out this problem
BEGIN
    DECLARE
        @intResult        INTEGER
    --    Always start with the default value
    SELECT @intResult = @intDefaultValue
    --    When the input is not null and is a number
    IF    @strToConvert IS NOT NULL
    AND    ISNUMERIC( @strToConvert ) = 1
    BEGIN
        --    Truncate the decimal portion?
        IF    CHARINDEX( '.', @strToConvert ) > 0
        AND    @blnTruncateDecimalPlaces = 1
            SELECT @strToConvert = LEFT( @strToConvert, CHARINDEX( '.', @strToConvert ) -1 )
        --    Space characters are not significant
        SELECT @strToConvert = LTRIM( RTRIM( @strToConvert ) )
        --    Check the specified length is not exceeded
        IF    CHARINDEX( '.', @strToConvert ) = 0
        AND    LEN( @strToConvert ) <= @intMaximumDigits
            SELECT @intResult = CAST( @strToConvert AS INTEGER )
    END
    --    Yield the result to the caller
    RETURN @intResult
END
I do not think that this code could generate the error you are seeing. The message suggests that you are using query hints in your code â€“ and there are none in the code provided.

Ok
Is there any thing went wrong while replication

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

rishidinesh942 - Thursday, September 7, 2017 8:16 AM
Ok
Is there any thing went wrong while replication

I thought that a replication error was the reason you asked the question?

rishidinesh942 SSC Journeyman Points: 78 More actions · Answer 4

Phil Parkin - Thursday, September 7, 2017 8:23 AM
rishidinesh942 - Thursday, September 7, 2017 8:16 AM
Ok
Is there any thing went wrong while replication
I thought that a replication error was the reason you asked the question?

yep

rishidinesh942 SSC Journeyman Points: 78 More actions · Answer 5

rishidinesh942 - Thursday, September 7, 2017 8:32 AM
Phil Parkin - Thursday, September 7, 2017 8:23 AM
rishidinesh942 - Thursday, September 7, 2017 8:16 AM
Ok
Is there any thing went wrong while replication
I thought that a replication error was the reason you asked the question?
yep

This is the detailed error i got ..

CREATE PROCEDURE [dbo].[spCD_GetFundTxnHistory]
@FundCode varchar(10),
@FromDate datetime
AS

If @FromDate Is Null SELECT @FromDate = Min(FundTxn_dtmCommitted) FROM tblFund_Transaction

SELECT FT.FundTxn_dtmCommitted,
FT.FundTxn_strCode,
FT.FundTxn_intSequence,
FT.FundTxn_curValue,
FT.FundTxn_intQty,
FTT.TxnType_strName,
FTT.TxnType_strRevExp,
FTT.TxnType_strDescription,
FT.FundTxn_strNote,
'' AS AssocFu
(Transaction sequence number: 0x0014CFF600000E8000F900000000, Command ID: 3308)

Error messages:
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. (Source: MSSQLServer, Error number: 1018)
Get help: http://help/1018
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. (Source: MSSQLServer, Error number: 1018)
Get help: http://help/1018
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. (Source: MSSQLServer, Error number: 1018)
Get help: http://help/1018

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

Please post all SQL code for procedure dbo.spCD_GetFundTxnHistory.

rishidinesh942 SSC Journeyman Points: 78 More actions · Answer 7

Phil Parkin - Thursday, September 7, 2017 8:43 AM
Please post all SQL code for procedure dbo.spCD_GetFundTxnHistory.

Here it is

AS

If @FromDate Is Null SELECT @FromDate = Min(FundTxn_dtmCommitted) FROM tblFund_Transaction

SELECT FT.FundTxn_dtmCommitted,
FT.FundTxn_strCode,
FT.FundTxn_intSequence,
FT.FundTxn_curValue,
FT.FundTxn_intQty,
FTT.TxnType_strName,
FTT.TxnType_strRevExp,
FTT.TxnType_strDescription,
FT.FundTxn_strNote,
'' AS AssocFundName,
CASE PA.POSA_strAdjustType
WHEN 'A' THEN 'O' --Adds or increases
WHEN 'O' THEN 'O' --Opening Balances
WHEN 'S' THEN 'I' --Subtracts or drops
WHEN 'D' THEN 'I' --POS Drops
WHEN 'I' THEN 'O' --POS Increases - shldnt be seen in FundTxn tables
END AS AssocFITDir,
P.POSS_strUserName AS AssocFITUserName,
PT.PayType_strDescription,
FT.AssocFund_strCode,
FT.POSS_intPOSSessionNumber,
FT.POSA_dtmAdjustmentDateTime,
FT.Deposit_intId
FROM tblFund_Transaction FT (INDEX = indFundTxn_DateCommitted)
INNER JOIN tblFundTxn_Type FTT
ON FT.TxnType_strCode = FTT.TxnType_strCode
LEFT JOIN tblPaymentType PT
ON PT.PayType_strType = FT.PayType_strType
LEFT JOIN tblPOS_SessAdjusts PA
ON FT.POSS_intPOSSessionNumber = PA.POSS_intPOSSessionNumber
AND FT.POSA_dtmAdjustmentDateTime = PA.POSA_dtmAdjustmentDateTime
LEFT JOIN tblPOS_Sess P
ON P.POSS_intPOSSessionNumber = PA.POSS_intPOSSessionNumber
WHERE FT.FundTarget_strCode = @FundCode
AND FT.FundTxn_dtmCommitted >= @FromDate
AND Not FT.POSS_intPOSSessionNumber Is Null

UNION ALL

SELECT FT.FundTxn_dtmCommitted,
FT.FundTxn_strCode,
FT.FundTxn_intSequence,
FT.FundTxn_curValue,
FT.FundTxn_intQty,
FTT.TxnType_strName,
FTT.TxnType_strRevExp,
FTT.TxnType_strDescription,
FT.FundTxn_strNote,
F.Fund_strName AS AssocFundName,
'' As AssocFITDir,
'' AS AssocFITUserName,
PT.PayType_strDescription,
FT.AssocFund_strCode,
FT.POSS_intPOSSessionNumber,
FT.POSA_dtmAdjustmentDateTime,
FT.Deposit_intId
FROM tblFund_Transaction FT (INDEX = indFundTxn_DateCommitted)
INNER JOIN tblFundTxn_Type FTT
ON FT.TxnType_strCode = FTT.TxnType_strCode
LEFT JOIN tblPaymentType PT
ON PT.PayType_strType = FT.PayType_strType
LEFT JOIN tblFund F
ON FT.AssocFund_strCode = F.Fund_strCode
WHERE FT.FundTarget_strCode = @FundCode
AND FT.FundTxn_dtmCommitted >= @FromDate
AND (
Not AssocFund_strCode Is Null
OR (IsNull(AssocFund_strCode,'') = '' AND IsNull(FT.POSS_intPOSSessionNumber,'') = '')
)

ORDER BY FT.FundTxn_dtmCommitted DESC,
FT.FundTxn_strCode, FT.FundTxn_intSequence

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

FROM tblFund_Transaction FT (INDEX = indFundTxn_DateCommitted)

In both places where that appears, you need a WITH added (or, probably better to take the index hint out)

FROM tblFund_Transaction FT WITH (INDEX = indFundTxn_DateCommitted)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

rishidinesh942 SSC Journeyman Points: 78 More actions · Answer 9

GilaMonster - Thursday, September 7, 2017 9:04 AM

FROM tblFund_Transaction FT (INDEX = indFundTxn_DateCommitted)

In both places where that appears, you need a WITH added (or, probably better

FROM tblFund_Transaction FT WITH (INDEX = indFundTxn_DateCommitted

rishidinesh942 SSC Journeyman Points: 78 More actions · Answer 10

rishidinesh942 - Thursday, September 7, 2017 9:17 AM

GilaMonster - Thursday, September 7, 2017 9:04 AM
FROM tblFund_Transaction FT (INDEX = indFundTxn_DateCommitted)
In both places where that appears, you need a WITH added (or, probably better
FROM tblFund_Transaction FT WITH (INDEX = indFundTxn_DateCommitted

Thank you Its Really helpfull