Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Running SQL Stored Procedure from VB

Posted on 2011-09-15
10
350 Views
Last Modified: 2013-12-25
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
Comment
Question by:kqureshi321
  • 5
  • 3
10 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36542892
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36542906
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
 

Author Comment

by:kqureshi321
ID: 36543052
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36543373
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
 

Author Comment

by:kqureshi321
ID: 36544663
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
 

Author Comment

by:kqureshi321
ID: 36544810
Yes, I did try to close and open the connection again. Unfortunately it did not do anything.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 36547624
> 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
 

Author Comment

by:kqureshi321
ID: 36567245
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
 

Author Comment

by:kqureshi321
ID: 36567421
Sorry, I meant I put this two procedures together in one.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question