How to return result code from VB 6.0 invoked SQL 2005 stored proc

Question

Post reply

How to return result code from VB 6.0 invoked SQL 2005 stored proc

Express12

SSCoach

Points: 18514
More actions
July 20, 2009 at 11:52 am

#208488

How do I code a SQL 2005 proc that will deliver an output returncode of 0 (to a VB GUI) if successful?
This proc will be invoked via ADO connection therefore this mechanism will not work:
EXEC @returnCode = MyDatabase.dbo.spr_MyProc @MyParm_1, @MyParm_2
(NOTE: the proc receives 2 parms and should return the RETURN CODE value)
BT

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

If the sproc has no errors it will return 0 automatically at the end..

CEWII

Express12 SSCoach Points: 18514 More actions · Answer 2

Sorry -- still don't understand.. as a DBA, I've coded a proc which is successfully invoked by our developer's front end VB 6.0 GUI... (ADO)

My developer is requesting a RETURN CODE from the proc -- back to the GUI... I'd like to return the values 0-6 listed at the tail end of my proc --- Am I coding the proc correctly here (specifically - the @ReturnCode and/or OUTPUT @status) source code:

CREATE PROCEDURE [dbo].[spr_Start_SSIS_Pkg]

@SSISPkgName NVARCHAR(254) = " "

,@ActiveDate CHAR(8) = " "

-- Outputs

,@Status INT output

AS

SET NOCOUNT ON

DECLARE @ssisstr VARCHAR(8000)

, @params VARCHAR(8000)

, @returncode INT

, @SSIS_Env VARCHAR(1000)

, @SSISPkgLocation VARCHAR(1000)

IF rtrim(@ActiveDate) = 'n/a'

SET @params = ' '

ELSE

SET @params = '/SET \package.variables[pActiveDate].Value;"\"' + @ActiveDate + '\""'

-- ******* Set @SSISPkgLocation & executable cmd line

SET @SSISPkgLocation = '\\' + (SELECT LEFT((SELECT @@servername),CHARINDEX('\',(SELECT @@servername))-1)) + '\IRM_SSIS\'

SET @ssisstr = 'dtexec /f ' + @SSISPkgLocation + @SSISPkgName + '.dtsx '

SET @ssisstr = @ssisstr + @params

-- Finally, execute dynamic SQL by using xp_cmdshell

EXEC @returncode = xp_cmdshell @ssisstr

--RETURN @returncode

--EXEC @returncode = xp_cmdshell @ssisstr

--SELECT @returncode

SELECT @status

/*

Value Description

0 -- The package executed successfully.

1 -- The package failed.

3 -- The package was canceled by the user.

4 -- Utility unable to locate the package. Package not found.

5 -- Utility unable to load the package. Package not loaded.

6 -- Utility encountered an internal error.

*/

GRANT EXECUTE ON spr_Start_SSIS_Pkg TO PUBLIC

BT

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

Is your developer of the VB6 application testing for returned errors in the existing code?

From the Help file for VB6 and ADO

Any operation involving ADO objects can generate one or more provider errors. As each error occurs, one or more Error objects are placed in the Errors collection of the Connection object. When another ADO operation generates an error, the Errors collection is cleared, and the new set of Error objects is placed in the Errors collection.
Note Each Error object represents a specific provider error, not an ADO error. ADO errors are exposed to the run-time exception-handling mechanism. For example, in Microsoft Visual Basic, the occurrence of an ADO-specific error will trigger an On Error event and appear in the Err object. For a complete list of ADO errors, see the ADO Error Codes topic.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

mj12 SSC-Addicted Points: 437 More actions · Answer 4

Have you looked at the ADODB.Command object, specifically the .Parameters collection? Here's something that should get you started... ADO Stored Procedures