Defining parameters for executing stored procedure - Classic ASP

Question

I am trying to update some old functionality. There is a stored procedure that has one input parameter and two output parameters. I am getting the following error when I try to execute the code:

Microsoft OLE DB Provider for ODBC Drivers error '80040e21'

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

/student.asp, line 30

Stored Procedure:

ALTER PROCEDURE [Api].[GetKeyByAuthId]
    @AuthenticationId uniqueidentifier
,   @Key int = NULL OUTPUT
,   @type varchar(25) = NULL OUTPUT

Direct call works great:

USE [Development]
GO

DECLARE @return_value int,
        @Key int,
        @type varchar(25)

EXEC    @return_value = [Api].[GetKeyByAuthId]
        @AuthenticationId = '0550F579-DBDA-4C41-82B3-453841A6232E',
        @Key = @Key OUTPUT,
        @type = @type OUTPUT

SELECT  @Key as N'@Key',
        @type as N'@type'

SELECT  'Return Value' = @return_value

GO

Authentication Id is unique identifier and is received via query string on the page. Here is my code, line 30 is cmd.Execute:

var cmd = Server.CreateObject("ADODB.Command");
cmd.CommandText = "Api.GetKeyByAuthId";
cmd.CommandType = 4;   // Stored procedure


var p = cmd.Parameters;
p.Append(cmd.CreateParameter("@AuthenticationId", 72, 1));
p.Append(cmd.CreateParameter("@Key", 3, 2, 9));
p.Append(cmd.CreateParameter("@type", 200, 2, 25));

cmd("@AuthenticationId") = aid;

cmd.ActiveConnection = Conn;
cmd.Execute();

Key = cmd.Parameters("@Key");
type = cmd.Parameters("@type");

score 1 · Accepted Answer · edited May 23 '17 at 11:50

After discussing your data types in the comments

You mentioned that the Stored Procedure expects @Key as an Int but the underlying table data type for the field is BigInt.

Warning

The problem with this approach is that the conversion from BigInt to Int is dangerous (see SQL - safely downcast BIGINT to INT). Most of the time you will get
Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type int.
You can suppress the exception using a combination of
SET ANSI_WARNINGS OFF -- Divide-by-zero and arithmetic overflow errors cause null values to be returned
SET ARITHABORT OFF    -- Don't terminate the query on Divide-by-zero and arithmetic overflow errors
SET ARITHIGNORE ON    -- Suppress errors from Divide-by-zero and arithmetic overflow errors

My guess is the

Multiple-step OLE DB operation generated errors

error in your Stored Procedure is being caused by an Arithetic overflow error because the value you are retrieving in @Key is too big to fit in an Int data type.

When possible I would speak to your developers and explain the situation and see whether the Stored Procedure can be modified to pass @Key as BigInt.

score 0 · Answer 2 · edited May 20 '14 at 16:34

0

These two changes worked for me at this time:

p.Append(cmd.CreateParameter("@AuthenticationId", 200, 1, 37));

var KeyDb = cmd.CreateParameter("@Key", 131, 2);
KeyDb.NumericScale = 0;
KeyDb.Precision = 9;
p.Append(KeyDb);

edited May 20 '14 at 16:34

user692942

16,398
7
76
175

answered May 19 '14 at 20:39

learning...

3,104
10
58
96

Interesting so you're using `adNumeric`, is your `@Key` parameter defined as `NUMERIC` or `DECIMAL` and not `INT` in the stored procedure `[Api].[Getkeybyauthid]`? I'm guessing so explains why your direct call works because SQL will automatically handle the conversion from `INT` to `NUMERIC` for you whereas `ADODB.Command` will expect the exact data type. – user692942 May 20 '14 at 16:35
@Key is bigint in the table. – learning... May 20 '14 at 17:57
In that case you should be using `adBigInt` (20), but it's not the table data type but the stored procedure parameter data type that is important. – user692942 May 20 '14 at 19:21
I am bit confused now... sproc has this as int and i don't have any control over it. Db team refused to change it (and i understand) as the same sproc may be used by other apps as well. Even though it is int in the sproc, do i still need to set it up as adBigInt (20) at my end? – learning... May 21 '14 at 15:06
That is a strange one. You shouldn't have to because the Stored Procedures definition for `@Key` is an `INTEGER`. That is really dangerous though because your `@Key` field could contain a value larger then `INTEGER` in which case you might get strange behaviour when the Stored Procedure returns the `BIGINT` as an `INTEGER`. See [SQL - safely downcast BIGINT to INT](http://stackoverflow.com/questions/7097171/sql-safely-downcast-bigint-to-int). – user692942 May 21 '14 at 15:12
1

@Lankymart please put your above comment as an answer so that i can mark it as accepted answer. – learning... May 21 '14 at 19:44

score -1 · Answer 3 · answered May 20 '14 at 13:12

Couldn't you just execute it like this?

var1 = ""
var1 = var1 & "USE [Development]"
var1 = var1 & " " & vbCrLf
var1 = var1 & "go"
var1 = var1 & " " & vbCrLf
var1 = var1 & "DECLARE @return_value INT, " & vbCrLf
var1 = var1 & "        @Key          INT, " & vbCrLf
var1 = var1 & "        @type         VARCHAR(25)"
var1 = var1 & " " & vbCrLf
var1 = var1 & "EXEC @return_value = [Api].[Getkeybyauthid] " & vbCrLf
var1 = var1 & "  @AuthenticationId = '0550F579-DBDA-4C41-82B3-453841A6232E', " & vbCrLf
var1 = var1 & "  @Key = @Key output, " & vbCrLf
var1 = var1 & "  @type = @type output"
var1 = var1 & " " & vbCrLf
var1 = var1 & "SELECT @Key  AS N'@Key', " & vbCrLf
var1 = var1 & "       @type AS N'@type'"
var1 = var1 & " " & vbCrLf
var1 = var1 & "SELECT 'Return Value' = @return_value"
var1 = var1 & " " & vbCrLf
var1 = var1 & "go "
cmd.execute(var1)

No. This will not give me the return values to use inside the app. — learning..., May 20 '14 at 14:07
@learning... Actually it will (if you set-up the parameters correctly and assign `OUTPUT` where required), but I wouldn't do it this way anyway. — user692942, May 20 '14 at 15:31

Defining parameters for executing stored procedure - Classic ASP

3 Answers3

Linked