Stored Procedure Does Not Work

Forum|Forum|19 years ago
June 5, 2006
3 replies
1112 views

I am trying to run the following code against a MSSQL 2k SP4 DB. When I run the procedure in query analyizer it works fine. However when I use the following code, CF throws back "Error Executing Database Query" when in debugging mode. Which of course tells me little to nothing. So my question is does anyone see anything wrong with this block of code?

<cfoutput>
<cfset Parent=0>
<cfstoredproc procedure="SpotsyFindLastInherit" datasource="CMS300DB">
<cfprocparam cfsqltype="cf_sql_integer" type="in" dbvarname="@CurrentFolderID" value="#GetContent.folder_id#">
<cfprocparam cfsqltype="cf_sql_integer" type="out" dbvarname="@ParentID" variable="Parent">
</cfstoredproc>
#Parent#
</cfoutput>

Edit:
This is on CF7 Enterprise

Database access

This topic has been closed for replies.

R

Robert10Author

Participating Frequently

The CF Error in debugging mode:

Exceptions

16:28:04.004 - Database Exception - in MySecretTemplate.cfm : line 33
Error Executing Database Query.

Data source permissions:
Select
Insert
Update
Delete
Stored Procedures

Sp Source is as follows:

CREATE PROCEDURE SpotsyFindLastInherit
@CurrentFolderId integer,
@ParentID integer output
AS
BEGIN
SET NOCOUNT ON
DECLARE @parent integer
Declare @inheritpermissions integer
SET @CurrentFolderID=380

Select @inheritpermissions=inherit_permissions, @parent=parent_id
FROm content_folder_tbl
where folder_id=@CurrentFolderID

WHILE @InheritPermissions <> 0
BEGIN
SELECT @parent = parent_id, @InheritPermissions=inherit_permissions
FROM content_folder_tbl
WHERE folder_id=@CurrentFolderId
if @Inheritpermissions = 0
SET @parent = @CurrentFolderID
else
SET @CurrentFolderId = @parent
END
SET @ParentID = @parent
END

GO

A

Anonymous

Replace the Stored procedure with the attached source.

Also, that is not the full error message -- which includes generated SQL, etc.
Do you have all error reporting options and robust exception handling turned on in the CF administrator?

The permissions you need to check are in the SQL Enterprise manager...
Does the SQL user that you are using for CF have datareader and datawriter access to all tables affected by the SP.
The SQL user must also have execute permission on SpotsyFindLastInherit.

Finally, is the DB owner anything other than "dbo" (standard) or is their more than one DB with SpotsyFindLastInherit or content_folder_tbl?

R

Robert10Author

Participating Frequently

I have all of the debugging options enabled. And that is the only thing it is giving me, besides the stuff that ran previous to it.

Which I find odd as well that it did not give me the SQL code, just a generic error message.

All permissions in the DB are correct, and there is only one table and one procedure with those names, and the owner is dbo.

I did noticed that one line where I manually set the FolderID to 380... that was something I forgot to remove from testing. I removed it, and still had the same result.

I tried your code as well with the same result. The additional checking for null is not needed as null is not allowed, but good idea to throw it in.

I am going to try restarting the CF services tonight and see if that fixes it (perhaps there is some kind of CF memory problem?!), I've seen it fix wierd DB problems before, I just figured I was doing something wrong with my CF code.

R

Robert10Author

Participating Frequently

From a best practices view point yes.

I tried that anyway on a "oh what the hell" guess... no dice. Same problem.

Now I did notice that it complains about this line:
<cfprocparam cfsqltype="cf_sql_integer" type="out" variable="Parent">

or at least that is the line number being referenced in the debugging output.

P

paross1

Participating Frequently

Are your CFPROCPARAMs in the same order, datatype, and number as the parameters in your stored procedure? The dbvarname attribute is deprecated and does absolutely nothing, so you can't use it to "match up" your parameters.

Phil

R

Robert10Author

Participating Frequently

Yep, the params are listed in the same order they are in the procedure code. Both params are type integer, and there is one input param, and one output param.

It has me baffled.

P

paross1

Participating Frequently

Wouldn't this work better (probably shouldn't really matter)?

<cfset Parent=0>
<cfstoredproc procedure="SpotsyFindLastInherit" datasource="CMS300DB">
<cfprocparam cfsqltype="cf_sql_integer" type="in" value="#GetContent.folder_id#">
<cfprocparam cfsqltype="cf_sql_integer" type="out" variable="Parent">
</cfstoredproc>

<cfoutput>#Parent#</cfoutput>

Phil

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded