Stored procedure error using sql server

I'm calling a stored procedure using Sql and can not get it to work using an the GUI's html form. The stored procedure has a start date and end date variable to call. I get this error:
(FOC1400) SQLCODE IS 8114 (HEX: 00001FB2) XOPEN: 22018
: Microsoft OLE DB Provider for SQL Server: [22018] Error converting data
: type varchar to datetime.
L (FOC1405) SQL PREPARE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: DUMP
BYPASSING TO END OF COMMAND

Code:

 <!-- Generated by Report Layout Painter -->
<HTML>
<HEAD>
<TITLE>HtmlPage</TITLE>
<BASE href=HTTP://ppnta369:8080>
<META content="MSHTML 6.00.2900.3243" name=GENERATOR>
<SCRIPT id=clientEventHandlersJS type=text/javascript>
//Begin function window_onload
function window_onload() {
UpdateData();
// TODO: Add your event handler code here
}
//End function window_onload
</SCRIPT>

<SCRIPT for=window eventname="onload">window.onload = function() { window_onload(); }</SCRIPT>
</HEAD>
<BODY style="OVERFLOW: auto">
<IFRAME id=report1 style="Z-INDEX: 1; LEFT: 20px; WIDTH: 680px; POSITION: absolute; TOP: 230px; HEIGHT: 330px" tabIndex=1 name=report1 executebuttonId="form2Submit" requests_list="1" autoExecute="False" elementtype="2"></IFRAME>
<FORM id=form2 style="Z-INDEX: 2; LEFT: 20px; WIDTH: 250px; POSITION: absolute; TOP: 100px; HEIGHT: 105px" tabIndex=2 name=form2 onsubmit="OnExecute(this);return false;" method=post requests_list="1" fexlist_list="report1" default_slider_type="4" form_prompt_location="1" form_number_of_visible_rows="4" form_number_of_columns="4" vert_dist_between_controls="10" form_hor_dist_between_controls="10" form_dist_between_desc_and_input="10">
<INPUT id=form2Submit style="Z-INDEX: 3; LEFT: 5px; BACKGROUND-IMAGE: url(/ibi_html/javaassist/ibi/html/describe/run16.gif); WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; POSITION: absolute; TOP: 78px; HEIGHT: 22px; BACKGROUND-COLOR: lavender" tabIndex=3 type=submit value=" " name=ITEM1> 
<INPUT id=form2Reset style="Z-INDEX: 4; LEFT: 48px; BACKGROUND-IMAGE: url(/ibi_html/javaassist/ibi/html/describe/reset.gif); WIDTH: 38px; BACKGROUND-REPEAT: no-repeat; POSITION: absolute; TOP: 78px; HEIGHT: 22px; BACKGROUND-COLOR: lavender" tabIndex=4 type=reset value=" " name=reset1> 
<FIELDSET id=form2_formbodyid style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; Z-INDEX: 5; LEFT: 0px; PADDING-BOTTOM: 0px; OVERFLOW: auto; WIDTH: 235px; BORDER-TOP-STYLE: none; PADDING-TOP: 0px; BORDER-RIGHT-STYLE: none; BORDER-LEFT-STYLE: none; POSITION: absolute; TOP: 0px; HEIGHT: 73px; BORDER-BOTTOM-STYLE: none" tabIndex=5 formbody="1">
<SPAN id=pane1 style="Z-INDEX: 6; LEFT: 0px; WIDTH: 60px; POSITION: absolute; TOP: 0px; HEIGHT: 51px" tabIndex=6 elementtype="32" name="dte">dte 
<SELECT id=combobox1 style="Z-INDEX: 7; LEFT: 0px; WIDTH: 60px; POSITION: absolute; TOP: 29px; HEIGHT: 22px" tabIndex=7 size=1 name=dte ibiformat="MDYY" ibiapp_app="joetest/VFHTML" ibic_server="EDASERVE" datafield="NOM_DATE" displayfield datasource="TBLARCH.mas" datatype="1" sourcetype="typeMaster" operation accept="0" defaultlocation="29,0,51,60" addalloption="0" requiredfield="42440984" datafieldtype="INTIGER"></SELECT></SPAN> 
<SPAN id=pane2 style="Z-INDEX: 8; LEFT: 70px; WIDTH: 150px; POSITION: absolute; TOP: 0px; HEIGHT: 50px" tabIndex=8 elementtype="32" name="edte">edte 
<SELECT id=combobox2 style="Z-INDEX: 9; LEFT: 20px; WIDTH: 120px; POSITION: absolute; TOP: 28px; HEIGHT: 22px" tabIndex=9 size=1 name=edte ibiformat="MDYY" ibiapp_app="joetest/VFHTML" ibic_server="EDASERVE" datafield="NOM_DATE" displayfield datasource="TBLARCH.mas" datatype="1" sourcetype="typeMaster" operation accept="0" defaultlocation="29,0,51,60" addalloption="0" requiredfield="42440984" datafieldtype="INTIGER"></SELECT></SPAN></FIELDSET></FORM>
<xml id=ibi_requests>
<requests>
	
