stored procedure

zma · Feb 23, 2007

Having problems with my 1st stored procedure.
We have ColdFusion MX6.1 and SQL server 2005.
The server administrator says my login has all permissions.

---------getting this error

[Macromedia][SQLServer JDBC Driver][SQLServer]EXECUTE permission denied on object 'try1', database 'MED_ESTEPS', schema 'dbo'.

The error occurred in C:\Inetpub\

http://wwwroot\html\next\books.cfm:

line 25

23 : <cfprocparam cfsqltype="cf_sql_char" value="#codestate#">
24 : <cfprocparam cfsqltype="cf_sql_char" value="#namestate#">
25 : <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="stateid">
26 : </cfstoredproc>
27 : <cfoutput>

-------this is the cfm called books.cfm,dso is set in[Application.cfm and works for other queries

Code:

<html>
   <head>
      <title>
      </title>
   </head>
   <body>
      <cfset codestate='CA'>
      <cfset namestate='California'>
      <cfif isdefined('codestate')>
         <cfstoredproc procedure="dbo.try1" datasource="#dso#">
            <cfprocparam cfsqltype="cf_sql_char" value="#codestate#">
            <cfprocparam cfsqltype="cf_sql_char" value="#namestate#">
            <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="stateid">
         </cfstoredproc>
         <cfoutput>
            new*#stateid#*
         </cfoutput>
      </cfif>
   </body>
</html>

--------this is the stored procedure try1 in try1.sql

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbo.try1
(
   @arg_codestate CHAR(2),
   @arg_namestate CHAR(10),
   @arg_bookid INT OUT
)
AS
BEGIN
    SET NOCOUNT ON
    INSERT INTO codestates (codestate,namestate)
           VALUES (@arg_codestate,@arg_namestate);
    SELECT @arg_bookid=@@IDENTITY;
END;
GO

ecobb · Feb 23, 2007

Drop the .dbo, see if that helps:

Code:

<cfstoredproc procedure="try1" datasource="#dso#">

Since your login and permissions are already set up in your datasource connection, you don't need it in the cfstoredproc tag.

Hope This Helps!

ECAR
ECAR Technologies

http://www.ecartech.com

"My work is a game, a very serious game." - M.C. Escher

FALCONSEYE · Mar 2, 2007

try this right on the SQL server 2005 and see if you get any error:

@arg_codestate CHAR(2),
@arg_namestate CHAR(10),
@arg_bookid INT OUT

declare @myNewID int
exec try1 @arg_codestate = 'CA', @arg_nameState = 'California', @arg_bookID = @myNewID
select @myNewID

if you have all the rights to execute a stored proc, you should run this without a problem. let us know what happens...

http://www.jetansolutions.com

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

stored procedure

zma

Programmer

ecobb

Programmer

FALCONSEYE

Programmer

Similar threads

Part and Inventory Search

Sponsor