stored procedure access

i'm trying to call a stored procedure and i get the following error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Could not find stored procedure 'uspGetChargeBackData'.

I know the stored procedure exists, i watched a guy run it on the database. i can call some other stored procedures just fine. here is my code to call it:

<cfstoredproc
procedure="uspGetChargeBackData"
dataSource = "#dsn#"
username = "#dbid#"
password = "#dbpw#">

<cfprocresult name="ChargeBackData">
</cfstoredproc>
<cfoutput>
#ChargeBackData#
</cfoutput>

any ideas about this?

Are you sure that the account you're using for the user name and password has rights to execute the procedure? Also, can you run it from the SQL query analyzer when logged in as that user?

we created another stored procedure with the same name except Test on the end and made the user name i am using to connect to the db as the owner and it still can't find it.

we logged in my username and password and executed the procedure.

You're using the exact same user name and password in the <cfstoredproc> tag? Can you run any other stored procs from CF? Does the DSN have user name and password info that is overriding the values you're entering as attributes to the <cfstoredproc> tag? Have you enabled execution of stored procedures for this DSN in the CF administrator?

i have run other stored procs with the same username and password settings. i just hard coded them in and got the same "Could not find stored procedure 'uspGetChargeBackData'. " error.

this username has access to all the tables in the db too.

It still sounds like a permissions issue to me, especially if the same user name and password can run other stored procs on the server. Will it run if you use <cfquery> instead, and use the "exec" command to execute the procedure?

If that doesn't work I'm out of ideas unfortunately. Clearly something is not set correctly somewhere, but I've gone through all the places I can think of to check.

One thing I might mention is to double check your DSN and ensure it's pointing to the appropriate server and db.

We've had problems (too frequently, I might add) with DSNs between our local vs. development vs. production environments. One problem is having a local DSN pointing to a local DB, instead of pointing to the production DB for example.

Whenever I have problems that just don't seem to make any sense, 9 times out of 10 it's something really, really simple that I'm overlooking.

Just thought I'd toss that out there.

you guys were right, it was simple. the stored procedure i was calling was on a different database (i had no idea). just had to add a new datasource and it's fine now. thanks for all the suggestions. i have found this to be the best cold fusion message board on the internet. thanks to all who make it that!

I had the same error...

The solution I needed was fully specifying the stored proc name
like this;
cfstoredproc procedure="[USER.PROCNAME]" datasource= etc

For the longest time I didn't have the [ ] and tried a variety of variations that all gave the same error, with exception/debug details being no help.

Posted to help anyone else who is having the same problem.