Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics GP (Archived)

Can not run SQL script before HITB tool reset

Posted on by 970

I get the following error when trying to check the sql script from page 6 of Historical Inventory Trail Balance Report under Installing the HITB Inventory reset tool.

 

Server: Msg 170, Level 15, State 1, Procedure EEUpdateAVGRCTSEQNM, Line 82

Line 82: Incorrect syntax near '('.

Server: Msg 170, Level 15, State 1, Procedure EEUpdateAVGRCTSEQNM, Line 96

Line 96: Incorrect syntax near '('.

Server: Msg 170, Level 15, State 1, Procedure EEUpdateAVGRCTSEQNM, Line 129

Line 129: Incorrect syntax near '('.

 

here is the script the error takes me to next to last line is where the first error code above takes me to

If OBJECT_ID('tempdb..#ItemList') IS NULL

   BEGIN

   CREATE TABLE #ItemList(

ITEMNMBR CHAR (31),

ADJUNITCOST NUMERIC (19,5),

NEXTRCTSEQNM INT)

   END

CREATE NONCLUSTERED INDEX [ItemList_1] ON [dbo].[#ItemList] 

(

[ITEMNMBR] ASC

)WITH (PAD_INDEX  = OFF, 

STATISTICS_NORECOMPUTE  = OFF, 

 

Any idea?

 

Thanks 

Rick

*This post is locked for comments

  • Rick13 Profile Picture
    Rick13 970 on at
    Re: Can not run SQL script before HITB tool reset

    Sorry thought it was relevant to running the HITB reset tools so did think another post was needed.  Next time I will know.

    As for my last question you are correct in that the answer is in the HITB FAQ.

    Thanks to all who help me through these issues.

  • David Bader Profile Picture
    David Bader on at
    Re: Can not run SQL script before HITB tool reset

    Hi Rick,

    Thank you for updating the post.

    WIth the Community Forums please understand that these online technical communities are an issue based service, one question/issue per post. This will provide crisp and accurate work and results by working on the most critical issue at a time. This service format also makes it easier to locate answers when you are searching for secific information or solutions.

    SInce your original issue was resolved and you have a new error message, please start a new thread so the community can provide answers and information for you specific to the new issue with a title containing the error message.

    However the HITB FAQ document may help resolve your errors. You can access the document at the following link on CustomerSource. It is in the Downloads section of the page.

    mbs.microsoft.com/.../mdgp10_hitb.htm

    Thanks and have a great day!

  • Rick13 Profile Picture
    Rick13 970 on at
    Re: Can not run SQL script before HITB tool reset

    Ok so I am back to HITB

    Comment out the above mentioned ran SQL worked great on to next step Run Data Check.

    Ran Data Check and get the following

    Error         Open layer exists in purchase receipts table where valuation method not stamped

    Now what do I do?

  • Rick13 Profile Picture
    Rick13 970 on at
    Re: Can not run SQL script before HITB tool reset

    Thank you Dave I will run the reset tool this weekend.  

    Also thank you Christina the missing ( was my first thought as well but I could not find where is was missing at.  Removing the code in 3 place as Dave suggested above did the trick.

    Rick

  • Verified answer
    David Bader Profile Picture
    David Bader on at
    Re: Can not run SQL script before HITB tool reset

    Rick,

    You will not experience any issue with HITB without these parameter. They are parameters used by SQL 2005 and SQL 2008 that are not readable by SQL 2000.

    Thanks,

    Dave

  • Rick13 Profile Picture
    Rick13 970 on at
    Re: Can not run SQL script before HITB tool reset

    Thanks for your reply.  Dave you are correct I am using 2000.  So I comment out the lines causing the error ran check and it now checks OK, I have not run on data yet.  Next question is commenting out these lines what issues am I going to have?

    Thanks

    Rick

  • David Bader Profile Picture
    David Bader on at
    Re: Can not run SQL script before HITB tool reset

    Hi Chistina,

    You are correct that the example is missing the close Paren at the end. Thank you for pointing that out.

    This was really just meant to be an example of the format to use when commenting out the section with the /* (start comment out) to */ (end comment out).

    Have a great day!

    Dave

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    Re: Can not run SQL script before HITB tool reset

    Hi-

    It also looks like you are missing  closed parentheses at the end?   (PAD_INDEX  = OFF,

    STATISTICS_NORECOMPUTE  = OFF,

    I would double check your cut/paste.

    take care,

    christina

  • Verified answer
    David Bader Profile Picture
    David Bader on at
    Re: Can not run SQL script before HITB tool reset

    Hi Rick,

    My guess is you are using SQL 2000 when running the scripts. Is that correct?  If so there is a limitation in SQL 2000 and it will not run the index attributes in the script.

    In order to resolve the error you will need to commented out the index attributes in 3 section using the format below. Once that is complete you should no longer receive any errors.

    /*

    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]

    */

    I hope this helps.

    Dave

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans