Avatar of Aaron Jabamani
Aaron Jabamani
Flag for United Kingdom of Great Britain and Northern Ireland asked on

SQLExecDirectA fails sometimes.

Hi All,
        I'm using SQLExecDirectA to to execute a stored procedure. It works many times but i do often get below error.
@ .\src\db\odbc\Connection.cpp:343   Detail: -1 { Record# 1 Error# 3922 State [42000] - Syntax error or access violation  ODBC Diagnostics: Row# 1 Line# 1 Message State [1] Message Severity [16] Procedure [sp_bindsession] Server [HOPRIFDBSHSQL2Q]  || Record# 2 Error# 3922 State [42000] - Syntax error or access violation  } )

Please let me know why it is failing only some times. From the web, state 42000 mean, Syntax error or access violation...really confused since the same code work fine many times

Now i will explain the the call is made from our project.
We are having a legacy code(VC++, one of our business component) which talks to the database to fetch/store data.  We have our web application(asp.net 1.1 & C#) . The legacy code is hosted in our web server(IIS).

Please let me know if u need more details....and please let me know any kind of information which will help in resolving this.

Thanks
-Aaron
Visual C++.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
DanRollins

8/22/2022 - Mon
DanRollins

It probably relates to the commands in the SP.
From the error message, it appears that
   sp_bindsesion
is being called.  There are lots of way that can fail, including if a previous/concurrent call is already running and something that the SP does locks a table.

What I'd do is try the SQLExecDirect with a simple SP.  If that never fails, try adding back parts of the current SP until you can get a failure.  The problem will be related to that last port of the SP that you moist recently added.

If you cannot access or change the SP, then you may need to find ways to avoid having multiple processes access it concurrently.
Aaron Jabamani

ASKER
Hi,
   Since it is a web applications, there will be multiple requests trying to access this method. Can you please explain a little the best way to avoid this mulitple process accessing it ?

As you said i cannot access or change the SP and also adding back parts will take long time since there are various code paths to this method.

-Aaron
ASKER CERTIFIED SOLUTION
DanRollins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
DanRollins

My answer might be useful, but as there was no reply, I recommend
delete - no refund.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes