if you are getting errors in the SP, try run it from within Query Analyser. it will show you the errors and on whatline they are
EXEC sp_ktChgsPmtsByDate '03/09/2002', '04/09/2002' try that from within QA and see what the error is. you should always EXEC your SP form within QA to debug and make sure there are no errors. it hard to try to debug a SP by going off of what CF tells you. Anthony Petruzzi Webmaster 954-321-4703 [EMAIL PROTECTED] http://www.sheriff.org -----Original Message----- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 10:53 AM To: CF-Talk Subject: RE: Still Need Help with Stored Procedure. what error are you getting? -----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED]] Sent: 10 April 2002 15:48 To: CF-Talk Subject: Still Need Help with Stored Procedure. Stored Procedure Help!!! We still have not figured out why we cannot access the following stored procedure with cold fusion. We can use do it with ASP, but if we are going to have to use ASP to access all the stored procedures (there are several of them) then the reason to migrate to Cold Fusion is diminished. Following is all the information I could gather about how we are trying to do this. Thanks Ian Skinner [EMAIL PROTECTED] www.ilsweb.com ---------------------------------------------------------------------------- ---- COLD FUSION STORED PROCEDURE CODE <CFSTOREDPROC PROCEDURE="sp_ktChgsPmtsByDate" DATASOURCE="Paragon" USERNAME="sa" PASSWORD="" PROVIDER="OLEDB" DBSERVER="SQL6932440" DBNAME="paragon" RETURNCODE="YES" DEBUG> <CFPROCRESULT NAME = YestPayments> <CFPROCPARAM TYPE="In" DBVARNAME="@SDate" VALUE="'#SDate#'" CFSQLTYPE="CF_SQL_DATE"> <CFPROCPARAM TYPE="In" DBVARNAME="@EDate" VALUE="'#EDate#'" CFSQLTYPE="CF_SQL_DATE"> </CFSTOREDPROC> ---------------------------------------------------------------------------- ---- MOST COMMON ERROR MESSAGE RECEIVED Error Occurred While Processing Request Error Diagnostic Information OLEDB Error Code = 0 NULL Interface Ptr SQL = "sp_ktChgsPmtsByDate" Data Source = "Paragon" The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (18:1) to (26:7) in the template file E:\Inetpub\wwwroot\MarshallReports\YestPaymntsToPatAccts.cfm. Date/Time: 03/22/02 09:50:37 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 4.0) Remote Address: 172.16.3.104 ---------------------------------------------------------------------------- ---- WITH SOME CONFIGURATIONS OF THE CFSTOREDPROC TAG, WE RECEIVE THIS ERROR MESSAGE Error Occurred While Processing Request Error Diagnostic InformationData Access Error Unknown Data Access Error. SQL = "sp_ktChgsPmtsByDate2" Data Source = "Paragon" The error occurred while processing an element with a general identifier of (CFSTOREDPROC), occupying document position (39:1) to (41:18) in the template file E:\Inetpub\wwwroot\MarshallReports\YestPaymntsToPatAccts.cfm.Date/Time: 04/05/02 11:14:08Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 4.0)Remote Address: 172.16.3.104 ---------------------------------------------------------------------------- ---- HERE IS THE SQL CODE IN THE STORED PROCEDURE. /* Payments (Detail) to Patient Account made during a Date Range sorted By Visit CREATED BY KEVIN TRAMMEL 3/2002 */ @SDate Datetime=NULL, @EDate Datetime=NULL AS -- Massage the date parameters to suitable boundary values: exec sp_ktDateMaker @SDate,@EDate OUT SELECT RTRIM(VPM300_PAT_VISIT.vst_ext_id) VisitID, Payment=(ISNULL(-1*(VPB110_PMT_ADJ_DETAIL.pmt_pst_at),0)), TPB900_CHG_CODE_MST.chg_cod_ext_id ChargeCd, VPB110_PMT_ADJ_DETAIL.pmt_dtl_ds PaymentDetail, PaymentPostDt=CONVERT(varchar(10),VPB110_PMT_ADJ_DETAIL.pmt_pst_ts,101), PatNam=RTRIM(VSM040_PERSON_HDR.lst_nm) + ", " + RTRIM(VSM040_PERSON_HDR.fst_nm), AdmitDt=CONVERT(datetime,CONVERT(char(12),VPM300_PAT_VISIT.adm_ts,101)), ExtFinClass, VPB110_PMT_ADJ_DETAIL.vst_int_id FROM ((((VPB110_PMT_ADJ_DETAIL INNER JOIN VPM300_PAT_VISIT ON VPB110_PMT_ADJ_DETAIL.vst_int_id=VPM300_PAT_VISIT.vst_int_id) INNER JOIN VSM040_PERSON_HDR ON VPM300_PAT_VISIT.psn_int_id = VSM040_PERSON_HDR.psn_int_id) LEFT JOIN VPB200_BILLING_INVOICE ON VPB200_BILLING_INVOICE.vst_int_id=VPM300_PAT_VISIT.vst_int_id) INNER JOIN v_FinClassConv ON VPM300_PAT_VISIT.fin_cls_cd=v_FinClassConv.IntFinClass) INNER JOIN TPB900_CHG_CODE_MST ON VPB110_PMT_ADJ_DETAIL.chg_cod_int_id = TPB900_CHG_CODE_MST.chg_cod_int_id WHERE (((VPB110_PMT_ADJ_DETAIL.acc_pcs_dt)>=@SDate And (VPB110_PMT_ADJ_DETAIL.acc_pcs_dt)<@EDate)) ORDER BY VPM300_PAT_VISIT.vst_ext_id ---------------------------------------------------------------------------- ---- CF ADMINISTRATOR SETTING FOR THE DATA SOURCE (Image may not show) ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
