Hi,
I have recently migrated to WF 8.2.03 from 8.2.02.
The Code which was working in old version is not working on 8.2.03.

When I do SQL Pass through on one of the tables in SQL Server. The Date field is getting converted to alphanumeric and throwing error if I use that date field as one of the Input parameters.
I have attached my code and error message below.
I couldn't recreate this issue using CAR or other sample file, as this is happening when I do SQL Pass through.

In the old version, the same code was working and when I checked the field definition of the HOLD1 file, the date field has YYMD format. But in the new version, it is showing as A10V.

Code
----
ENGINE SQLMSS SET DEFAULT_CONNECTION TESTSQLSER
SQL SQLMSS PREPARE SQLOUT FOR
Select Invoicenumber, Invoicedate
from Invoices
where ClientID = 1000
END


TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HOLD1
END

TABLE FILE HOLD1
PRINT
*
WHERE ( Invoicedate GE &InvoiceStart.(|FORMAT=YYMD).InvoiceStart:.QUOTEDSTRING )
AND ( Invoicedate LE &InvoiceEnd.(|FORMAT=YYMD).InvoiceEnd:.QUOTEDSTRING );
END
-EXIT

Error Message:
-------------

An error has occurred

Detail:
0 NUMBER OF RECORDS IN TABLE= 1923 LINES= 1923
ERROR AT OR NEAR LINE 27 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC015) THE TEST VALUE IS LONGER THAN THE FIELD FORMAT LENGTH: September 02 2018
(FOC009) Request failed validation, not executed.



Thanks,
Joshy
Webfocus 8.2.03

This message has been edited. Last edited by: FP Mod Chuck,

In SQL Server Management Studio, can you check the format of Invoicedate in Invoices? The hold file replicates the format of the table.

Is this the exact same database and table or is this pointing to a different instance of SQL Server?

Hi,

In the SQL Server, the format is DATE.
This is exactly same database and table, I am pointing in both the versions.

Thanks.

I just tested this on a table that has a DATE field and a DATETIME field. ORD_DT is a DATE field and it's a YYMD in the HOLD file.

  
ENGINE SQLMSS SET DEFAULT_CONNECTION SQL_BN
SQL SQLMSS PREPARE SQLOUT FOR
SELECT
  T1."ORD_DT",
  T1."Freight" FROM Orders T1;
END


TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HOLD1
END
-RUN
?FF HOLD1

This is what I get:

  
No output was returned
Possible causes:
- Output was directed to a destination such as a file or printer
Detail:
 0 NUMBER OF RECORDS IN TABLE=      830  LINES=    830
 FILENAME=  HOLD1
 ORD_DT        E01           YYMD
 Freight       E02           P21.4

Hi,

In the SQL Server, the Invoice Date is date format.

When I need ?FF HOLD1, I got the below:
0 NUMBER OF RECORDS IN TABLE= 1923 LINES= 1923
FILENAME= HOLD1
Invoicenumber E01 I11
Invoicedate E02 A10V

But in WF 8.2.02, it is showing i as YYMD.

Is there any settings needed in Adapter settings.

Thanks,

I know of no specific settings in the adapter that force a SQL Server Date field to show up as an integer.

If you create a synonym for this table, will you see Invoice Date as an I field?

Using 8.2.03, if I query WF_RETAIL table wf_retail_time then I get the following against the time_date column which is declared as a DATE format -

SQL SQLMSS PREPARE SQLOUT FOR
select time_date
from _wf_retail_time
END
TABLE FILE SQLOUT
BY  time_date
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HOLD1
END
-RUN
?FF HOLD1

----

 0 NUMBER OF RECORDS IN TABLE=     6575  LINES=   6575
 FILENAME=  HOLD1
 time_date     E01           YYMD

T

I have a feeling there might be another version of Invoice table in the database. If you look at the TESTSQLSER connection properties what's the default database? Try fully qualifying the select statement's FROM tablename to make sure you're looking at the right version of invoices. Something is different between the two environments and it's not the date format.

Hi,
When I created the adapter, I have specified the default database which has all the data information for the reporting(which includes Invoices table).
When I don't specify the default database, when I run the SQL Pass through, it is giving connection failed error.



And also,
When I print the Hold file,
In WF 8.2.02,it printed the date in yyyy/mm/dd
In WF 8.2.03, it printed as yyyy-mm-dd.

I assume thats why Webfocus is understanding it as A10V instead of YYMD.

Did you try creating a master file for this table? That'll tell you what WebFOCUS sees when it interrogates the SQL Server table's column format. If you're seeing an A10 field for a Date column, then SQL Server is telling WebFOCUS it's a 10 character field. If you're pointing 8203 and 8202 to the exact same table, I'd expect it to create identical column properties.

You might need to open a case with IB Tech support to figure this one out.

Yes, I have a master file and it shows the Invoice date as MDYY.
This is same in both 8.2.03 and 8.2.02.

It is getting converted only if its through SQL Passthrough.

Thank you for helping me out. I will open a case with IBI.