Skip to main content
A
Anonymous
March 21, 2012
Question

Using Client Variables When Calling Stored Procedure

  • March 21, 2012
  • 2 replies
  • 5353 views

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.

This topic has been closed for replies.

2 replies

A
Adam Cameron.
Inspiring
March 23, 2012

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

A
Anonymous
March 23, 2012

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
Community Expert
BKBKCommunity Expert
Community Expert
March 25, 2012

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();

A
Anonymous
March 21, 2012

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.

A
Anonymous
March 22, 2012

Has no other person run into a similar issue before?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices