SQL Procedure not working/very slow with SQL Command

Question

I have the following scenario;

Dim cmd as New SQLCommand
cmd.Connection = myopenconnection
cmd.CommandText = "usp_getdata"
cmd.Parameters.AddWithValue("@Year", 2008)
cmd.CommandType = StoredProcedure
Dim reader = cmd.ExecuteReader

The application get stuck and keep waiting for a response when the above is excuted. I have tryed to execute the SQL command from SQL Management studio and it works fine and on another copy of the database.

What does the SP look like? Take a look at [this article](http://www.sommarskog.se/query-plan-mysteries.html) - perhaps this will explain why you are getting different results from the app than from SSMS. — Oded, Jan 03 '12 at 17:53
Checked for locks, etc, on the target database? When you run it from management studio, are you sure to use the same login as the .Net applicaiton? — MatBailie, Jan 03 '12 at 17:54
@dems, Yes, I am sure that I am using the same login credentials. — A. Agius, Jan 03 '12 at 18:05
`.Read` only advances the reader to the first result (if one exists). — Oded, Jan 03 '12 at 18:14
Which line in the question does the application get stuck on and for how long? That is fairly critical information to determining an answer. — competent_tech, Jan 03 '12 at 19:49

score 2 · Answer 1 · 2012-01-03T17:58:10.907

2

Execute a stored procedure with no data return

Instead of calling SqlCommand.ExecuteReader(), call SqlCommand.ExecuteNonQuery().

As per this MSDN reference:

ExecuteReader

Executes commands that return rows. For increased performance, ExecuteReader invokes commands using the Transact-SQL sp_executesql system stored procedure. Therefore, ExecuteReader might not have the effect that you want if used to execute commands such as Transact-SQL SET statements.

ExecuteNonQuery

Executes commands such as Transact-SQL INSERT, DELETE, UPDATE, and SET statements.

EDIT:

Execute a stored procedure to retrieve data

Or if what you're trying to do is return a scalar value, you can use SqlCommand.ExecuteScalar(). But if you're looking to get data, you need to utilize a SqlDataReader object, like so:

Dim queryString As String = "usp_getdata"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.CommandType = CommandType.StoredProcedure
        connection.Open()

        Dim reader As SqlDataReader = command.ExecuteReader()

        ' Call Read before accessing data.
        While reader.Read()
            Console.WriteLine(String.Format("{0}, {1}", _
                reader(0), reader(1)))
        End While

        ' Call Close when done reading.
        reader.Close()
    End Using

SqlDataReader Class Reference

edited Jan 03 '12 at 17:58

answered Jan 03 '12 at 17:52

And if it is supposed to return results? `usp_getdate` implies that `ExecuteReader` is the right choice. – Oded Jan 03 '12 at 17:53
This doesn't really answer the question though, does it? – Oded Jan 03 '12 at 17:58
@Oded well I would think it does, because if the OP is just calling `ExecuteReader()` without stepping through it and retrieving values, it would appear as though nothing is happening. – Jan 03 '12 at 17:59
My understanding is that the application hangs on that line, not that the reader is not being utilized. I could be wrong of course. – Oded Jan 03 '12 at 18:00
the SP should return data as it contains a SELECT statement. – A. Agius Jan 03 '12 at 18:02
@Agius then utilize the ExecuteReader example of my post. Does that help? – Jan 03 '12 at 18:25
At the moment I don't have access to the server cause I am not at work. I will check tomorrow. From some of my research it seems that the issue might also be that I don't have SET ARITHABORT OFF in my SP. – A. Agius Jan 03 '12 at 19:44

A. Agius · Accepted Answer · 2012-08-04T06:20:56.643

Very slow/not working code;

  Dim cmd As New SQLCommand
  cmd.Connection = myopenconnection
  cmd.CommandText = "usp_getdata"
  cmd.Parameters.AddWithValue("@Year", 2008)
  cmd.CommandType = StoredProcedure
  Dim reader = cmd.ExecuteReader

The problem was solved by modifiying the above code to;

 Dim cmd As New SQLCommand
 cmd.Connection = myopenconnection
 cmd.Parameters.AddWithValue("@Year", 2008)
 cmd.CommandText = "Exec usp_getdata @Year With Recompile"
 cmd.CommandType = Text
 Dim reader = cmd.ExecuteReader

SQL Procedure not working/very slow with SQL Command

2 Answers2

Execute a stored procedure with no data return

Execute a stored procedure to retrieve data