MSSQL Stored Procedure error

[danh]

I'm getting the following error when trying to run a store procedure in MSSQL:
"Internal Error - Data Truncated - The buffer for receiving data is too short"

I think it has something to do with Alpha, because I can run it directly in SSMS and it works fine.

Here is the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[sp_test]
	-- Add the parameters for the stored procedure here
	@whatCompany char(32), 
	@whatUser varchar(32), 
	@whatData CHAR(150) OUTPUT
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @whatSORefNum VARCHAR(25);
	DECLARE @whatWORefNum VARCHAR(25);
	DECLARE @whatTaskCnt INT;
	DECLARE @whatResult VARCHAR(25);

    -- Insert statements for procedure here
	SET @whatResult = 'success';

	SET @whatData = CONCAT('{"result":"',isnull(@whatResult,'null'),'","WORefNum":"',isnull(@whatWORefNum,'null'),'","SORefNum":"',isnull(@whatSORefNum,'null'),'","TaskCnt":',isnull(@whatTaskCnt,0),'}');

END
GO

Following is my interactive window code, and the results I'm getting:

dim cn as SQL::Connection
?cn.open("::Name::conn")
= .T.

dim sqlStmt as c = "{CALL sp_test(:whatCompany, :whatUser, :whatData)}"
dim args as SQL::Arguments
args.Set("whatCompany","Test Company")
args.Set("whatUser","Test User")
args.SetNull("whatData","C",SQL::ArgumentUsage::InputOutputArgument)

?cn.Execute(sqlStmt,args)
= .F.

?cn.CallResult.Text
= "Internal Error - Data Truncated - The buffer for receiving data is too short"

Any input would be appreciated!
Thanks, Dan

[nlights]

Re: MSSQL Stored Procedure error

Since you set your arguments as char(32) and varchar(32) in your stored procedure, you have to make sure to make the argument strings in your Xbasic exactly the same length. For example, your whatCompany argument string ("Test Company") is 12 characters long, so if you add 20 spaces to it it should resolve the problem (repeat it for the other argument, too.) you could set your SQL procedure arguments as varchar(), in which case you shouldn't have to worry about the xbasic argument string sizes.

[danh]

Re: MSSQL Stored Procedure error

Thank you for that info! It helped point me to the solution.

From what I'm seeing, it appears the RETURN argument (whatData in my case), needs to be long enough to accommodate the length of the output string.

So, it works if I change this argument:
args.SetNull("whatData","C",SQL::ArgumentUsage::InputOutputArgument)

to:
args.Set("whatData",padr("",150," "),SQL::ArgumentUsage::InputOutputArgument)

Technically, it only needs to be about 70 characters long, because that's the length of the text this store procedure is returning.

Thanks again.