ODBC - executing stored procedures

[theomen]

Hi,

I'm having a really strange problem executing a stored procedure through vb.net, and am hoping someone might have seen a similar problem before and might be able to help.

I have written a file polling application, which picks up various excel spreadsheets, processes the data, and stores it in an sql database. It is then supposed to perform caluclations based on this data and store the results of these calculations in another table.

I have a stored procedure called "usp_CalculateAll", which just executes a number of other stored procedures for performing the calculations (the reason its done this way is so I don't have to keep editing my vb code to add new calculations, I just create a new stored procedure, and execute it from the original, so its just like a batch file).

If I execute usp_CalculateAll through Query Analyser, it executes both of the stored procedures that actually perform the calulations, and put 1635 records into the results table. This is how it should work, and it takes about 7 seconds to execute.

However, when my application executes usp_CalculateAll, it starts executing the first stored procedure, adds 5-10 records (should be more like 1630), then just seems to give up, and doesn't execute the second stored procedure. It doesn't return any errors.

Had anyone got any idea why executing a stored procedure with no parameters through a vb application would fail when running it in the same way through query analyser works fine? I can't see that it would be timing out because as I say above, there are no errors and it only takes 7 seconds to execute anyway, and the remainder of the code in the application continues executing, so even the application doesn't fail.

Any help would be much appreciated, and could save my PC from being thrown out of the window.

 

[theomen]

P.S. I've copied the code into a new project, which just executes the stored procedure and nothing else, and it still doesn't work. Below is the code:

If sqlConn.State = ConnectionState.Closed Then
sqlConn.Open()
End If
strSql = "EXECUTE usp_CalculateAll"
Dim sqlCmd2 As New OdbcCommand(strSql, sqlConn)
sqlCmd2.ExecuteNonQuery()
TextBox1.Text = "done"
sqlConn.Close()

 

[theomen]

It's ok, I've fixed the problem now.

For debugging purposes, I was using "Print" in the stored procedure to output values, figured out that VB/ADO probably wouldn't like this so commented out the "Print" lines, and suddenly it works a treat.

Right, I'm going out to hit my head on a wall a few times