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
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.
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;
Also why should I use Command? Can't I achieve the same using Connection?
Will use ADODB.Command and try to fix the problem.