[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Return Status from SQL Server - Executing Stored Procedure

Posted on 2001-06-27
3
Medium Priority
?
684 Views
Last Modified: 2008-02-01
I'm using VB6 against SQL Server and I need some help.
From a drop-down menu in my VB app, I'd like to execute a stored procedure (an update which passes some parameters)
on SQL Server.

I'd like the sp to execute in the background and then pass a return code on completion or failure of the sp to the VB program;
Possibly display a message box to the user or some popup to let them know that the job completed.

Can anyone suggest the best way to do this?
I know sql server comes with a bunch of built-in sps; but the one closest to what I need is only suitable for OLE Automation.

Thanks in advance.

John
0
Comment
Question by:jtrapat1
[X]
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
3 Comments
 

Expert Comment

by:MarioK
ID: 6232153
Don't know if this will help, but here go's

After the SQL statement executed in your store procedure
you can query the value of the @@Error SQLserver variable
while still in the SP. If the @@Error = 0 then the SQL
statement executed without errors. You can then set a
return variable that VB will be able to to intrepid.

MK



0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6232454
Is this "job" lengthy (say minutes/hours)? in that case i would define a sql job (look under the SQL Server Agent part of the server in the Enterprise manager), you find a Jobs folder.

To start such a job, you can use the sp_startjob stored procedure. to find the result of this is a little bit more complicated...

If this goes into the direction you want, we can continue to solve the small technical details...

Cheers
0
 
LVL 7

Accepted Solution

by:
John844 earned 400 total points
ID: 6232648
one way to chest is to send some identifyer to the stored procedure to let you know what user is running stored procedure currently.  inside the stored procedure execute your items, trap any errors using @@Error.  If an error occurs, write a item to a log table with the userid time and error message.  if no errors have occured at the bottom of the sp, then write a row to the log table indicating success.  Execute the sp asyncronously without expecting a recordset back(adexecutenorecords).  check the contents of the table periodically to inform user of success or failure.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

649 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