Stored procedure hanging in Read only database...

[Madhavan Rajagopal]

Problem Description:

Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).

If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.

On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).

Environment:

Windows NT 4.0 (Service Pack 5 )
SQL Server 7.0 ( Service Pack 3 )
Multi Processor ( 2 - 500 Mhz )

Let me know if you need more information on it.

[ItsMeAgain]

Try recompiling the storedproc for each execution.

------------
Madhavan Rajagopal at 5/7/01 8:09:04 PM

Problem Description:

Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).

If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.

On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).

Environment:

Windows NT 4.0 (Service Pack 5 )
SQL Server 7.0 ( Service Pack 3 )
Multi Processor ( 2 - 500 Mhz )

Let me know if you need more information on it.

[Madhavan Rajagopal]

I cannot do that since it is a read only database.


------------
ItsMeAgain at 5/8/01 1:11:56 PM

Try recompiling the storedproc for each execution.

------------
Madhavan Rajagopal at 5/7/01 8:09:04 PM

Problem Description:

Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).

If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.

On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).

Environment:

Windows NT 4.0 (Service Pack 5 )
SQL Server 7.0 ( Service Pack 3 )
Multi Processor ( 2 - 500 Mhz )

Let me know if you need more information on it.

[Mad]

????


------------
Madhavan Rajagopal at 5/7/01 8:09:04 PM

Problem Description:

Stored Procedure hanging when try to execute in READ_ONLY Database. Though the stored procedure is highly complicated,it runs smoothly on the same server if the Database option is set to READ/WRITE. Not all stored procedures are hanging in READ_ONLY database,only if the stored procedure tries to retrieve more records (say 10000 ).

If the SQL Statements present inside the stored procedures are executed,they are getting executed perfectly but hangs if it is executed as stored procedure.

On restarting the SQL Server, stored procedure getting executed only for the first time in READ_ONLY database but executing it again for the second time, it starts hanging. ( I tried executing the stored procedure after clearing the procedure cache but still hangs ).

Environment:

Windows NT 4.0 (Service Pack 5 )
SQL Server 7.0 ( Service Pack 3 )
Multi Processor ( 2 - 500 Mhz )

Let me know if you need more information on it.