Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-03-22 : 09:13:00
|
| Zac writes "Hi all. My question is this. I have a database that calculates employee utilization. The root data that I get I recieve as a dbf from our accounting system. I then import that data with a dts package. My web (asp) application then allows users to choose their manager. When a manager is chosen, that manager's id is sent to the database and 4 stored procedures are run to populate a temporary table with the selected data. I store in temporary table because of some sorting and grouping features that are availiable. In the stored procedures I use cursors to traverse the data. On our staging server it works fine. On my server at home it works fine. but on the production server here, the temporary table is created, populated with the appropriate records, the first two stored procedures are run, but on the third one which calculates totals, it just stops processing about half way through the recordset and leaves the rest of the records as null. On smaller recordsets of <35 records it works fine, but on larger ones is where i get this problem. No error is returned from sql server or from asp. I have set all of my timeout values to very high numbers with no luck. I copied the website straight from the development server to the production server and dts'd the database over, so everything should be the same. Each server is running iis 4.0, and sql server 7.0, sp3. I am at my wits end, any help is greatly appreciated." |
|
|
Jay99
468 Posts |
Posted - 2002-03-22 : 09:51:34
|
Read this.If the code is the exactly the same and the servers are exactly the same and the configurations are exactly the same, the only possible reason why it works here and not there is magic. I would suggest looking into xp_MSExorcist. It is a very dangerous tool, so you may want to take another look at all the things you think are the same and look for some difference just one more time . . .Zac, there is a reason your proc is crapping out. I wish you the best of luck finding it, but I hope you don't expect me to waste my clairvoyance powers on this. I only get to use that once every 47 days . . . Take a deep breath and have at it again . .Jay<O> |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-22 : 09:56:26
|
| I've got another reason that's not really magical...server load and CURSORS! I don't imagine that your home server or your staging server have as much traffic as the production server does. It is very likely that the cursor usage is unacceptably slow, but that the effect wasn't noticeable with lighter loads.Can you post your code (stored procedures at least, ASP code too if you can)? |
|
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-03-22 : 09:57:30
|
| when you say "no errors are returned by sql server", do you mean that you've run the same stored procedures straight into query analyzer and they've correctly executed with no error?setBasedIsTheTruepath<O> |
|
|
|
jackstow
Posting Yak Master
160 Posts |
Posted - 2002-03-22 : 10:07:15
|
| robvolk is on the right path I bet ( as usual!) - it may be that you need to tidy up any db connections left open in the ASP, move your processing from recordsets into stored procedures, stuff like that.. |
|
|
|
|
|
|
|
|
Stored Procedures stop working in middle of processing.