Using Client Variables When Calling Stored Procedure

[Guest]

I have a ColdFusion function that calls two stored procedures.  The first stored procedure ends up inserting data into a remote database and the second stored procedure calls a remote procedure on a remote database.

Each are called using a block of code identical to the code below:

variables.storedProcService = new storedProc();
variables.storedProcService.clear();
variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE");
variables.storedProcService.execute();

When I run this function, the calls fail.  The error I receive is [Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224. This isn't very helpful, so I had an associate of mine test the function with his ColdFusion environment and it worked just fine.  Comparing the two environments, I noticed that he was using the Windows Registry to store client variables, while I was using a database.  So I switched to the Windows Registry and the two calls worked just fine.  However, I don't want to use the Windows Registry because the current system I support uses a database.

Why would this work with the Windows Registry and not the database?  I was under the impression that the same information was stored regardless of the storage mechanism.

[Guest]

UPDATE: I can also run the stored procedures just fine without the use of the application.  I ran the stored procedures on a simple CFML page.

[Guest]

Has no other person run into a similar issue before?

[Adam Cameron.]

Do you get the same error if you just use <cfstoredproc>?  Is the proc hitting the same DB / DSN as the client variables are stored in (/accessed via ~)?  Maybe set up a specific DSN for the client store, and see if that sorts it out?

It's certainly a weird one.

As for the "[Macromedia][SQLServer JDBC Driver]The DBMS returned an unspecified error. The command code was 224" being not very helpful: CF can only report back what the DB says.  If the DB doesn't return anything helpful then one cannot blame CF.

Did you google up what a SQL Server error 224 is?  that might give a clue.

Do you actually use client variables?  Most people don't, I find.  So you could always just set clietn storage to "none".

--

Adam

[Guest]

Yep, I get the same result from <cfstoredproc> and the proc is hitting the same database/dsn.  And searching for the specific error code did not seem to help, it gave me another error message that did not seem to be "correct".

Object ID %ld specified as a rule for table ID %ld, column ID %d is missing or not of type default.

What do you mean by setting up a specific DSN for the client store.  Right now the client store is in a specific database and that database has its own DSN.  Is that what you mean?

[BKBK]

Two points. Firstly, since the issue shows up when you move from registry to database, it might have to do with authentication. What happens when you do something like this?

variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE", username="db_user", password="db_pword");

Secondly, I see no need for variables.storedProcService.clear();

[Guest]

When I try this:

variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE", username="db_user", password="db_pword");

The two calls do work just fine, but I was hoping to not include the credentials in my source code.  If including the credentials with the call works, what does that mean to you?  Authentication still?  I am using the same credentials that are included as a part of the datasources in Jrun (and those same credentials work just fine calling the procedures from within SQL Server Management Studio).

And I am only calling this:

Secondly, I see no need for variables.storedProcService.clear();

To be sure I am not using a different set up from a different call using the same stored procedure service (I use this stored procedure service in several functions).

[BKBK]

HiTopp wrote:

When I try this:

variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE", username="db_user", password="db_pword");

The two calls do work just fine, but I was hoping to not include the credentials in my source code.  If including the credentials with the call works, what does that mean to you?  Authentication still?  I am using the same credentials that are included as a part of the datasources in Jrun (and those same credentials work just fine calling the procedures from within SQL Server Management Studio).

I wont pretend to know what's going on here. I arrived at username and password simply by elimination.

Since the calls work when you include the credentials, and fail when you don't, I would say that that is the source of the problem. Is there perhaps a mismatch between the username/password pair for the datasource that you use for client variables and for the procedure's datasource? What about going to the administrator and making sure they are the same?

[Guest]

Shouldn't ColdFusion use the credentials provided when the datasource was set up (in Jrun)?  I don't actually assign credentials within the code, if that's what you are referring to.

[Guest]

The thing I don't get is this:  When I include the credentials with the procedure call within ColdFusion (the calls work fine), I include the credentials for the database server on which the stored procedures reside.  The credentials on the remote server are not the same as these credentials, but the calls to those procedures on the remote server work as well.  Does that confuse you even more?

[BKBK]

HiTopp wrote:

Shouldn't ColdFusion use the credentials provided when the datasource was set up (in Jrun)?  I don't actually assign credentials within the code, if that's what you are referring to.

I didn't suggest you assigned credentials by code. I am talking about the credentials you provided when you initially created the datasource. That's indeed what ColdFusion should use.

However, each datasource you created in the administrator was assigned its own credentials. My point is that these could differ from one datasource to the next. Apparently, your procedure code internally invokes a process that involves client variables.

[Guest]

You are correct, the credentials are different on the local server and remote server.  However, do you know if ColdFusion uses the credentials for the local server to make the stored procedure call and that stored procedure makes the remote procedure call (which requires different credentials), does ColdFusion use the credentials it previously used (from the local server datasource)?  That may be the issue if ColdFusion is attempting to use the local credentials on the remote server.

[BKBK]

To me, what needs investigating is where or why the procedure call would require client variables. The rest is straightforward. One thing I know for sure is that the accessing client variables will require the permissions of the local client datasource.

[Guest]

BKBK wrote:

To me, what needs investigating is where or why the procedure call would require client variables.

What do you mean by this?  Why the call works when credentials are included?

BKBK wrote:

One thing I know for sure is that the accessing client variables will require the permissions of the local client datasource.

And by this, do you mean that ColdFusion needs an account for the local database (where the datasource points)?

[BKBK]

HiTopp wrote:

BKBK wrote:

To me, what needs investigating is where or why the procedure call would require client variables.

What do you mean by this?  Why the call works when credentials are included?

Yes, and why it fails when they are not. The change in behaviour between registry(no problems) and database for client storage(problems) suggests that the procedure requires/uses client variables somewhere.

BKBK wrote:

One thing I know for sure is that the accessing client variables will require the permissions of for the local client datasource.

And by this, do you mean that ColdFusion needs an account for the local database (where the datasource points)?

I was only confirming a point. I think this question is irrelevant to your case. I am assuming that you have set up the client datasource locally in the ColdFusion adminstrator.

[Guest]

HiTopp wrote:

When I try this:

variables.storedProcService.setAttributes(datasource="DATASOURCE", procedure="PROCEDURE", username="db_user", password="db_pword");

The two calls do work just fine, but I was hoping to not include the credentials in my source code.  If including the credentials with the call works, what does that mean to you?  Authentication still?  I am using the same credentials that are included as a part of the datasources in Jrun (and those same credentials work just fine calling the procedures from within SQL Server Management Studio).

Correction, I can include only the username or only the password and the calls will still work.  Would ColdFusion not be using the correct credentials for some reason?

[Guest]

I've done some more testing with this issue and found a new "clue".  I attempted to call the two stored procedures from a basic CFML page.  The calls worked just fine and using SQL Server Profiler I see no difference between this successful call and the other successful call (including the username or password).  Does that make you think that the issue lies somewhere within the application I am using to make the calls?

[BKBK]

Curious. What happens when you use username or password, but then with incorrect values?

[Guest]

I get a "Login Failed for user _____" error if I attempt to use incorrect credentials.

[Guest]

Okay... Not sure exactly why, but when you mentioned changing the credentials to invalid credentials I thought about simply erasing the credentials.  In other words, include the username/password attributes with no data.  And the calls worked!?!?  Not sure whether to be excited or perplexed.

[BKBK]

Oh, and it helps to know your ColdFusion version.

[BKBK]

HiTopp wrote:

I get a "Login Failed for user _____" error if I attempt to use incorrect credentials.

That message is very likely coming from the database. To be sure, turn debugging on in the ColdFusion Administrator. Also check out the logs. We have to narrow this down, otherwise we could end up running circles.

If the attribute pairs [username="valid_name", password="valid_password"] and [username="", password=""] work, but [username="valid_name", password="invalid_password"] fails, then the cause of the problem is most probably your code, and how it connects to the database(s). Could you show us all (or at least more) of the code?

My first suggestion to you was to omit the line variables.storedProcService.clear();. Invoking clear() only makes sense when it occurs after addProcResults() or addParam(). Did you omit the line? What was the effect, with and without credentials?

Could you please describe your infrastructure a bit more. From what I understand, there are at least 2 servers, S1 and S2. Let's say, ColdFusion is installed on S1. I am assuming from what has gone before that SQL Server, which runs the stored procedure, is on the second server S2.

Go to the 'Client Variables' page in the ColdFusion Administrator. Verify that ColdFusion is indeed configured to use a database for client storage. What is the name of the datasource used as client store? Now, proceed to the datasource page and check out the settings for the client datasource. To which machine/database server does this datasource point?

Jot down the server settings and credentials of the other datasources configured in the Administrator. To which machine/database server do these datasources point?