Stored Procedure Does Not Work

[Robert10]

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>

[paross1]

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

[Robert10]

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.

[paross1]

Wouldn't this work better?

<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>

<cfoutput>
#Parent#
</cfoutput>

Phil

[Robert10]

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.

[MikerRoo]

You need to attach the FULL, EXACT error message.
It would be good to attach the SP source too.

Without seeing the omitted details, the most common causes are that (1) order
was scrambled as Paross said, (2) Your CF datasource does not have all the
permission(s) it needs for the SP, (3) Input variable mis-set or SP output
variable mis-set.

[Robert10]

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

[MikerRoo]

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?



ALTER PROCEDURE SpotsyFindLastInherit
@CurrentFolderId integer,
@ParentID integer output
AS
BEGIN
SET NOCOUNT ON
DECLARE
@parent integer,
@inheritpermissions integer

SET @parent = 0 -- Initialize!
SET @inheritpermissions = 0
SET @CurrentFolderID = 380 -- Suspicious!


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 OR @Inheritpermissions IS NULL)
BEGIN
SET @parent = @CurrentFolderID
SET @Inheritpermissions = 0
END
ELSE
SET @CurrentFolderId = @parent
END

SET @ParentID = @parent
IF @ParentID IS NULL
SET @ParentID = 0
END

GO

[Robert10]

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.

[MikerRoo]

The addition null checking is needed -- depending on your DB and connection
options -- even if the column does not allow nulls.

Anyway, something is missing from this picture but a CF restart never hurts.

Also clear the cache in CF administrator.

[Robert10]

The reboot did the trick, I also now see the generated code when in debugging mode.

Not sure what the initial problem was, but it seems to be fixed now.

Thanks for all the help.