In a VB.NET application (VS2005) I am calling a stored procedure through a SQLDataAdapter.
On my local database everything works fine. If I do this on another database I get a timeout exception.
this is the code :

Public Overloads Shared Function ExecuteDataset( _
ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter) As DataSet
If (connection Is Nothing) Then Throw New ArgumentNullException("connection")
' Create a command and prepare it for execution
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dataAdatpter As SqlDataAdapter
Dim mustCloseConnection As Boolean = False
dataAdatpter = Nothing
PrepareCommand(cmd, connection, CType(Nothing, SqlTransaction), commandType, commandText, commandParameters, mustCloseConnection)
cmd.CommandTimeout = 0 'I get a timeout exception if I leave this out
Try
' Create the DataAdapter & DataSet
dataAdatpter = New SqlDataAdapter(cmd)
' Fill the DataSet using default values for DataTable names, etc
dataAdatpter.Fill(ds)
' Detach the SqlParameters from the command object, so they can be used again
cmd.Parameters.Clear()
Finally
If (Not dataAdatpter Is Nothing) Then dataAdatpter.Dispose()
End Try
If (mustCloseConnection) Then connection.Close()
' Return the dataset
Return ds
End Function ' ExecuteDataset

I've executed the stored procedure directly on the database and that works fine and fast.
I've also tried to set the CommandTimeout property of cmd to 0.
When I do this the stored procedure gets executed but this takes a very long time.
Again if I work on a local db there are no problems.

are you able to connect that database through sql server?
–
ArshadJan 2 '13 at 8:59

Yes I've even executed the stored procedure in sql server with the same parameters and this works fine. The connectionstring in tha application is encrypted though. But all the other functionality works fine and fast so the encrypted connection string works
–
Gert HermansJan 2 '13 at 9:07

From your code is not possible to see if the connection is already open when you call Fill. Probably the connection is closed and something prevents it to open. If you add a catch clause to your Try/Finally you could see the exact error message and report about it.
–
SteveJan 2 '13 at 9:28