Long running SP never ends using sqlCommand

Question

I've got a quite expensive stored procedure in my SQL Server database. Launching it from the SQL Server Managment Studio requires some minutes. But I can't launch it via code using SqlCommand.

I've got this code:

spExecQuery = "EXEC [schema].[storedName]"

If I use this vb.NET snippet:

Using sqlCmd As SqlCommand = New SqlCommand(spExecQuery, conn)
    sqlCmd.ExecuteNonQuery()
End Using

The script ends with "Timeout expired" error. But if I do:

Using sqlCmd As SqlCommand = New SqlCommand(spExecQuery, conn)
    sqlCmd.CommandTimeout = 0
    sqlCmd.ExecuteNonQuery()
End Using

The script never ends (it's running from at least 2 hours)... What am I missing? Thank you.

Tim Schmelter · Accepted Answer · 2014-02-04T13:11:29.293

2

You need to specify first that it's a stored-procedure:

Using sqlCmd As SqlCommand = New SqlCommand(spExecQuery, conn)
    sqlCmd.CommandType = CommandType.StoredProcedure
    sqlCmd.CommandTimeout = 0
    sqlCmd.ExecuteNonQuery()
End Using

You could also use QueueUserWorkItem to let the method run asynchronously, then you don't need to wait until it has finished:

Public Shared Sub ExecuteStoredProcedureAsync()
    Threading.ThreadPool.QueueUserWorkItem(
        New Threading.WaitCallback(AddressOf ExecuteStoredProcedure)
        )
End Sub

Private Shared Sub ExecuteStoredProcedure(threadState As Object)
    Dim sw = New Stopwatch()
    sw.Start()
    Try
        ' add code or call of long running method here '
        Log.WriteInfo(String.Format("ExecuteStoredProcedure(async call) executed successfully, execution-time: {0}.", sw.Elapsed))
    Catch ex As Exception
        Log.WriteError(String.Format("Exception in ExecuteStoredProcedure (async call), execution-time: {0}.", sw.Elapsed))
    End Try
End Sub

edited Feb 04 '14 at 13:11

answered Feb 04 '14 at 11:09

Tim Schmelter

450,073
74
686
939

Thank you. I've done as suggested, but in the meanwhile the script has ended. It took about 4 hours... So the real question is why so much difference between SSMS and the code I ran? Anyway, I hope the solution you provided will speed up the execution. I already running the SP in a different thread, but I'm inside a setup project, so it must finish before I allow to go to the next page... For now I'm allowing to skip the SP execution, because it would be a pain to wait for such a long time inside an installer... – ufo Feb 04 '14 at 14:48
@ufo: you have to use the profiler to see why there's such a great difference between ssms and `SqlCommand`. Maybe this helps also: http://stackoverflow.com/questions/801909/why-is-some-sql-query-much-slower-when-used-with-sqlcommand – Tim Schmelter Feb 04 '14 at 14:55

score 0 · Answer 2 · answered Feb 04 '14 at 11:10

0

It should work if you specify the CmdType.

If still not working, try using the SqlDataReader

 cmd.Connection = conn
 cmd.CommandType = CommandType.StoredProcedure
 cmd.CommandText = "proc_name"

 Dim dr As SqlDataReader = cmd.ExecuteReader

answered Feb 04 '14 at 11:10

Nadeem_MK

7,533
7
50
61

score 0 · Answer 3 · answered Feb 04 '14 at 11:12

0

try to add the command type to your sqlcommand

sqlCmd.CommandType = CommandType.StoredProcedure

answered Feb 04 '14 at 11:12

dimis164

130
9

Long running SP never ends using sqlCommand

3 Answers3