Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

Running SQL Stored Procedure from VB

I am running two stored procedures in SQL 2005 from VB. First one runs fine but the second it looks like it runs but does not do anything. It does not give me any errors and I know it works and runs in SQL but from VB the second proc just runs thru and does not execute. I am not sure why it would not work for the second command.

    Cmd1.ActiveConnection = Con1
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.CommandText = "UpdateTemp"
    Cmd1.CommandTimeout = 0
    Cmd1.Execute
    Cmd2.ActiveConnection = Con1
    Cmd2.CommandType = adCmdStoredProc
    Cmd2.CommandText = "ApplyPay"
    Cmd2.CommandTimeout = 0
    Cmd2.Execute
    Con1.Close
0
Galina Besselyanova
Asked:
Galina Besselyanova
  • 5
  • 3
1 Solution
 
Haris DjulicCommented:
Did you try to close the connection after first proc and then open it again and run the second proc?

    Cmd1.ActiveConnection = Con1
    Cmd1.CommandType = adCmdStoredProc
    Cmd1.CommandText = "UpdateTemp"
    Cmd1.CommandTimeout = 0
    Cmd1.Execute
    Con1.Close
    con1.open
    Cmd2.ActiveConnection = Con1
    Cmd2.CommandType = adCmdStoredProc
    Cmd2.CommandText = "ApplyPay"
    Cmd2.CommandTimeout = 0
    Cmd2.Execute
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it actually runs, but " does not execute" usually means that it does not what you think it does.
seeing the procedure code might help to clarify.
adding debugging steps to the procedure(s) would be part of this
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Code for the executing the procedure works it executes but the procedure itself does not do what it suppose to do. When I execute the same procedure inside SQL it works fine. And same procedure runs every night on the job schedule in SQL just fine.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I wrote above, you need to debug what is happening.

executing stored procedures does not "do nothing".
either there is an error (it might be ignored/catched in the error handling somewhere), or the procedure does run.
you might not check in the same database then where you run the procedure
you might look at the wrong rows
you might have some other process that undoes the changes by the procedure.
....
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
The thing is both procedures run in the same database (only one connection to one db). There is no error handling for this piece of code and there is no process to undo the changes.  First procedure creates temp table with outstanding balances and runs for 3-4 min. Second one applying payments to the outstanding balances (when it is done running outstanding balances should be 0) and suppose to run even longer than the first. But when you debug  it in VB it goes thru the execute line in a 1 sec and when I check the payment is not applied.
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Yes, I did try to close and open the connection again. Unfortunately it did not do anything.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> First procedure creates temp table
temp table like #table name?

if that is the case, you might have the problem that the second procedure finds that table empty after quitting the first one ...

why is this not in 1 single procedure?
resp, you could create 1 procedure calling the 2 procedures?

next: why not put that into a sql agent job, and the GUI application does only start the job, so it processes async. having to wait in a gui to run something for over 3 minutes without knowing if the job is still running or simply the app crashed is no good design.
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Putting this two procedures was not  a bad idea. That is what I did. And it is running fine in SQL and still not applying payments from VB.
0
 
Galina BesselyanovaSenior Software Developer/EngineerAuthor Commented:
Sorry, I meant I put this two procedures together in one.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now