<request requestid="1" targetname="report1" ibif_ex="report1" targettype="0" sourcetype="typeAdhocfex" ibiapp_app="" paramremovedbyuser="dte&edte">
		
<![CDATA[-* File sqlstoredad.fex

SQL SQLMSS SET SERVER IVB
SET SQLENGINE = SQLMSS
SQL SQLMSS EX wf_adherence '&dte','&edte' ;

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS DUMP
END
TABLE FILE DUMP
PRINT
     FULLNAME
     NStart AS 'START'
     SSTOP AS 'STOP'
     SSCHEDULED AS 'SCHEDULED'
     SignedInPerc AS 'Signed In %'
     CompPerc AS 'Comp %'
BY TEAMNAME
HEADING
"<TEAMNAME"
ON TABLE NOTOTAL
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     UNITS=IN,
     PAGESIZE='Letter',
     SQUEEZE=ON,
     ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
     GRID=OFF,
     FONT='ARIAL',
     SIZE=9,
     RIGHTGAP=0.125000,
$
TYPE=DATA,
     COLUMN=N2,
     FOCEXEC=v1adhere_datetest(NAME=N2),
$
TYPE=TITLE,
     STYLE=BOLD,
$
TYPE=TITLE,
     COLUMN=N2,
     FOCEXEC=v1adhere_datetest(NAME=N2),
$
TYPE=TABHEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=TABFOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=HEADING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=FOOTING,
     SIZE=12,
     STYLE=BOLD,
$
TYPE=SUBHEAD,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBFOOT,
     SIZE=10,
     STYLE=BOLD,
$
TYPE=SUBTOTAL,
     BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
     SIZE=9,
$
TYPE=ACROSSTITLE,
     STYLE=BOLD,
$
TYPE=GRANDTOTAL,
     BACKCOLOR=RGB(210 210 210),
     STYLE=BOLD,
$
ENDSTYLE
END]]>
		
<variables>
			
<variable field="dte" file="sqlout.mas" desc="dte" datatype="0" operation="" default="" name="dte" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable>
<variable field="edte" file="sqlout.mas" desc="edte" datatype="0" operation="" default="" name="edte" textvarname="" accept="0" type="unresolved" select="0" min="" max="" controltype="7" create="1"></variable></variables></request></requests>
</xml></BODY></HTML>

Any help or examples would be appreciated.

Thanks,

Joe

WebFocus 7.7.02 WinXP

Posts: 236 | Registered: May 12, 2006

Tony A

Expert

posted

Hide Post

Joe,

The error is quite clear - MS SQL is having difficulty converting the data to datetime from varchar. WebFOCUS is not even receiving the resultset from MS SQL therefore SQLOUT is empty and the hold file DUMP doesn't even get created, therefore the final error message which says that DUMP cannot be found.

Look to your stored proc, the problem is there.

T