2

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.

Here is the stacktrace of the timeout exception

at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
   at KlantenApplicatie.BL.DAL.SQLHelper.ExecuteDataset(SqlConnection connection, CommandType commandType, String commandText, SqlParameter[] commandParameters) in C:\BitProjects\teamsystem\gerher\KlantenApplicatie.BL.DAL\sqlHelper\SQLHelper.vb:line 545

Any ideas on what might be the cause of this?

Gert Hermans
  • 769
  • 1
  • 9
  • 30
  • are you able to connect that database through sql server? – Mohammad Arshad Alam Jan 02 '13 at 08: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 Hermans Jan 02 '13 at 09:07
  • can you share full code to fill DataSet? – Mohammad Arshad Alam Jan 02 '13 at 09:16
  • 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. – Steve Jan 02 '13 at 09:28
  • what i will suggest is that.. you just declare SqlConnection and SqlCommand here set connection string manually(Decrypted) and test it – Mohammad Arshad Alam Jan 02 '13 at 09:31
  • and set command type is StoreProcedure – Mohammad Arshad Alam Jan 02 '13 at 09:32
  • The code I put here does work because I've set the CommandTimeOut to 0.It just takes a very long time. If I remove this line I get A timeout exception. The Connection must be open otherwise the it wouldn't have worked the first time. If I change my connectionstring in the app.config file to another database everything works fine. – Gert Hermans Jan 02 '13 at 09:40

1 Answers1

3

I've found a solution.

It turned out to be a parameter sniffing problem for query optimalisation. I needed to add OPTION(RECOMPILE) to the sql that creates the stored procedure.

the issue is explained here:

https://www.itprotoday.com/sql-server/using-recompile-query-hint-solve-parameter-sniffing-problems

And here:

http://social.technet.microsoft.com/Forums/en-US/sqldatabaseengine/thread/74a85e26-be9b-4830-9638-6aa30fd9e3e7

batintherain
  • 63
  • 8
Gert Hermans
  • 769
  • 1
  • 9
  • 30