error while calling Stored Procedure from OleDB

[vijay singh]

Platform : VC++(Visual Studio 2008), Windows 2003 server,

Technology :OleDb

Database : SQL Server

Provider : SQLOLEDB

Hi,

I have developed a stored procedure which open a record set for a given SQL
and return records count as output parameter. After procedure call returned
count from this stored procedure is zero.

ALTER PROCEDURE [dbo].[ABI_SP_OPENRECORDSETANDCOUNT]
( @strOpenSQL NVARCHAR(2000), @nCount nvarchar(20) OUTPUT )

AS

BEGIN

SET NOCOUNT ON;
EXECUTE sp_executesql @strOpenSQL;
set @nCount = @@rowcount;

END

I am using OleDB to access database and calling this procedure using
CDynamicParameterAccessor accessor. Using same accessor I am successfully
able to open record set and get count using Oracle database but for SQL
Server returned count is zero.

Please help me resolve this issue.

Thanks in advance

Vijay

[Erland Sommarskog]

vijay singh (vijay...@abosoftware.com) writes:
> I have developed a stored procedure which open a record set for a given
> SQL and return records count as output parameter. After procedure call
> returned count from this stored procedure is zero.
>
>
> ALTER PROCEDURE [dbo].[ABI_SP_OPENRECORDSETANDCOUNT]
> ( @strOpenSQL NVARCHAR(2000), @nCount nvarchar(20) OUTPUT )

nvarchar(20)?

> SET NOCOUNT ON;
> EXECUTE sp_executesql @strOpenSQL;
> set @nCount = @@rowcount;

Does @strOpenSQL produce a result set? In that case you need to get
past that result set before you can get the parameter values. Use
IMultipleResults to go through the result sets.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx