Reports Generator Conflict... .Need solution. 1

[m0nty005]

Technology: ASP, SQL Server, javascript
I've design an web application which runs a stored proc (that cleans out and fill this table w/ data). When there's different web users generating their own report at the same time (particularly when one report is still being generated), there'll be a conflict because this one table is still generating data while another user tries to generate his own data report to the same table. Also, not to mention that the same sql account (userReport used for all web users) is conflicting at that same time! Any solutions to this?? If I run the query strictly from the ASP page, w/o using the stored proc and that dynamic table, to generate data on the fly.. session time out will occur on long reports. Arghhhh...!?.

- Lost in limbo

 

[donutman]

I don't know your situation, but what I do for that problem is only delete the data for that user. One column has user info (computer name in my case). That way it's at least a multi-computer friendly app.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

Actuyally the problem is that you should be doing this type of work using temp tables or table varaiables not real tables. Temp tables are local in scope and so different people can work with different sets of data simultaneously without interfereing with each other. You create a temp table by prefacing the table name with #. Other than that a temp table works just like a real table in your SQL code. You should of course follow good programming practice and drop the temp table when you are done with it. Table variables are even better than temp tables as far as efficiency, but they cannot do everything a temp table can, for instance you can't populate them by execuiting another stored procedure. You should read up on this in BOL.

Further using an ordinary database table will force your sp to recompile every time you execute them which will make them slower than they need to be. It will also make your database take longer to restore because all those changes are logged and will be part of the restoration process.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

It's funny how you get a misconception stuck in your head and it lives on for years. I just tested to see if Crystal Reports will work on a temporary table, especially one that you drop after you Select * from it. It works fine.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Kingkumar]

Hi donutman,
I am required to move from web reports in my asp/sql application to crystal reports.
I would appreciate if you should show me a code snippet of how to achieve this or guide me to a proper link to achieve this.
Thanks.
--King

 

[donutman]

King, you should post in the CR forum. I'm using an old version 7.0 and if you're not at all familiar with CR, then I'm not sure how to get you started. If you're just looking for a SP as suggested by SQLSister, then she or I can do that. Let us know.
SQLSister, you are not going to believe this. My next project this morning (after checking in on TT) was to work on a user complaint about a VB/CR report application that runs really slow during the Form_Load event. You can probably guess what table it uses! Now, I also see why I persisted that table. I had to because it reports on the invoices that had been printed by the application at that particular workstation. Well, the table was getting rather large because I never bothered to prune it over time so it was keeping too much unnecessary history. WHAT a coincidence, the VERY next bit of work I did after posting my answer to you!
This is off topic, but I'm just curious if you've had to deal with the same problem addressed by this table. I print invoices and mark the invoices as printed (hence not requiring further printing) when the report is sent to the printer. If the printer jams or for whatever reason losses the output (which happens often enough that I needed to deal with it), then I need to be able to reprint them. Is there a better way to solve this problem? I can't rely on the users to press a button after they have actually received the output...they will forget to.
I also like the way my mind twisted the reason for doing this in the first place.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[SQLSister]

I suppose instead of the printed yes/no you could have two columns one with date printed (Which will use a variable storring getdate for the input, so that all in the same batch have the same date/time) and userid to identify who printed it. Thenn if user 2 comes to you and says, can I get a reprint of the batch I printed yesterday, you can easily identify the records to reprint. Null values of course are invoices nobody has printed.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[donutman]

It's one of those legacy apps written by a beginner...I wrote it 3 years ago! I didn't anticipate the printer issue, so it was easier to just create a new table with the additional info. Actually, now that I've added an automatic pruning feature, it's better than having the info in the base table...a serendipitous design indeed.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]