ADODB returns error when I execute Stored Proc from Navision

jks · 2006-09-19

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..

jks · 2006-09-19

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.

jks · 2006-09-19

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;

jks · 2006-09-19

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?

jks · 2006-09-19

Thanks for your help.
Will use ADODB.Command and try to fix the problem.

ADODB returns error when I execute Stored Proc from Navision

Comments

Categories