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
-
1 16672
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
-
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Vipul Pathak |
last post by:
Hello Friends !
I have the Following Code, that Executes a Stored Procedure and Attempt to
read a Returned Integer Value from the StoredProc.
But It gives Error ...
ADODB.Command (0x800A0BB9)...
|
by: JT |
last post by:
how can i see a stored procedures return value in ASP??
|
by: sumGirl |
last post by:
Hi all. I have a stored procedure on my sql server that returns a
simple informtation about that tim a database was backed up. I owuld
like to create an HTA that operator types can look at to make...
|
by: Daniel |
last post by:
hi ng,
i am newbie to sqlserver and my problem seems simple, but i didn't find
information about it:
How can i display the
RETURN @x
value of a stored procedure in the sql analyzer of the...
|
by: stjulian |
last post by:
I have a stored procedure which returns 2 tables and 1 output value. I want
the first table to be assigned to rs1 and the second to rs2. However when I
run this, I get the following error as I...
|
by: colmkav |
last post by:
Can someone tell me how I can access the return value of a function
called from Oracle as opposed to a store proc from oracle? my oracle
function is get_num_dates_varposfile. I am only used to...
|
by: Dooza |
last post by:
I have a stored procedure that takes a number of inputs, does a bulk
insert, and then outputs a recordset. When I run the stored procedure in
Server Management Studio I also get a return value from...
|
by: SethM |
last post by:
I have a stored procedure that returns a record set. I want to
functionalize this so I can have multiple presentations of the same
record set. However, I can not get rs_event.open StoreProc to pass...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |