What I
do with SP's is use a try catch statement and an error
template.
I
create a string that contains the SP Call and display it along with the full
error in the error template.
If
there is an error I copy the SP call to Query Analyser and run it there.
QA gives you a lot of detail about what failed.
If I
have still get no Idea I convert the query in the SP to a string and
display the string. In QA I re run the SP and look at the
displayed query in the messages section. then I copy the string into
the Command part of the QA and try to run that. Failing that I reformat the
computer and start again :)
Sproc
will look like the following
CREATE
PROCEDURE ProcName
--
Specify Parameters
@P_Var1 CHAR(10) = NULL,
@P_Var1 CHAR(10) = NULL,
@P_Var2 CHAR(10) = NULL,
@P_Var3 CHAR(10) = NULL
AS
DECLARE @VAR_SQL_String VARCHAR(8000)
SET
@VAR_SQL_String = '
SELECT *
SELECT *
FROM TableName
WHERE Field1 = ' + @P_Var1 + '
AND Field2 = ' + @P_Var2 + '
WHERE Field1 = ' + @P_Var1 + '
AND Field2 = ' + @P_Var2 + '
AND Field3 = ' + @P_Var3 + '
PRINT
@VAR_SQL_String
EXEC(@VAR_SQL_String)
EXEC(@VAR_SQL_String)
GO
Sproc
Template looks like this
<cfset SQLString = "EXEC ProcName<br>@P_Var1=
'#Var1#',<br>@P_Var1= '#Var2#',<br>@P_Var1=
'#Var2#'">
<cftry>
<cfstoredproc
procedure="ProcName"
datasource="#datasource#">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@P_Var1"
value="#Var1#"
null="No">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@P_Var2"
value="#IVar2#"
null="No">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@P_Var3"
<cfstoredproc
procedure="ProcName"
datasource="#datasource#">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@P_Var1"
value="#Var1#"
null="No">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@P_Var2"
value="#IVar2#"
null="No">
<cfprocparam type="In"
cfsqltype="CF_SQL_CHAR"
dbvarname="@P_Var3"
value="#Var3#"
null="No">
<cfprocresult name="MyQuery">
</cfstoredproc>
<cfcatch type="Database">
<cfinclude template="qryerr.cfm">
</cfcatch>
</cftry>
null="No">
<cfprocresult name="MyQuery">
</cfstoredproc>
<cfcatch type="Database">
<cfinclude template="qryerr.cfm">
</cfcatch>
</cftry>
Error
template looks like this
<cfoutput>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Untitled</title>
</head>
<head>
<title>Untitled</title>
</head>
<body>
<div align="center">
<font Face="arial">
<hr width="40%">
<h3>Database Error</h3>
<hr width="40%">
</font>
</div>
<font Face="arial">
<strong>Query/SP:</strong><br>
#SQLString#<p>
<strong>SQL State:</strong> #CFCATCH.SQLSTATE#<br>
<strong>Native Error Code:</strong> #CFCATCH.NATIVEERRORCODE#<br>
<strong>TYPE:</strong> #CFCATCH.TYPE#<br>
<strong>MESSAGE:</strong> #CFCATCH.MESSAGE#<p>
<strong>TAG Context</strong>:
<ul>
<cfloop index="contextLoop" from="1" to="#arraylen(cfcatch.tagcontext)#">
<cfset Curr_Tag_Stack = CFCATCH.TAGCONTEXT[contextLoop]>
<li><strong>#Curr_Tag_Stack["ID"]#</strong>, #Curr_Tag_Stack["LINE"]#, #Curr_Tag_Stack["COLUMN"]#, #Curr_Tag_Stack["Template"]#
</li>
</cfloop>
</ul>
<form>
<strong>DETAILS</strong>:<Br>
<TextAREA name="Detail"cols="50" rows="6">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA>
</form>
</font>
</body>
</html>
<cfabort>
</cfoutput>
<div align="center">
<font Face="arial">
<hr width="40%">
<h3>Database Error</h3>
<hr width="40%">
</font>
</div>
<font Face="arial">
<strong>Query/SP:</strong><br>
#SQLString#<p>
<strong>SQL State:</strong> #CFCATCH.SQLSTATE#<br>
<strong>Native Error Code:</strong> #CFCATCH.NATIVEERRORCODE#<br>
<strong>TYPE:</strong> #CFCATCH.TYPE#<br>
<strong>MESSAGE:</strong> #CFCATCH.MESSAGE#<p>
<strong>TAG Context</strong>:
<ul>
<cfloop index="contextLoop" from="1" to="#arraylen(cfcatch.tagcontext)#">
<cfset Curr_Tag_Stack = CFCATCH.TAGCONTEXT[contextLoop]>
<li><strong>#Curr_Tag_Stack["ID"]#</strong>, #Curr_Tag_Stack["LINE"]#, #Curr_Tag_Stack["COLUMN"]#, #Curr_Tag_Stack["Template"]#
</li>
</cfloop>
</ul>
<form>
<strong>DETAILS</strong>:<Br>
<TextAREA name="Detail"cols="50" rows="6">#HTMLEDITFORMAT(CFCATCH.DETAIL)#</TEXTAREA>
</form>
</font>
</body>
</html>
<cfabort>
</cfoutput>
--------Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Taco Fleur
Sent: Monday, 29 December 2003 10:41 AM
To: CFAussie Mailing List
Subject: [cfaussie] SP DebuggingAnybody there yet ;-))
What other ways are there to debug Stored Procedures in CF?
I have used
- SP Debugging in Query analyzer, i.e. did a cfdump of what gets passed from cf to the SP, no problems.
- Matched every parameter passed manually to the parameters in the SP (datatype etc.), could not find anything.
- double checked every cfprocparam of datatype INT (see error message below)I have been trying to debug this SP for a week now..
I keep getting
[Macromedia][SQLServer JDBC Driver][SQLServer]Error converting data type varchar to int.The error is clear, but it does not show me which value it is that it is having problems with.
What else can I do?Taco Fleur
---
07 3535 5072
Tell me and I will forget
Show me and I will remember
Teach me and I will learn
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
