cant execute storedprocedure from ADO

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

cant execute storedprocedure from ADO

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2004-08-17 : 06:33:39

Hi,

I am having problems executing a storedprocedure from ADO. The Sp contains a cursor. When i remove the cursot it works fine. Can someone help me to resolve this thanks

Here is the ADO code

Public Function RunSPReturnRS(ByVal strSP As String, ParamArray params() As Variant) As ADODB.Recordset
On Error GoTo errorHandler

' Create the ADO objects
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim e As ADODB.Error

Set rs = CreateObject("ADODB.Recordset")
Set cmd = CreateObject("ADODB.Command")
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = goConn
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc

collectParams cmd, params

With rs
'Work around for Temp tables
.ActiveConnection = goConn
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.CacheSize = 2

' Execute the query for readonly
'.CursorLocation = adUseClient
.Open cmd, , adOpenDynamic, adLockOptimistic
End With

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-17 : 06:40:46

it looks ok to me. post the sp code.

Go with the flow & have fun! Else fight the flow :)

miranwar
Posting Yak Master

125 Posts

Posted - 2004-08-17 : 06:46:54

Here is extract of SP Code. when i exeute the SP in the backend it returns resultset fine.

/*create report totals*/
INSERT INTO #ReportTotals
SELECT 'Item Total',
NULL,
NULL,
NULL,
NULL,
NULL,
[Revtype],
SUM([QtyOrdered]),
NULL,
SUM([UnitPrice]),
SUM([Disc]),
SUM([NetSales]),
NULL,
SUM([NetGBPSales])
FROM #ReportData
GROUP BY [Revtype]

/*create report totals*/
INSERT INTO #ReportTotals
SELECT 'Grand Total',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SUM([QtyOrdered]),
NULL,
SUM([UnitPrice]),
SUM([Disc]),
SUM([NetSales]),
NULL,
SUM([NetGBPSales])
FROM #ReportData

insert into
#ReportPrintTemp
select distinct Revtype, Rowtype from #reportData order by Rowtype,Revtype

Declare @revtype varchar(50)
Declare @RowType varchar (50)

declare revtypecursor cursor
for select distinct chrevenuetype,RowType
from #ReportPrintTemp order by RowType,chrevenuetype

open revtypecursor

fetch next from revtypecursor into @revtype,@RowType

while @@FETCH_STATUS = 0
begin

Insert Into #ReportPrint select * from #reportdata where Revtype = @revtype

insert into #ReportPrint select * from #ReportTotals where Revtype = @revtype

fetch next from revtypecursor into @revtype,@RowType
end
close revtypecursor
deallocate revtypecursor

SELECT * FROM #ReporTprint

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-17 : 07:09:26

You may need to use SET NOCOUNT ON ...

-------
Moo. :)

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2004-08-17 : 07:11:17

I'd take this a step further and attempt to get rid of the cursor....poor performance!!

miranwar
Posting Yak Master

125 Posts

Posted - 2004-08-17 : 07:24:30

sET nOCOUNT IS OFF ALREADY. iS THERE A BETTER SOLUTION FOR THIS ?

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-17 : 07:43:26

I meant like this -


/*create report totals*/
SET NOCOUNT ON 

INSERT INTO #ReportTotals
SELECT 'Item Total',
NULL,
NULL,
NULL,
NULL,
NULL,
[Revtype],
SUM([QtyOrdered]),
NULL,
SUM([UnitPrice]),
SUM([Disc]),
SUM([NetSales]),
NULL,
SUM([NetGBPSales])
FROM #ReportData 
GROUP BY [Revtype] 



/*create report totals*/
INSERT INTO #ReportTotals
SELECT 'Grand Total',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
SUM([QtyOrdered]),
NULL,
SUM([UnitPrice]),
SUM([Disc]),
SUM([NetSales]),
NULL,
SUM([NetGBPSales])
FROM #ReportData 

insert into 
#ReportPrintTemp
select distinct Revtype, Rowtype from #reportData order by Rowtype,Revtype

Declare @revtype varchar(50)
Declare @RowType varchar (50)

declare revtypecursor cursor
for select distinct chrevenuetype,RowType
from #ReportPrintTemp order by RowType,chrevenuetype

open revtypecursor

fetch next from revtypecursor into @revtype,@RowType

while @@FETCH_STATUS = 0
begin

Insert Into #ReportPrint select * from #reportdata where Revtype = @revtype

insert into #ReportPrint select * from #ReportTotals where Revtype = @revtype

fetch next from revtypecursor into @revtype,@RowType
end
close revtypecursor
deallocate revtypecursor

SET NOCOUNT OFF

SELECT * FROM #ReporTprint

-------
Moo. :)

mr_mist
Grunnio

1870 Posts

Posted - 2004-08-17 : 07:44:44

Also, could you not just remove that cursor?

-------
Moo. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources