View SQL code by navision table name

nikeman77

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.

ara3n

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.

nikeman77

ara3n,

noted, thanks a lot.. but we need to find out the report which is locking the table before request to amend the report..

dans

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.

stryk

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!

nikeman77

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 ?

stryk

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

davmac1

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.

nikeman77

davmac1,

thanks!

davmac1

You are welcome - hope you find it.