We have a SQL Server procedure with 20K lines of code. We will execute the procedure 18 times and syntax is created dynamically from PowerBuilder. Attached the dynamic query.
If the query is executed from SSMS there is no issue, it is inserting into all tables for all the input values we give.
But from PowerBuilder it is executing only 4 times/sometimes 6/sometimes 7 instead of 18 times. This is really weird issue that we are facing
The same procedure is working fine in Sybase database. After we migrate to SQL Server we are facing the issue.
We are using PowerBuilder 2017
1/ Do we need to add any DB parms to overcome this issue?
[ODBC]
DBMS=ODBC
Database=database
DBParm="TrimSpaces=1,ConnectString='DRIVER={SQL Server};SERVER=server\server;DATABASE=database;PORT=1435;PROTOCOL=TCPIP;UID=uid;PWD=pwd';PBNewSPInvocation=YES,CallEscape=NO"
User_Id=test
2/ Any other changes need to do to SQL server ?
Sample PB Code for Dynamic SQL-
DECLARE @temp_error int
DECLARE @return_code int
DECLARE @return_message varchar(255)
DECLARE @temp_message varchar(255)
execute @return_code = our_stored_procedure1 2345678, 402056421, '0012345', 'testing', 'PEND', ' ', 'GP', 'OURAPP', 'NO', ' ', 'NACCT', 'N', 'N'
SELECT @temp_error = @@error
IF @return_code <> 0
begin
IF @temp_error != 0
begin
SELECT @temp_message = convert(char(5), @temp_error)
end
ELSE
begin
SELECT @temp_message = convert(char(5), @return_code)
end
SELECT @temp_message = @temp_message + '|' +
' our_stored_procedure1 2345678, 402056421, "0012345", "testing", "PEND", '' '', "GP", "OURAPP", "NO", '' '', "NACCT", "N", "N" '
raiserror (50999,16,1, @temp_message)
return
end
execute @return_code = our_stored_procedure 2345678, 'BSCL', 'GP'
SELECT @temp_error = @@error
IF @return_code <> 0
begin
IF @temp_error != 0
begin
SELECT @temp_message = convert(char(5), @temp_error)
end
ELSE
begin
SELECT @temp_message = convert(char(5), @return_code)
end
SELECT @temp_message = @temp_message + '|' +
' our_stored_procedure 2345678, "BSCL", "GP" '
raiserror (50999,16,1, @temp_message)
return
end
execute @return_code = our_stored_procedure 2345678, 'DADD', 'GP'
SELECT @temp_error = @@error
IF @return_code <> 0
begin
IF @temp_error != 0
begin
SELECT @temp_message = convert(char(5), @temp_error)
end
ELSE
begin
SELECT @temp_message = convert(char(5), @return_code)
end
SELECT @temp_message = @temp_message + '|' +
' our_stored_procedure 2345678, "DADD", "GP" '
raiserror (50999,16,1, @temp_message)
return
end
It has become a show stoper for us. Please help to put your thoughts/suggesstions on this. Thank you
