table locking

[Cyrus]

I have written app using sql server as backend, it just a simple reporting,
i have created a X table to store data taken from various table and use the X table as source for reporting, if one user is accessing the x table to generate report its 100% Ok, but if by change another user generate a report using X table as well, user 1 extracted data is being included in user 2 extracted data.

How can i prevent other user to access the table until the fist user has completed his report.

i've tried adlockpessimistic and open static not working

tnx
cyrus

[Shuja Ali]

Do you mean to say that you dump the data into a table X when a user requests a report? If that is the case then you will have this kind of problem where data would be stale or not correct for most of the cases when concurrent users use your tool. And this is the not the right way to write a reporting app.

The correct way would be to dump all records into one table and then query from this table. You could use a nightly job to dump all your reporting data into a single table, or you can run the job at regular intervals. If you are using SQL 2005, you could also try looking into partitioning. You could also use a simple view.

I am not sure about your requirement, if this doesn't help you, could you elaborate on what exactly ou are trying to do.

[dglienna]

Add two more fields. One for EMP# that can view, and also a DELETE flag, so you know when to do garbage collection.

[ComITSolutions]

Have you ever tried Table Type of Data in SQL Server?

[Cyrus]

Do you mean to say that you dump the data into a table X when a user requests a report? If that is the case then you will have this kind of problem where data would be stale or not correct for most of the cases when concurrent users use your tool. And this is the not the right way to write a reporting app.

The correct way would be to dump all records into one table and then query from this table. You could use a nightly job to dump all your reporting data into a single table, or you can run the job at regular intervals. If you are using SQL 2005, you could also try looking into partitioning. You could also use a simple view.

I am not sure about your requirement, if this doesn't help you, could you elaborate on what exactly ou are trying to do.

Following ur suggestion problem has been resolve,by maintaining the query for selection criteria of data to be reported.

thnks