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.


  • ara3nara3n Member Posts: 9,250
    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

  • nikeman77nikeman77 Member Posts: 517

    noted, thanks a lot.. but we need to find out the report which is locking the table before request to amend the report..
  • 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
  • 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
  • nikeman77nikeman77 Member Posts: 517

    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 ?
  • 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:

    More about "Expensive Queries":
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • davmac1davmac1 Member Posts: 1,279
    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.
  • nikeman77nikeman77 Member Posts: 517

  • davmac1davmac1 Member Posts: 1,279
    You are welcome - hope you find it.
Sign In or Register to comment.