Inconsistent stored procedure syntax error

We have a stored procedure that is being called from a vb6 application. The exact line of code is below.

mats_Reports_CSR_OB_Hourly_Percent('11/1/2006 4:51:27 PM','12/1/2006 4:51:27 PM')

when executing this in query analyzer it will always fail due to the parenthesis. However when this same line of code is passed to sql server in the vb6 application it usually works. There are times it fails with a syntax error message similar to:

Incorrect syntax near '{

I fixed the problem last time by removing the parenthesis in the vb6 code. However I can now put the parenthesis back into the code and it works again now.

Prior to my recent intervention the code was not being changed as it will work for days and then randomly stop working. Then without any change start working by itself again.

What is causing this behavior? How do I make it always work?

Calling a sql stored procedure is not like calling a vb function. Since you're calling a stored procedure, consider using Parameters collection to pass into desired inputs.

http://windowssdk.msdn.microsoft.com/en-gb/library/ms675869.aspx|||

Thank you for your response.

Your response seems to imply there is a bug in the way ODBC handles stored procedure calls?

This application was built over 3 years ago. It is not a realistic solution to switch connections from ODBC to ADO, as it would require the entire application to be recoded.

I have spent the better part of the day yesterday doing research on this problem and it seems it has something to do with ODBC escape sequences. What conditions would change how sql server parses a statement? There has to be something the server is automatically deciding on using that changes, because the same code works and then doesn't work (running against the same data).

|||No. What I hinted at is that you shouldn't call a proc like so:

myproc(para1,para2)

Instead you should do:

cmd.CommandText="myproc para1, para2"

or create a parameters collection for your parameters.|||I already know not using the parenthesis will work. I was looking for why it's happening.|||There is a simple answer: Because its the syntax of calling stored procedures that way. Did you ever ask yourself why a vb function is called like SomeFunction('Something') and not like SomeFunction*'Something'* :-) Its simply the syntax that is specified for calling the procedure.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

OK, your missing what I am getting at. I will make an analogy:

Say your using your keyboard, everytime you press the the 'k' key you'd expect the letter k every time you pressed it.

Now if sometimes when you pressed the 'k' key you got the letter q you'd pull the keyboard and replace it with one that works, or you'd send the keyboard in for repair.

Now what I am describing is the same way. If you call a function using the syntax I already described above you'd expect it to work every time you used it. What I am getting though is sometimes the same syntax is sucessfully running, other times it is giving a syntax error. I am asking why is this occuring? What can you think of that would change to cause this behavior?

|||Where is the error thrown ? Could it be that this is some SQL Server error (not actually in the frontend) which is caused by some malformed composed dynamic SQL in the backend, producing error messages like this ? Are you able to start profiler on the server to see what happends behind the scenes and what comamdns are fired against the database.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de