Timeout Expired error while restoring database

Hi,

I am getting the Timeout Expired error when the tool try restoring the database from the backup file which is lying on the same DB server.

The tool calls a stored procedure which does lots of actions.The tool is developed in VB6 and transactions are handled by COM+ servers. The data access layer is installed on Com+ server.

When i execute this StoredPrfocedure thru query analyser it works fine and took max 4-5 minutes and it doesnt give me TimeOut expired error but when this SP is executed thru Data access layer it gives me this error.

Here are the sequence of actions:

1) Get the list of users in the existing database by opening a cursor.
Loop through all the users records and calls 'EXEC sp_GrantDBAccess ' to grant access
to each user.
2) Get the role membership of users in the existing database by opening a cursor
Loop through all the users records and calls ' EXEC sp_AddRoleMember '
3) RESTORE the database using the SQL given below:

BEGIN
SET @SQL = 'RESTORE DATABASE ' + @DatabaseName + '
FROM DISK = ''' + @BackupFile + '''
WITH MOVE ''StandardTest_Dat'' TO ''' + @DataPath +
@DatabaseName + '.mdf'',
MOVE ''StandardTest_Log'' TO ''' + @DataPath +
@DatabaseName + '_log.ldf'''
EXEC (@SQL)
SET @RetVal = @@ERROR
END
4) Finally it restores the users old access to databaseby opening the cursor and
executing the sql e.g.the 'EXEC sp_RevokeDBAccess'


The tool intercats with database through Data Access layer which is installed on COM+ server.

The connection settings which tool uses to get connected to the Database server is
<CommandTimeout>6000</CommandTimeout>
<ConnectionTimeout>240</ConnectionTimeout>
<LockTimeout>600000</LockTimeout>

I have tried increasing the connectiontimout but it didnt help. I set it to 0(Unlimited) but result the same.

The fast response would be appreciated.

Many Thanks

Setting all timeouts to 0 should solve the problem. Double-check if perhaps a class is invoked that sets timeout by itself.

Thanks Adriaan! for the fast response.

I set all the values to 0 e.g.
<CommandTimeout>0</CommandTimeout>
<ConnectionTimeout>0</ConnectionTimeout>

But No luck. I ma getting the same error.

I have double checked and the application reads the settings as given above from the config file(XML) and its not changed inside code.

Many Thanks.

Thanks Satya for thr responce,

I put some break points in the application code and the TimeOut error is coming when the Data Access Layer makes a call to the stored procedure which does all the actions which i have mentioned in the thread above.

But as i already said the error is not occuring when we run the same SP thru query analyzer(it taked 2 mins approx). I dont know how can we step thru the Stored Procedure when it is being called by the client application.

Many Thanks.