Running stored proc doesn't fully complete

[DannyC]

Hi,

I have a plugin which puts a button on the debtors maintenance ribbon which simply runs a stored proc - no parameters.

Code: Select all

   Private Sub DebtorForm_Toolbar_ToolClick
      With JiwaApplication.Manager.Instance.Database
      Dim SQL As String = ""
      Dim SQLReader As SqlDataReader = Nothing
      Dim SQLParam As SqlParameter = Nothing
      
        SQL = "usp_MyStoredProc"

        Using SQLCmd As SqlCommand = New SqlCommand(SQL, .SQLConnection, .SQLTransaction)

         SQLCmd.CommandType = System.Data.CommandType.StoredProcedure          
              SQLReader = SQLCmd.ExecuteReader()
              SQLReader.Close()
         SQLCmd.Dispose()
        End Using         
      
       End With      
   End Sub   


When the button is clicked, the stored procedure doesn't seem to fully complete. When I execute it in a SQL query window it completes fine, taking about 2 to 3 minutes.
I am wondering if there is some kind of built in timeout or row limitation in the ExecuteReader which is stopping the stored proc from running to completion? There is no error displaying and no rows need to be returned to the plugin for processing elsewhere. The stored proc reads some debtor custom fields and populates another table elsewhere and the rows it needs to deal with can be 10,000 to 20,000. The stored proc, when executed from the button seems to handle smaller recordsets OK, but a typical scenario would be much higher.
Maybe if CommandType.StoredProcedure was not specified and it just executes a SQL statement "EXEC usp_MyStoredProc" ? How would I do that - just leave out the CommandType.StoredProcedure line?
Other ideas?

[Mike.Sheen]

I am wondering if there is some kind of built in timeout or row limitation in the ExecuteReader which is stopping the stored proc from running to completion?

Yes, there is. Default is 30 seconds, so set it to be what you desire.

Mike

[DannyC]

That'd be it.
I have timed it and it seems to take around 9 minutes, so will set the timeout to be much longer.

Three further questions:
See attached plugin. It seems to be firing whenever a debtor is read. It should only be firing when the button is clicked. Looks OK to me, can you run your eye over it and see why it is firing each time a debtor is read?

As alternative, I do not need it to be a toolbar button, I could instead run a BAT file from a Jiwa menu item which could run the stored procedure via a SQLCMD command line in the BAT file. You have a sample "launch app" plugin which launches Notepad. I guess I can use that to fire a BAT file by specifying the path & filename where you have "notepad.exe"?
Or have a Jiwa menu item to run the stored proc directly from the plugin?

Cheers

[DannyC]

Hi,
I have managed to sort out my own answers to q2 & q3. I tried running a BAT file with a SQLCMD command line to run the stored proc, and also running the stored proc directly from the Jiwa plugin.
As an FYI, I am running with the BAT method because when running the SP from the plugin, Jiwa hangs for the full timeout specified. When launching a BAT file, Jiwa is freed up immediately for other work while the DOS command prompt sits there as the SP runs.

Be interested in the fault which made the button fire each time a different debtor was loaded.

[Mike.Sheen]

Hi Danny,

Regarding the ToolClick, I can't see why the read of a debtor would invoke your handler, but I note that you are not checking which tool is clicked in your handler - so your handler will run the code regardless of the tool clicked.

Your handler code should look like this:

Code: Select all

Private Sub DebtorForm_Toolbar_ToolClick(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinToolbars.ToolClickEventArgs)
    Select Case e.Tool.Key
        Case "Price Load"
            With JiwaApplication.Manager.Instance.Database
                 Dim SQL As String = ""
                 Dim SQLReader As SqlDataReader = Nothing
                 Dim SQLParam As SqlParameter = Nothing
      
                Try
                     SQL = "Attkey_ConfigureDebtorPricing"

                    Using SQLCmd As SqlCommand = New SqlCommand(SQL, .SQLConnection, .SQLTransaction)
                        messagebox.Show("Please wait 10 minutes for the prices to update into inventory.")
                   SQLCmd.CommandTimeout = 600
                        SQLCmd.CommandType = System.Data.CommandType.StoredProcedure          
                        SQLReader = SQLCmd.ExecuteReader()
                        SQLReader.Close()
                    End Using
             Finally
                If SQLReader IsNot Nothing Then
                    SQLReader.Close()
                End If
            End Try
       End With   
    End Select
End Sub


As for a menu item invoking a BAT file - that's an interesting approach! It will happily run in a separate thread and not block the Jiwa UI - but it does mean you need to have in the BAT file the SQL credentials to use - something which may or may not be a concern to you - depending on how accessible the BAT file is to users (you could always pass the SQL username and password as arguments to the batch file, which would address this concern). You also lose the ability to handle exceptions and report those back to the user in the Jiwa UI context.

There are ways you can use a background worker for long running tasks to execute whilst returning control to the user - it's a more advanced technique which requires some careful approaches on how to update the UI from your long running task as threads outside the Jiwa application can't be interacted with directly. It is possible, and I'll post a sample at some point showing how to do that - but if you're happy with the BAT technique you've arrived at then so be it.

You could also consider using Powershell script. If you don't know powershell scripts I posted a sample here.

Mike