Options

ADODB returns error when I execute Stored Proc from Navision

jksjks Member Posts: 277
Hi all,

I want to execute followind Stored Procedure from Navision using ADODB.Connection and ADODB.Recordset.

When I execute following Stored Proc. directly from SQL using Query Analyzer it give me the correct output.

ALTER PROCEDURE [dbo].[js_sp_test]
-- Add the parameters for the stored procedure here
@A2 smallint
AS
BEGIN
SELECT a, b, c into #out1
FROM Table1
where (e = @A2)

select * from #out1;

END

But when I execute it from Navision, it give me following error:
"ADODB.Fields returned following error msg:
Item can not be found in the collection corresponding to the requested name or ordinal"

Please Help..

Comments

  • jksjks Member Posts: 277
    I read it many times.
    I found that if I do not use intermediate recordset i.e #out1 and write my stored pro. as follow then it works from Navision as well.
    AS
    BEGIN
    SELECT a, b, c
    FROM Table1
    where (e = @A2)

    END

    But my actual stored procedure is very complex. And I have to have use such many #out1 to arrive to the final result. Why using #out1 gives me such error when executed from Navision?? Do I need to set some properties of the objects of ADODB.

    Thanks for any help.
  • jksjks Member Posts: 277
    CREATE(connection);
    CREATE(recordset);
    connectionStr := STRSUBSTNO('Provider=SQLNCLI.1;Integrated Security=SSPI;Initial Catalog=%1;Data Source=%2', DatabaseName,
    ServerName);
    connection.Open(connectionStr,UserName,Password);

    sSQL := STRSUBSTNO('EXEC js_sp_test ''%1''', A1);
    recordset := connection.Execute(sSQL);

    REPEAT
    fields := recordset.Fields();

    a := fields.Item(0); //Here I get error.
    b := fields.Item(1);
    c := fields.Item(2);
    recordset.MoveNext;
    UNTIL recordset.EOF = TRUE;
  • jksjks Member Posts: 277
    I changed sSQL as you said. But it still doesn't work.
    Also why should I use Command? Can't I achieve the same using Connection?
  • jksjks Member Posts: 277
    Thanks for your help.
    Will use ADODB.Command and try to fix the problem.
Sign In or Register to comment.