NYCBabak . Posted February 4, 2021 Share Posted February 4, 2021 Does the non-working Connection have a default database assigned to it Link to comment Share on other sites More sharing options...
Deana Goeken Posted February 4, 2021 Author Share Posted February 4, 2021 Yes, the default db is explicitly specified in the adapter configuration. Link to comment Share on other sites More sharing options...
NYCBabak . Posted February 4, 2021 Share Posted February 4, 2021 Could you post the full text of a TABLE FILE END request Link to comment Share on other sites More sharing options...
Deana Goeken Posted February 4, 2021 Author Share Posted February 4, 2021 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, $ Link to comment Share on other sites More sharing options...
NYCBabak . Posted February 4, 2021 Share Posted February 4, 2021 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 Link to comment Share on other sites More sharing options...
Deana Goeken Posted February 4, 2021 Author Share Posted February 4, 2021 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 Link to comment Share on other sites More sharing options...
NYCBabak . Posted February 4, 2021 Share Posted February 4, 2021 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. Link to comment Share on other sites More sharing options...
Deana Goeken Posted February 4, 2021 Author Share Posted February 4, 2021 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. Link to comment Share on other sites More sharing options...
Deana Goeken Posted February 4, 2021 Author Share Posted February 4, 2021 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 Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now