[HiTopp]

I currently have a stored procedure that exists on a SQL Server. When that stored procedure is called, it ends up calling a remote stored procedure on a different SQL Server using a linked server. When I execute this stored procedure in ColdFusion like so:

<cfstoredproc datasource="DATASOURCE" procedure="PROCEDURE">
  <cfprocparam cfsqltype="cf_sql_integer" value="VALUE" />
</cfstoredproc>

I receive the error The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVER" was unable to begin a distributed transaction.

However, if I attempt the same call from a basic CFML page, I get no error and the call works just fine.

And to throw another twist into the issue, if I execute the stored procedure above within my application (like normal), but include either the username or password for the local SQL Server (not the remote SQL Server), the call works fine.

Does this lead anybody to believe that the issue is somewhere in the bowels of my application?

[Craig328]

Hey HiTopp, welcome to DIC!

I understand that you're getting an error but given that you have two other means for executing the stored proc with CF calling it, I'm not sure I'm understanding the problem.

If the CFSTOREDPROCEDURE call works when you add in either the username or password...then add in the username or password. When you say "if I attempt the same call from a basic CFML page, I get no error and the call works just fine" how do you mean and do you have the option to do that instead of using CFSTOREDPROC?

Or are you asking out of curiosity why something that otherwise works well via CF doesn't work in one instance? If it's not out of curiosity but you're needing to make this work (without adding the username or password) then we'll probably need to know all kinds of additional info about your setup. What version of CF are you using, what database and versions are involved, what OS is the CF instance running on, the connectors you're using to connect the web server to the database...stuff like that.

CFSTOREDPROC is sensitive like that sometimes. It usually works for me but for the times when it doesn't, I've been known to use a regular CFQUERY with a SQL execute command.

[HiTopp]

Craig328, on 29 March 2012 - 09:10 AM, said:

If it's not out of curiosity but you're needing to make this work (without adding the username or password) then we'll probably need to know all kinds of additional info about your setup. What version of CF are you using, what database and versions are involved, what OS is the CF instance running on, the connectors you're using to connect the web server to the database...stuff like that.

CFSTOREDPROC is sensitive like that sometimes. It usually works for me but for the times when it doesn't, I've been known to use a regular CFQUERY with a SQL execute command.

Yes, I would like to make it work without include the credentials in the call. I don't see why I should have to include the credentials since I have datasources set up in Jrun that include the credentials.

Here is the basic information of my ColdFusion/Web Server Environment:

ColdFusion Version: 9.0.1.274733
Server OS: Windows Server 2008
Database Server: MSSQL Server 2005 and 2008 (depends on server)
ColdFusion Datasources Database Driver: Microsoft SQL Server (in Jrun), JNDI (in ColdFusion Admin)

Craig328, on 29 March 2012 - 09:10 AM, said:

CFSTOREDPROC is sensitive like that sometimes. It usually works for me but for the times when it doesn't, I've been known to use a regular CFQUERY with a SQL execute command.

By the way, I have tried this as well with no luck. The same error is returned.