View SQL code by navision table name

nikeman77 · 2012-08-03

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 · 2012-08-03

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 · 2012-08-03

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 · 2012-08-03

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 · 2012-08-07

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 · 2012-09-23

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 · 2012-09-23

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 · 2012-09-24

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 · 2012-09-25

davmac1,

thanks!

davmac1 · 2012-09-25

You are welcome - hope you find it.

View SQL code by navision table name

Comments

Categories