Options

View SQL code by navision table name

nikeman77nikeman77 Member Posts: 517
edited 2012-09-25 in SQL Performance
hi folks,

i have no sql experience thus sorry if it sounds stupid to you.. when i run report that will export to excel.. i got an error message saying:

The Excel Buffer table cannot be changed because it is locked by another user.
Wait until the user is finished and then try again.

Comments

  • Options
    ara3nara3n Member Posts: 9,256
    The developer who wrote the made a mistake and didn't make the excel buffer as temporary. Contact your partner and ask them to fix it.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Options
    nikeman77nikeman77 Member Posts: 517
    ara3n,

    noted, thanks a lot.. but we need to find out the report which is locking the table before request to amend the report..
    :wink:
  • Options
    dansdans Member Posts: 148
    you could just ask them to check all custom, excel buffer related reports. it could be more than one report. it's a bug anyway, not a change request.
    Microsoft Certified IT Professional for Microsoft Dynamics NAV

    Just a happy frood who knows where his towel is
  • Options
    strykstryk Member Posts: 645
    As mentioned before: the current problem is that "Excel Buffer" is not declared as a temporary record.

    Anyway, if you want to learn more about blocks & deadlocks you might look into this!
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Options
    nikeman77nikeman77 Member Posts: 517
    stryk,

    I figure one of the easiest way was to run SQL Activity monitor and sort the expensive column in a circumstances when user feedback that there was performance & locking issues....
    what do you think of the idea ?
  • Options
    strykstryk Member Posts: 645
    Well, do you want to sit in from of the "SQL Activity Monitor" and wait for Blocks or Expensive queries?
    I'd rather establish processes which automatically record problems, so you could investigate them any time you want. Also, once you record data, you can "weight" or "priorize" problems, you could count them and compare results before/after changes etc..

    More about Blocks & Deadlocks:
    http://dynamicsuser.net/blogs/stryk/archive/tags/Locking/default.aspx

    More about "Expensive Queries":
    http://dynamicsuser.net/blogs/stryk/archive/tags/Indexes/default.aspx
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • Options
    davmac1davmac1 Member Posts: 1,283
    If you have the permissions, export all the recent changes to text, then do a search for table 370 and see if it is defined as temporary.

    I had to use this technique when some developer defined the dimension table as not temporary and did a deleteall.

    Sometimes a text search is the easiest way to find an error like this.
  • Options
    nikeman77nikeman77 Member Posts: 517
    davmac1,

    thanks!
    :)
  • Options
    davmac1davmac1 Member Posts: 1,283
    You are welcome - hope you find it.
Sign In or Register to comment.