[SOLVED] Can you use SQL Passthru in Domains?

September 13, 2018, 02:57 PM

can you use sql passthru in domains?

I keep getting an error saying Invalid path. The passthru works in the data server application folders.

This message has been edited. Last edited by: KellyT,

September 13, 2018, 02:59 PM

Hallway

I use SQL Passthrough all the time in domains. What does your code look like?

This is the format that I use:

  
ENGINE SQLMSS SET DEFAULT_CONNECTION <SQL Adapter>
SQL SQLMSS PREPARE SQLOUT FOR

SELECT *
FROM <TABLE NAME>

END

TABLE FILE SQLOUT
PRINT *
END

This message has been edited. Last edited by: Hallway,

September 13, 2018, 03:49 PM

KellyT

  
ENGINE SQLSYB SET DEFAULT_CONNECTION REPORTING

SQL SQLSYB
EX hr..ServiceAwardsWF;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
JOIN
GETALLEMPIDANDSSN.ANSWERSET1.SSN IN GETALLEMPIDANDSSN TO MULTIPLE
SQLOUT.SQLOUT.socialSecurityNumber IN SQLOUT TAG J0 AS J0
END
TABLE FILE GETALLEMPIDANDSSN
PRINT
     GETALLEMPIDANDSSN.ANSWERSET1.EMPLOYID AS 'Emp ID'
     J0.SQLOUT.employeeName AS 'Employee Name'
     J0.SQLOUT.employeeStatus AS 'Status'
     J0.SQLOUT.hireDate/HMDYY AS 'Hire Date'
     J0.SQLOUT.yearsOfService AS 'Service Years'
     J0.SQLOUT.homeAddress AS 'Home Address'
     J0.SQLOUT.homeCity AS 'Home City'
     J0.SQLOUT.homeState AS 'Home State'
     J0.SQLOUT.homeZip AS 'Home Zip'
     J0.SQLOUT.driverNumber AS 'Driver #'
     J0.SQLOUT.fleetId AS 'Fleet #'
     J0.SQLOUT.department AS 'Department'
     J0.SQLOUT.opsManager AS 'Ops Manager'
     J0.SQLOUT.daysInactive AS 'Days Inactive Last 5 yrs'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = enblue_theme,
$
     TITLETEXT='Service Awards',
$
TYPE=REPORT,
     COLUMN=N1,
     SQUEEZE=0.638889,
$
TYPE=REPORT,
     COLUMN=N2,
     SQUEEZE=2.250000,
$
TYPE=REPORT,
     COLUMN=N6,
     SQUEEZE=1.930556,
$
TYPE=REPORT,
     COLUMN=N7,
     SQUEEZE=1.402778,
$
TYPE=REPORT,
     COLUMN=N9,
     SQUEEZE=0.680556,
$
TYPE=REPORT,
     COLUMN=N12,
     SQUEEZE=0.416667,
$
TYPE=REPORT,
     COLUMN=N13,
     SQUEEZE=1.930556,
$
TYPE=REPORT,
     COLUMN=N14,
     SQUEEZE=2.305556,
$
ENDSTYLE
END

September 13, 2018, 06:19 PM

Hallway

So it looks like you are trying call a Sybase Stored Procedure Using SQL Passthru. I'm not familiar with Sybase, but there is documentation here: Calling a Sybase Stored Procedure Using SQL Passthru

September 14, 2018, 07:37 AM

KellyT

Thank you.

The code I have is what the documentation said to do. Like I said my fex files with the sybase stored procedure sql passthru works on the data server/application folders but DOES NOT work in the domains. We've had this problem for many years. However when we build our portals/dashboards we have to create meta data against the stored procedures. Gets very inefficient for us.

Thanks

September 14, 2018, 08:45 AM

KellyT

I looked in the event.log file and this was an error that has to do with the passthru.

[2018-09-14 07:01:17,496] ERROR [ajp-nio-8009-exec-8:IBFSService] kjt - IBFS_180914_070117-1 ERROR_IBFS_BAD_PATH(8005) Invalid path hr..ServiceAwardsWF; LOGINFO{REASON=invalid character found in path element; {IBFS_PATH}={hr..ServiceAwardsWF;}; {IBFS_PATH_ELEMENT}={hr..ServiceAwardsWF;}; {INVALID_CHARACTER}={'.'}; {AT_INDEX}={4};}

Any Ideas??

Thanks

September 14, 2018, 09:18 AM

jfr99

Hi KellyT,

Does it work putting your SP call in between -MRNOEDIT BEGIN and -MRNOEDIT END

Like this ...

-MRNOEDIT BEGIN
SQL SQLSYB
EX hr..ServiceAwardsWF;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS RPTDATA_SQL
END
-MRNOEDIT END
-RUN
? HOLD RPTDATA_SQL
-EXIT