I created the synonym for a new SQL stored procedure via the...

[NYCBabak .]

Does the non-working Connection have a default database assigned to it

[Deana Goeken]

Yes, the default db is explicitly specified in the adapter configuration.

[NYCBabak .]

Could you post the full text of a TABLE FILE END request

[Deana Goeken]

Here is the TABLE FILE I also included the .acx and .mas files. I used the console to initially create the synonym. For @FROMDATE and @TODATE I changed USAGE=HYMMDs, ACTUAL=HYMMDs to USAGE=MDYY, ACTUAL=DATE.

TABLE FILE USP_RPTMONTHLYPOPULATIONSUMMARY

PRINT REPORTFACILITYCODE

REPORTFACILITYNAME

REPORTFACILITYSORTORDER

TRANSFERFACILITYCODE

TRANSFERFACILITYNAME

SECTIONHEADER

SECTIONSORTORDER

LINESORTORDER

LABEL1

COUNT1

PERCENT1

LABEL2

COUNT2

PERCENT2

LABEL3

COUNT3

PERCENT3

LABEL4

COUNT4

PERCENT4

LABEL5

COUNT5

PERCENT5

WHERE @FACILITYCODE EQ &LOC

AND @FROMDATE EQ &RPDT1

AND @TODATE EQ &RPDT2

END

SEGNAME=INPUT, CONNECTION=WCIS_Test,

STPNAME=dbo.usp_RptMonthlyPopulationSummary, $

SEGNAME=OUTPUT, STPRESORDER=0, $

SEGNAME=ANSWERSET1, STPRESORDER=1, $

FILENAME=USP_RPTMONTHLYPOPULATIONSUMMARY, SUFFIX=SQLMSS , $

SEGMENT=INPUT, SEGTYPE=S0, $

FIELDNAME=@FACILITYCODE, ALIAS=P0001, USAGE=A4, ACTUAL=A4,

MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $

FIELDNAME=@FROMDATE, ALIAS=P0002, USAGE=MDYY, ACTUAL=DATE,

MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $

FIELDNAME=@TODATE, ALIAS=P0003, USAGE=MDYY, ACTUAL=DATE,

MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $

SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $

FIELDNAME=@RETURN_VALUE, ALIAS=P0000, USAGE=I11, ACTUAL=I4, $

SEGMENT=ANSWERSET1, SEGTYPE=S0, PARENT=INPUT, $

FIELDNAME=REPORTFACILITYCODE, ALIAS=ReportFacilityCode, USAGE=A4, ACTUAL=A4,

MISSING=ON, $

FIELDNAME=REPORTFACILITYNAME, ALIAS=ReportFacilityName, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=REPORTFACILITYSORTORDER, ALIAS=ReportFacilitySortOrder, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=TRANSFERFACILITYCODE, ALIAS=TransferFacilityCode, USAGE=A4, ACTUAL=A4,

MISSING=ON, $

FIELDNAME=TRANSFERFACILITYNAME, ALIAS=TransferFacilityName, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=SECTIONHEADER, ALIAS=SectionHeader, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=SECTIONSORTORDER, ALIAS=SectionSortOrder, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=LINESORTORDER, ALIAS=LineSortOrder, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=LABEL1, ALIAS=Label1, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=COUNT1, ALIAS=Count1, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=PERCENT1, ALIAS=Percent1, USAGE=P10.2, ACTUAL=P8,

MISSING=ON, $

FIELDNAME=LABEL2, ALIAS=Label2, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=COUNT2, ALIAS=Count2, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=PERCENT2, ALIAS=Percent2, USAGE=P10.2, ACTUAL=P8,

MISSING=ON, $

FIELDNAME=LABEL3, ALIAS=Label3, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=COUNT3, ALIAS=Count3, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=PERCENT3, ALIAS=Percent3, USAGE=P10.2, ACTUAL=P8,

MISSING=ON, $

FIELDNAME=LABEL4, ALIAS=Label4, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=COUNT4, ALIAS=Count4, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=PERCENT4, ALIAS=Percent4, USAGE=P10.2, ACTUAL=P8,

