return values and recordset in one stored procedure

[rover]

Hi

We have an application that runs fine on SQL 2000. Due to other applications we are forced to move to SQL 2005.

One Stored Procedure isn't working on the new SQL 2005. It's a SP with output parameters and a recordset. It's working fine on SQL 2000.

The data is returned through OLEDB to a VB6 application. In this application the output parameters are working fine (can access them). But the recordset isn't working. Both EOF en BOF are true...

Executing the SP from SQL Server Management Studio works fine.

Is it not allow to have output parameters and a recordset anymore? Or is the code different?

I hope you can help...


Below is the SP and the VB6 code:

ALTER PROCEDURE [dbo].[ADM_LS_SelectUserPassword]
  @UserName   varchar(16),
  @UserID  int  OUTPUT,
  @UserPassword  varchar(16) OUTPUT,
  @UserNickname  varchar(16) OUTPUT,
  @UserErrCount smallint  OUTPUT,
  @UserLastname varchar(32) OUTPUT,
  @Expired int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
 
DECLARE @ExpireDate smalldatetime,@PWChanged smalldatetime
 
 
SELECT distinct @UserNickname = PAEm_Nickname, @UserLastname = PAEm_Lastname, @UserPassword = PAEm_Password, @UserID = PAEm_ID, @UserErrCount = PAEm_ErrCount,@PWChanged = PAEm_PWLastChanged
FROM ADM_PA_Employee, ADM_PA_CntrGlobal
WHERE PAEm_Username = @Username and PACG_FK_PAEm_ID = PAEm_ID and (PACg_Enddate > dateadd(day,-1,getdate()) or PACg_Enddate is null)
 
--Kijken en instellen of het wachtwoord niet is verlopen
SET @ExpireDate = DATEADD(mm,CONVERT(int,(SELECT CMPa_Value FROM ADM_CM_PARAMETERS WHERE CMPa_Code = 'PWExpire')),@PWChanged)
IF @ExpireDate < getdate() SET @Expired = 1
ELSE SET @Expired = 0
 
IF @UserPassword != '' 
begin 
if isnull(@UserNickname,'') = '' 
  SELECT @UserNickname = PAEm_Firstname
  FROM ADM_PA_Employee
  WHERE PAEm_Username = @Username
end
 
 
IF @UserID > 0
  SELECT distinct sysobjects.name
  FROM sysobjects, sysprotects, sysusers, ADM_LS_Groups, ADM_LS_EmployeeGroups, ADM_PA_Employee
  WHERE xtype = 'P' 
    and sysobjects.id = sysprotects.id 
    and sysprotects.uid = sysusers.uid
    and (sysprotects.protecttype = 204 or sysprotects.protecttype = 205)
    and LSGr_Login = sysusers.name
    and LSGr_ID = LSEG_FK_LSGr_ID
    and LSEG_FK_PAEm_ID = PAEm_ID
    and PAEm_ID = @UserID
  ORDER BY sysobjects.nameELSE
 SELECT * FROM ADM_PA_Employee WHERE PAEm_ID = 1 and PAEm_ID = 2
END
 

dbCom.CommandText = "ADM_LS_SelectUserPassword"
dbCom.CommandType = adCmdStoredProc
dbCom.Parameters.Refresh
 
dbCom(1).Value = strUserName
 
dbRst.Open dbCom
 
 
 If dbRst.EOF = True Or dbRst.BOF = True Then
  dbRst.Close
  If Not IsNull(dbCom(2).Value) Then
    Err.Raise 1004, , "User has no permissions: " & strUserName
  Else
    Err.Raise 1000, , "Unknown user: " & strUserName
  End If
Else
  ReDim strGroup(0)
  Do While Not dbRst.EOF
    ReDim Preserve strGroup(UBound(strGroup) + 1)
    strGroup(UBound(strGroup) - 1) = dbRst.Fields(0)
    dbRst.MoveNext
  Loop
  dbRst.Close
End If
 

[rover]

A little update.

So it seems that the issue is not related to the return values and recordset.

I have created a small test application which returns a recordset just fine.

Could it be possible that SQL 2005 prevents information from sysobject to be exported (using SQL)?

I have this in a stored procedure where I fetch a list of stored procedures where a user has rights:

SELECT distinct sysobjects.name
  FROM sysobjects, sysprotects, sysusers, ADM_LS_Groups, ADM_LS_EmployeeGroups, ADM_PA_Employee
  WHERE xtype = 'P' 
    and sysobjects.id = sysprotects.id 
    and sysprotects.uid = sysusers.uid
    and (sysprotects.protecttype = 204 or sysprotects.protecttype = 205)
    and LSGr_Login = sysusers.name
    and LSGr_ID = LSEG_FK_LSGr_ID
    and LSEG_FK_PAEm_ID = PAEm_ID
    and PAEm_ID = @UserID
  ORDER BY sysobjects.name