Timeout Exception When Running Stored Procedure

Hi,

 I'm running a CLR stored procedure through my web using table adapters as follows:

res = BLL.contractRateAdviceAdapter.AutoGenCRA()    'with BLL being the business logic layer that hooks into the DAL containing the table adapters.

 The AutoGen stored procedure runs fine when executed directly from within Management Studio, but times out after 30 seconds when run from my application. It's quite a complex stored procedure and will often take longer than 30 seconds to complete.

The stored procedure contains a number of queries and updates which all run as a single transaction. The transaction is defined as follows:

----------------------------------------------------------------------------------------------------------------------

options.IsolationLevel = Transactions.IsolationLevel.ReadUncommittedoptions.Timeout = New TimeSpan(1, 0, 0)

Using scope As New TransactionScope(TransactionScopeOption.Required, options)

'Once we've opened this connection, we need to pass it through to just about every

'function so it can be used throughout. Opening and closing the same connection doesn't seem to work

'within a single transactionUsing conn As New SqlConnection("Context Connection=true")

conn.Open()

ProcessEffectedCRAs(dtTableInfo, arDateList, conn)

scope.Complete()

End Using

End Using

----------------------------------------------------------------------------------------------------------------------

As I said, the code encompassed within this transaction performs a number of database table operations, using the one connection. Each of these operations uses it's own instance of SQLCommand. For example:

----------------------------------------------------------------------------------------------------------------------Dim dt As DataTable

Dim strSQL As StringDim cmd As New SqlCommand

cmd.Connection = conn

cmd.CommandType = CommandType.Text

cmd.CommandTimeout = 0Dim rdr As SqlDataReaderstrSQL = "SELECT * FROM " & Table

cmd.CommandText = strSQL

rdr = cmd.ExecuteReader

SqlContext.Pipe.Send(rdr)

rdr.Close()

----------------------------------------------------------------------------------------------------------------------

Each instance of SQLCommand throughout the stored procedure specifies cmd.CommandTimeout = 0, which is supposed to be endless. And the fact that the stored procedure is successful when run directly from Management studio indicates to me that the stored procedure itself is fine. I also know from output messages that there is no issues with the database connection.

I've set the ASP.Net configuration properties in IIS accordingly.

Are there any other settings that I need to change?

Can I set a timeout property when I'm calling the stored procedure in the first place?

Any advice would be appreciated.

 

Thanks