It's unlike to be this if you are able to execute that SP using ASP. I'd suggest that you move your procresult tag below your proc params - not sure this will make a blind bit of difference but worth a try. Also ensure that your parameters are in the same order in the SP as they are in the call to it in CF. Also ensure that the datatypes are correct and the values being passed in are of those datatypes. Finally set up a DSN and try using ODBC to see if it's the provider.
-----Original Message----- From: Voris, Jim [mailto:[EMAIL PROTECTED]] Sent: 10 April 2002 15:57 To: CF-Talk Subject: RE: Still Need Help with Stored Procedure. Your problem may be on the database side, and not with your CF code. 1) Is the database owner, dbo, and not the user that created it? 2) Have you granted execute permission on on the stored proc to the SQL id you use on your DSN? Jim -----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 10:48 AM 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) ______________________________________________________________________ Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.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
