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]Incorrect syntax near '='.  
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?
COV-WebmasterCity Of VancouverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Since there were no other responses so far ... let me throw in my $0.02.  

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.

0
duncancummingCommented:
Code we see the code in question?
0
nmcdermaidCommented:
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.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

COV-WebmasterCity Of VancouverAuthor Commented:
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.
0
_agx_Commented:
It's really more likely the problem is just a basic syntax error.  Here's an example that generates the same error when you pass in an empty string instead of an id value. It should do the same on MX7.  (It's a truly dreadful stored procedure,  but that's obviously intentional ... to bring out the errors ;-)



CF 
===========
<cfstoredproc procedure="test" datasource="MSSQL_2005">
   <cfprocparam type="in" value="" cfsqltype="cf_sql_varchar">
</cfstoredproc>

SQL 
===========
CREATE PROCEDURE Test
@id varchar(50)
AS
BEGIN
	DECLARE @sql varchar(max)
	SET @sql = 'SELECT EmployeeID, FirstName, LastName
				FROM	Employee
				WHERE   EmployeeID = '+ @id
	EXEC( @sql )
END
GO

ERROR
===========
Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near '='.  
SQLSTATE   HY000 
SQL   {call test( (param 1) )} 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
COV-WebmasterCity Of VancouverAuthor Commented:
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.
0
COV-WebmasterCity Of VancouverAuthor Commented:
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.
0
_agx_Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.