Calling All DAO / ODBC / SQL Experts - Need Help w. Stored Procedure Calls
Need help with ODBC Direct Calls to MS SQLServer 6.5 from ACCESS
VBA I'm trying to execute a stored procedure that has no input parameters and
one output
parameter (procedure is at bottom of this note). I have not been successful
in both calling
the SP and retrieving the value of the output parameter from Access 97 using
ODBCDirect (or any other method). I can call a stored procedure that has no
input ot output
parameters (see CASE 2 below) with no problems.
HOW CAN I CALL A PROCEDURE WITH JUST AN OUTPUT PARAMETER AND GET THE
VALUE???
Below is what I have tried so far....
'Create An ODBCDirect Workspace:'
strConnection = "ODBC;DSN=FCCU;"
Set wsDirect = CreateWorkspace("ODBCDirect", "", "", dbUseODBC)
Set dbFCCU = wsDirect.OpenDatabase("", False, False, strConnection)
Set cnDirect = wsDirect.Connections(0)
'Set Up a Test SQL call (I only use one at a time, comment out the others):'
strTestString = "EXECUTE RequestToolSetBalanceDataLoad ?" TEST CASE
- Works, can't get return param
strTestString = "{call CAMMSteadyStates }"
TEST CASE 2 - Works, no parameters required
strTestString = "{call RequestToolSetBalanceDataLoad (?) }" TEST
CASE 3.1 - Doesn't work
strTestString = "{call RequestToolSetBalanceDataLoad ? }" TEST
CASE 3.2
strTestString = "{? = call RequestToolSetBalanceDataLoad }" TEST
CASE 3.3
'Create and execute the QueryDef:'
Set qdfDirect = cnDirect.CreateQueryDef("")
qdfDirect.SQL = strTestString
qdfDirect.Parameters(0).Direction = dbParamInputOutput
qdfDirect.ODBCTimeout = 0
qdfDirect.Execute
intReturnedParam = qdfDirect.Parameters(0)
qdfDirect.Close
TEST CASE 1 Works (code doesn't fail) but intReturnedParam is null (when it
should have a value)
TEST CASE 2 Is a stored proc that has no input or output parameters. This
case works as expected
(but I have to comment out the parameter statements, since no
parameters are passed)
TEST CASE 3 This is the way I would expect it to work. But it always fails
on execution with an
ODBC error! Checking the ODBC trace, the actual error is:
DIAG [37000] [Microsoft] [ODBC SQL Server Driver] [SQL SERVER] Procedure
supplied. (201)
For all cases above. I also tried setting Param Direction = dbParamOutput
and
dbParamReturnValue with same result. It is like the parameter is ignored for
Case 3
Stored Procedure for CASE 3 ( I cannot modify this - it is used by other
applications):
if exists (select * from sysobjects where id =
object_id('dbo.RequestToolSetBalanceDataLoad') and sysstat & 0xf = 4)
drop procedure dbo.RequestToolSetBalanceDataLoad
GO
create procedure RequestToolSetBalanceDataLoad
)
as
/**********************************************************************/
/* Service routine for the FCCU app. */
/* Reserves a handle for rows in the ToolSetBalanceDataLoad */
/* table. */
/* */
/* Inputs : None */
/* */
/* used by the caller as the identifier it can use */
/* when inserting rows in this table to know which */
/* rows belong to it. */
/* */
/* Returns: nothing */
/* */
/* Selects: nothing */
/* */
/**********************************************************************/
ToolSetBalanceDataLoad
insert into ToolSetBalanceDataLoad
( Handle
, Stream
, CutPoint
, Attribute
, EngUnit
, DataValue
, FCCUAttribute
)
, replicate("*",30)
, replicate("*",30)
, replicate("*",30)
, replicate("*",30)
, 0.0
, NULL
)
GO
Thanks, Marty
--
Martin Lycan
Oakland, CA
