Resolution:
Description:
===========
While making a stored procedure call using the EAI DB plugins to Microsoft SQL Server (MS-SQL), in/out parameters are set to DATE.  The out parameters of the configured stored procedure call works successfully in updating the required tables in MS SQL Server. The EAI DB plugin is able to fetch the same parameter from SQL server but fails to persist it as a case data of the same type.

Environment:
============
TIBCO iProcess Engine (SQL) v11.3.1
Microsoft SQL Server 2008 R2

Symptoms:
==========
After parsing the IN parameter from MS-SQL the persisting of case data fails with the following error in the sw_warn.log file:

EAI call-out failed(“EAISQL_ERR – 7 – Unable to Set Case Data field: &ltdate field name>”)

Cause:
=======
The DATE datatype is returning its value in the format - 'yyyy-mm-dd', which is the default string literal date format in MS-SQL. The EAI DB plugin is unable to handle this format and throws the above mentioned error while processing it.

Resolution:
==========
There are two workarounds that may be chosen to overcome this issue .

Workaround 1 – Modify the stored procedure to return the date in MM/DD/YYYY. For example:

-- RESOLVE_DATE_TEST is the stored procedure used for this test
ALTER PROCEDURE [swpro].[RESOLVE_DATE_TEST]
@return_date VARCHAR(10) OUTPUT
AS
BEGIN
-- Below convert method is used to modify the date in the required format.
  SELECT @return_date = CONVERT(VARCHAR(10), GETDATE(), 103);
END

Workaround 2 – Change data type of the field in iProcess Engine procedure to text.

References:
============
http://technet.microsoft.com/en-us/library/bb630352(v=sql.105).aspx
http://technet.microsoft.com/en-us/library/ms180878(v=sql.105).aspx#UsingDateandTimeFormats

In TIBCO iProcess Engine, an EAI DB step connecting to Microsoft SQL Server is unable to process an “IN” parameter of type DATE.