Summary:	ASTERISK-19423: Issue regarding CDR_ADAPTIVE_ODBC.c versus CDR_ODBC.c
Reporter:	Jeff Hoppe (jhoppebugs)	Labels:
Date Opened:	2012-02-22 10:47:41.000-0600	Date Closed:	2012-02-23 08:48:06.000-0600
Priority:	Major	Regression?
Status:	Closed/Complete	Components:	CDR/cdr_adaptive_odbc CDR/cdr_odbc
Versions:	10.1.0	Frequency of Occurrence	Frequent
Related Issues:
Environment:	CentOS with MS Sql Server 2008 (FreeTDS)	Attachments:
Description:	When using CDR_adaptive_odbc I lose about 60% of my CDR records logging to SQL Server.  The full log inidicates that the only error or warning is [cdr_adaptive_odbc: Insert failed on 'cdrconnection:dialer_cdr_v03']  (see Reference Notes for snippet of full log).   Tracing through the code I have found the following:   1. The prepare_cb function must be returning successfully because there are no errors|warnings in the full log that it would write.   2. SQLExecute() call in ast_odbc_prepare_and_execute must be returning SQL_SUCCESS, SQL_SUCCESS_WITH_INFO or SQL_NO_DATA for the same reason of no errors|warnings in full log. Sql Server profiler shows that the SQL insert statement is not hitting the server.  If I run the SQL insert statement directly on my SQL Server, it runs successfully.   Also, when I use CDR_ODBC.conf which uses the SQLExecDirect() command, everything works fine. A side note suggestion that I have is to put verbose logging messages at more points in res_odbc.c and cdr_adaptive_odbc.c Through all of my testing on this I have come to my personal conclusion that SQLExecute() is erroring because it can't connect to SQL Server but SQL_NO_DATA is being returned (shot in the dark). Thanks for any help.
Comments:	By: Matt Jordan (mjordan) 2012-02-23 08:46:33.845-0600 This is most likely a function of using Microsoft SQL Server and its handling of SQLPrepare/SQLExecute versus SQLExecDirect.  Depending on your call volume, you may be running into the concurrency problems SQL Server faces with multiple SQLPrepare statements.  Quoting from the article linked below: "SQL Server itself does not directly support the SQLPrepare/SQLExecute model, but the SQL Server ODBC driver can use stored procedures to emulate this behavior. On a SQLPrepare, the driver asks the server to create a stored procedure that contains the SQL statement from the application. On SQLExecute, the driver executes the created stored procedure. The ODBC driver uses stored procedures to support SQLPrepare/SQLExecute when the option is enabled either in the data source or the SQLDriverConnect keywords. For example, if an application calls:" ... (ellipsis mine) "If an application will be run by many concurrent users and the users will all be using the same SQL statement, the best approach is to create the SQL statement as a permanent, parameterized, stored procedure and executed it with SQLExecDirect. Having many users concurrently issue SQLPrepare commands can create a concurrency problem on the system tables in tempdb. Even if each user is executing exactly the same statement, the SQL Server ODBC driver on each client is creating its own copy of a temporary stored procedure in tempdb. If the SQL statement is created as a parameterized stored procedure, however, the procedure is created only once. Each ODBC application does not have to create a new procedure for its exclusive use, it simply uses a copy of the permanent procedure's execution plan from the procedure cache." See: http://msdn.microsoft.com/en-us/library/ms811006.aspx By: Matt Jordan (mjordan) 2012-02-23 08:47:59.314-0600 Note that as other database engines use cdr_adaptive_odbc without issue, I don't believe this is a problem with Asterisk.  While you may want more VERBOSE logging statements (or DEBUG, as that would most likely be more appropriate in this case), that would be an improvement and a feature request, and not a bug.  As such, you're more then welcome to write a patch to the ODBC CDR modules to do that - if so, please contact a bug marshal and have them reopen this issue.