1
votes

Using VB.Net and SQL

Code

 myConnection = New SqlConnection(Connection)
        myConnection.Open()
        Dim myCommand As SqlCommand = New SqlCommand("proc_g_report", myConnection)
        Dim myReader As SqlDataReader = myCommand.ExecuteReader() 

        DTResults.Load(myReader)

Throwing error as "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding" Error on this line Dim myReader As SqlDataReader = myCommand.ExecuteReader()

And I cannot change the database pool size due to security reason, Please any one advise.

2
did you try myCommand.CommandTimeout=0 - Ganesh
@Ganesh, where i have to user after which line - Gopal
posted answer try that - Ganesh
Have you looked at optimizing the stored procedure? - Bernd Linde
Not sure if you have posted all the code, but if you want to call a StoredProcedure you need to set the CommandType to CommandType.StoredProcedure or the db cannot understand your command text - Steve

2 Answers

1
votes

Try Setting CommandTimeout

        myConnection = New SqlConnection(Connection)
        myConnection.Open()
        Dim myCommand As SqlCommand = New SqlCommand("proc_g_report", myConnection)
        myCommand.CommandTimeout=0
        Dim myReader As SqlDataReader = myCommand.ExecuteReader() 

        DTResults.Load(myReader)
1
votes

The error seems to be unrelated but, if you want to call the execution of a stored procedure you need to tell what is the CommandType passed.

The default is CommandType = CommandType.Text, so your command text (proc_g_report) is treated as if it was a SELECT/INSERT or other standard T-SQL statement.

You need to set the CommandType and possibly add the Using Statement around the disposable objects....

Using myConnection = New SqlConnection(Connection)
Using myCommand = New SqlCommand("proc_g_report", myConnection)
    myCommand.CommandType = CommandType.StoredProcedure
    myConnection.Open()
    Using myReader = myCommand.ExecuteReader() 
         DTResults.Load(myReader)
    End Using
End Using
End Using

Of course you could easily test if this is the problem using the Sql Server Management Studio and call that stored procedure from you working PC and check what amount of time is required to complete. If the time is less than 30 seconds (the default timeout for the SqlCommand execution) then the problem is elsewhere and not in the Timeout value.