November 18, 2016 at 6:28 am
Hi,
my colleagues have a problem where IBM DataPower acts as the gateway between SqlServer and their software (I don't know the details).
What happens is that with some customer numbers they always exceed the time-out setting (10000 ms) and when we tried to isolate the reason, we found out that when trace was on and customer tried to fetch data, the @P1 was set to NULL with these failing customers and some INT with the succeeding ones. See below
declare @p1 int
set @p1=16
exec sp_prepexec @p1 output,N'@P1 varchar(999)',N'EXEC ASIAKAS.uspESBhaeMyonnetytTKElakkeet_V2_0 @P1','24979'
select @p1
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(999)',N'EXEC ASIAKAS.uspESBhaeMyonnetytTKElakkeet_V2_0 @P1','897325'
select @p1
declare @p1 int
set @p1=NULL
exec sp_prepexec @p1 output,N'@P1 varchar(999)',N'EXEC ASIAKAS.uspESBhaeMyonnetytTKElakkeet_V2_0 @P1','897325'
select @p1
Sql Server is generating the @P1 (statement handle), so why it might fail with some customers only - It is just another value in the parameter above? Seems ridiculous.
Everything works ALWAYS nicely and FAST when the proc is run with Management Studio.
Could it be that DataPower has some fault in the API when it communicates with Sql? (I don't know is it ODBS, OleDB,...)
If so, how could it be only with some parameter values (cust numbers)?
Sorry my ignorance but I don't even know is SqlServer generating the sp_prepexec etc..or has the DataPower something to do with it?
Could it be better with sp_executesql, sp_execute...?
V
November 18, 2016 at 6:32 am
Quick thought, could this be a permission problem?
😎
November 18, 2016 at 6:43 am
I hope it would but I don't think so. At least not from DB perspective not and can't see why DataPower would/could deny anything from certain customers...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply