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.
The variable for the Excel Buffer table needs to be a temporary variable, and then you should never get that message.
Looks like you asked the same question on mibuso: http://www.mibuso.com/forum/viewtopic.php?f=34&t=54232&start=0
Denster,
thanks for replying… but we need to find out the report which is locking the table before request to amend the report…
Use Whereused functionality of NDT to locate all the places where Excel buffer is used
You’re right, you do need to figure out which reports have this. However it is not a SQL problem. I’ve changed the title of this post and moved it to the developer forum.
When the Excel Buffer table is used to export to Excel, the variable that is used should be a temporary variable, it’s just a way to keep the data in the computer’s memory. The reason that this locking happens is probably because there are multiple reports with this export functionality, and in a number of them this variable is not temporary. When multiple people are running reports to export to Excel, all of these people are now actually inserting records in the table, and you get locking problems.
The system admin should have a pretty good idea which report has the Excel stuff in it, and if they don’t they should be able to run down the list of reports to check. If they don’t know how to do this then get your partner involved. You could also send out an email to all of your users asking for all the reports that they use that have the Excel export.
Go to the globals of your report.
View The properties of variable you have declared for “Excel Buffer”
Set Temporary to “YES”
savatage,
thanks for replying…
“Go to the globals of your report. View The properties of variable you have declared for “Excel Buffer” Set Temporary to “YES””
the first part lies in finding out the culprit before proceed to get set variables…
So go and look for it. You can only find if you search. How can WE figure out which object is the culprit?
You were saying you got the error when running a certain report. That report is the first one I would look at. If someone else has the same problem, ask them “which report were you running?”, and that will be your second one. Then the third person will have another. At some point you will have a whole bunch of them, and when you fix them one by one, there will be less of them because they will all be fixed.
It’s a process, don’t expect these things to be fixed in a second just by asking the question on a forum. Knowing what to do is only step one, the hard work still has to be done.
Hi Denster,
I figure one of the easiest way was to run SQLactivity monitor and sort the expensive column in a circumstances when user feedback that there was performance issues…