Coldfusion MX7 Data adapter/driver calling SQL stored procedure using sp_prepare
We have a problem with one of our Coldfusion MX7 applications generating a database error when a user chooses the Add Button to add an event to the database. The error is as follows:
Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
ErrorCode HY000
Message Error Executing Database Query.
We have this same application with the exact same code deployed on four web servers where it works just fine and does not generate an error. Tracing determined that the problem server calls the SQL database with an sp_prepare call for the stored procedure. Our DBA feels that this is what is causing the problem because the other servers where the applicaiton works consistently do not call the SQL server with sp_prepare.
I've compared the settings in our Coldfusion administrator with the other servers and they are the same. I've also verified that the Coldfusion drivers match on all the servers.
The developer does not want to change their code because the application works consistently on four servers. The developer feels that the problem is something to do with the server environment.
Does anyone have any suggestions on how to resolve this issue?
Detail [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrec
ErrorCode HY000
Message Error Executing Database Query.
We have this same application with the exact same code deployed on four web servers where it works just fine and does not generate an error. Tracing determined that the problem server calls the SQL database with an sp_prepare call for the stored procedure. Our DBA feels that this is what is causing the problem because the other servers where the applicaiton works consistently do not call the SQL server with sp_prepare.
I've compared the settings in our Coldfusion administrator with the other servers and they are the same. I've also verified that the Coldfusion drivers match on all the servers.
The developer does not want to change their code because the application works consistently on four servers. The developer feels that the problem is something to do with the server environment.
Does anyone have any suggestions on how to resolve this issue?
Code we see the code in question?
Your DBA should be able to capture the SQL in question and the error will be there in black and white. As already mentioned this is more likley to be because of the parameters passed, or because of dodgy dynamic SQL.
ASKER
Thanks for your comments. I've given the comments to the developer who wrote the application and I'm waiting to hear back from him to provide further details. The developer and the dba both feel it is related to the sp_prepare call because that is the only difference between the server where the application doesn't work and the other four servers where the application works fine.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The developer ended up changing his code a bit and the application is working now. The thing that was confusing us was that the same code works fine on all the other servers that are set up exactly the same. It probably is a syntax issue but it seems strange that the code doesn't generate an error on the other 4 web servers.
ASKER
I was hoping to be able to find out how to troubleshoot what was different about this MX 7 server that caused the error. The same code works fine on 4 other servers and the application doesn't use sp_prepare when it calls the database. On this server it uses sp_prepare and we wanted to find out why.
1. what does this procedure do (ie the sql) and how is it called in your CF code? ....
2. Your DBA should be able to capture the SQL in question and the error will be there in black and white
>>> I was hoping to be able to find out how to troubleshoot what was different about this MX 7
>>> server that caused the error.
Without the seeing sql or capturing the trace information, there's not much else we can suggest with so little information.
2. Your DBA should be able to capture the SQL in question and the error will be there in black and white
>>> I was hoping to be able to find out how to troubleshoot what was different about this MX 7
>>> server that caused the error.
Without the seeing sql or capturing the trace information, there's not much else we can suggest with so little information.
Not to discount your dba's comments. But what leads you/them to the conclusion the problem is something other than what the error message says: ie syntax error. Depending on what your proc does, it's entirely possible it might work with one set of parameters and fail with another. So did you check the obvious (ie likely) causes first? Also, exactly does this procedure do (ie the sql) and how is it called in your CF code?
> Tracing determined that the problem server calls the SQL database with an sp_prepare call
> for the stored procedure. Our DBA feels that this is what is causing the problem
As I understand it, seeing sp_prepare is not necessarily unexpected. So .. exactly what about this
call does the dba think is causing a problem? ie Why did they narrow in on this as the problem, other than the fact that it's present in one case and not in the others.