Description:
I have created the following procedure in my SQL Server Database:
<- Begin
create PROCEDURE ChangePW
@UserName varchar(4000) out,
@PW varchar(4000) out
AS
Update smuser
set password = @PW
where Smuser.name= @UserName
return 0
-> End
And I have modified my sql scheme in order to change the password via store procedure:
Set User Password: call ChangePW %s , %s
However, the procedure fails with the following errors:
Snippet of smps.log:
<- Begin
[2128/1848][Wed Mar 04 2009 16:47:19][SmDsOdbcProvider.cpp:1688][ERROR] Database Error executing query ( '{ ? = CALL ChangePW(?,?) }'). Error: Internal Error: Database error. Code is -4007 (DBMSG: <<<
State = HY010
Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>) .
-> End
Profiler:
<- Begin
[16:25:09][Finish processing SQL statement.][][][][CDb.cpp:233][2128][1848][03/04/2009][16:25:09.171][CSmRecordset::DoSelect]
[Exception][][][][][][][][][][][][Internal Error: Database error. Code is -4007 (DBMSG: <<< State = HY010
Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>)][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][{ ? = CALL ChangePW(?,?) }][][][][][][][][][]
[16:25:09][Caught an exception 'Internal Error: Database error. Code is -4007 (DBMSG: <<<
State = HY010
Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>)'][][][][CDb.cpp:405][2128][1848][03/04/2009][16:25:09.171][CSmRecordset::Open][][][][][][][][][][][][][Internal Error: Database error.
Code is -4007 (DBMSG: <<< State = HY010
Internal Code = 0 - [DataDirect][ODBC SQL Server Driver]Function sequence error>>>)][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][][]
-> End
I'm running SiteMinder Policy Server R12 SP1 CR02 on windows 2003 and using SQL Server 2005 as User store.
Solution:
The issue is with the store procedure, it is not returning all the information as expected by SiteMinder Policy Server.
Please make the following changes:
You can use the following procedure as template:
<- Begin
create PROCEDURE ChangePW
@PW varchar(4000) out,
@UserName varchar(4000) out
AS
SET NOCOUNT on;
Update smuser
set password = @PW
where Smuser.name= @UserName
select name
from smuser
where Smuser.name= @UserName
return 0
-> End