MISSING=ON, $

FIELDNAME=LABEL5, ALIAS=Label5, USAGE=A50V, ACTUAL=A50V,

MISSING=ON, $

FIELDNAME=COUNT5, ALIAS=Count5, USAGE=I11, ACTUAL=I4,

MISSING=ON, $

FIELDNAME=PERCENT5, ALIAS=Percent5, USAGE=P10.2, ACTUAL=P8,

MISSING=ON, $

[NYCBabak .]

Can you hard code the values for the & variables and see if that makes a difference

Can you get a SQL trace to see what WebFOCUS is generating and if that SQL runs in SQL Server Mgmt Studio.

Is there any chance the non-working connection is configured as MSODBC instead of SQLMSS

[Deana Goeken]

I have tried hardcoding the values for the variables, no changes.

I am not sure how to do a sql trace as I have not done this before. I found an example I used. I added

SET TRACEOFF = ALL

SET TRACEON = SQLAGGR//CLIENT

SET TRACEON = STMTRACE//CLIENT

SET TRACEON = STMTRACE/2/CLIENT

SET TRACEUSER = ON

at the beginning of my code. Nothing is returned to the client. View source returns

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000

: Microsoft OLE DB Provider for SQL Server: [42000] Syntax error or access

: violation

L (FOC1406) SQL OPEN CURSOR ERROR.

I did enable Traces and check the .trc file, but I dont see anything that looks like a sql statement.

 

I did double check and there are no non-working connections configured incorrectly.

 

My other focexecs which table file from a stored procedure are still working correctly.

Thank you

[NYCBabak .]

The Trace code you have should generate SQL syntax. I assume if you go to the Reporting Server Console and right click the connection and click Test, you dont get any errors. When you sample data do you get prompted for the parameters Could there be another master file with the same name in your path As a test create a master file and call it something very unique like DG_TESTXX and try a TABLE FILE off of that. If there are duplicate masters you could also use the WHENCE command to make sure its the right master. WHENCE USP_RPTMONTHLYPOPULATIONSUMMARY MASTER will return the directory where WebFOCUS is finding that master file.

Could this be caused by your modification of the Date field format and actuals in the master file

Im running out of ideas. You may have to reach out to ibi tech support.

[Deana Goeken]

Well, you may have been running out of ideas, but you had the answer. The .acx file was in another directory, with the wrong connection. I deleted the incorrect directory and the report now works from both the report console and my test application.

Thank you so much for your help.

[Deana Goeken]

I created the synonym for a new SQL stored procedure via the WebFOCUS console. I tested the synonym using Sample Data. The data is returned as expected. I am encountering problems when I use TABLE FILE or SQLMSS.

-SET &LOC = All;

-SET &RPTD1 = 12012020;

-SET &RPDT2 = 12312020;

TABLE FILE StoredProcName

WHERE @LOC = &LOC

@Date1 = &RPDT1

@Date2 = &RPDT1

Returns

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 42000 : Microsoft OLE DB Provider for SQL Server: [42000] Syntax error or access : violation (FOC1406) SQL OPEN CURSOR ERROR.

SET SQLENGINE = SQLMSS

SQL SQLMSS SET VARCHAR OFF

ENGINE SQLMSS SET DEFAULT_CONNECTION DBNAME

ENGINE SQLMSS SET CONVERSION LONGCHAR ALPHA

END

SQL SQLMSS

EX usp_RptMonthlyPopulationSummary &LOC, &RPDT1, &RPDT2;

Returns

(FOC1400) SQLCODE IS 2812 (HEX: 00000AFC) XOPEN: 42000

: Microsoft OLE DB Provider for SQL Server: [42000] Could not find stored

: procedure usp_RptMonthlyPopulationSummary .

(FOC1405) SQL PREPARE ERROR.

I have used both of the above methods successfully for other stored procedures. I initially created the TABLE FILE version in my development environment where it works correctly. I have compared the .mas and .acx files in the 2 environments. The only difference is the CONNECTION=DB where DB is the correct DB name for each environment.

Any suggestions will be appreciated. Deana