D2007, DBX tsqlstoredproc and MSSQL errors

[Dwight Crevelt]

I am getting errors trying to access a stored procedure on a MSSQL server
using DBX tsqlstoredproc in D2007
Stored Procedure name sp_GetClientInfo, input param @plcard, outputs several
parameters Plfname, Pllname

The following gives an Index out of bounds error.

sqlstoredproc1.Close;
sqlstoredproc1.Params.CreateParam(ftstring,'@plcard',ptinput);
sqlstoredproc1.ParamByName('@plcard').asstring := edit1.Text;
sqlstoredproc1.ExecProc;

Removing the CreateParm line gives a Sqlstoredproc1 error parameter plcard
not found.

Removing the ParamByName line ALSO gives a MS SQL Server error
sp_GetClientInfo expecting parameter @plcard which was not provided.

using Open vs. ExecProc gives same results.

How do you get the SQLStoredProc in DBX to work?

[Dwight Crevelt]

I have got the code to access the stored procedure but it does not see the
output parameters.
I get a parameter not found '@PLACCN'

I have parameters defined in the sqlstoredproc at design time, but had to
add them at runtime to get them to be seen on the input side anyway.

sqlstoredproc1.StoredProcName := 'sp_GetClientInfo';
sqlstoredproc1.SchemaName := 'dbo';

sqlstoredproc1.Params.CreateParam(ftstring,'@plcard',ptinput);
sqlstoredproc1.Params.CreateParam(ftstring,'@placcn',ptoutput);
sqlstoredproc1.Params.CreateParam(ftstring,'@plfname',ptoutput);
sqlstoredproc1.Params.CreateParam(ftstring,'@pllname',ptoutput);
sqlstoredproc1.Params.CreateParam(ftstring,'@pllevel',ptoutput);
sqlstoredproc1.ParamByName('@plcard').asstring := edit1.Text;
stpret := sqlstoredproc1.ExecProc;

placcn := sqlstoredproc1.ParamByName('@placcn').AsString; Errors
out here
plfname := sqlstoredproc1.ParamByName('@plfname').AsString;
pllname := sqlstoredproc1.ParamByName('@pllname').AsString;
pllevel := sqlstoredproc1.ParamByName('@pllevel').AsString;

stpret is being set to 0 by the ExecProc call.

SQLmonitor shows:


OLEDB - ICommandText
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME,
CASE WHEN IS_RESULT='YES' THEN 'RESULT' ELSE PARAMETER_MODE END, DATA_TYPE,
COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION), NUMERIC_SCALE,
ORDINAL_POSITION, CONVERT(BIT,1) FROM INFORMATION_SCHEMA.PARAMETERS WHERE
(SPECIFIC_CATALOG = ? or (1=2)) AND (SPECIFIC_SCHEMA = ? OR (1=2)) AND
(SPECIFIC_NAME = ? or (1=2)) AND (PARAMETER_NAME = ? or (1=1)) ORDER BY
SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION
OLEDB - SetCommandText
OLEDB - IAccessor
OLEDB - CreateAccessor
OLEDB - Execute
OLEDB - GetResult
OLEDB - IColumnsInfo
OLEDB - GetColumnInfo
OLEDB - Release
OLEDB - IAccessor
OLEDB - CreateAccessor
OLEDB - GetNextRows
OLEDB - GetData
OLEDB - ReleaseRows
OLEDB - GetNextRows
OLEDB - ReleaseAccessor
OLEDB - Release
OLEDB - ReleaseAccessor
OLEDB - Release
OLEDB - Release
{CALL "dbo"."sp_GetClientInfo"(?) }
OLEDB - SetCommandText
OLEDB - IAccessor
OLEDB - CreateAccessor
OLEDB - Execute
OLEDB - GetResult
OLEDB - IColumnsInfo
OLEDB - GetColumnInfo
OLEDB - Release
OLEDB - IAccessor
OLEDB - CreateAccessor
OLEDB - ReleaseAccessor
OLEDB - Release
OLEDB - Release

"Dwight Crevelt" <***@creveltcomputer.com> wrote in message news:47bcb47f$***@newsgroups.borland.com...
I am getting errors trying to access a stored procedure on a MSSQL server
using DBX tsqlstoredproc in D2007
Stored Procedure name sp_GetClientInfo, input param @plcard, outputs several
parameters Plfname, Pllname

The following gives an Index out of bounds error.

sqlstoredproc1.Close;
sqlstoredproc1.Params.CreateParam(ftstring,'@plcard',ptinput);
sqlstoredproc1.ParamByName('@plcard').asstring := edit1.Text;
sqlstoredproc1.ExecProc;

Removing the CreateParm line gives a Sqlstoredproc1 error parameter plcard
not found.

Removing the ParamByName line ALSO gives a MS SQL Server error
sp_GetClientInfo expecting parameter @plcard which was not provided.

using Open vs. ExecProc gives same results.

How do you get the SQLStoredProc in DBX to work?