asterisk users - May 2010 - CDR to MS-SQL via ODBC issue

Hi guys, 

Having issue with getting CDR to write to MS-SQL via ODBC.

       > cdr_odbc: Connected to freetds-connector
       > cdr_odbc: Error in PREPARE -1
       > cdr_odbc: Query FAILED Call not logged!
  == Spawn extension (cisco, ##########, 2) exited non-zero on
'IAX2/astYYYY-507


Isql test: 

[xxx at YYYY asterisk]# isql freetds-connector XXXXXXX YYYYYYYYY
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

I can connect to the database and run via isql, and also use func_odbc,
etc with res_odbc configured with the same database / freetds, but I
cannot write CDRs. 

Any ideas would be really appreciated. 

Thanks, 

Neeraj

On Wednesday 05 May 2010 06:51:48 Neeraj Chand wrote:
> I can connect to the database and run via isql, and also use func_odbc,
> etc with res_odbc configured with the same database / freetds, but I
> cannot write CDRs.
Are you writing to the database with func_odbc, or just reading?  My gut says
that you need to check your permissions on the database to ensure that
you're
allowed to write to the CDR table.

-- 
Tilghman Lesher
Digium, Inc. | Senior Software Developer
twitter: Corydon76 | IRC: Corydon76-dig (Freenode)
Check us out at: www.digium.com & www.asterisk.org

------------------------------------------------------------------------
---
Message: 10
Date: Wed, 5 May 2010 10:26:34 -0500
From: Tilghman Lesher <tlesher at digium.com>
Subject: Re: [asterisk-users] CDR to MS-SQL via ODBC issue
To: "Asterisk Users Mailing List - Non-Commercial Discussion"
	<asterisk-users at lists.digium.com>
Message-ID: <201005051026.34929.tlesher at digium.com>
Content-Type: text/plain;  charset="iso-8859-1"

On Wednesday 05 May 2010 06:51:48 Neeraj Chand wrote:
> I can connect to the database and run via isql, and also use
func_odbc,
> etc with res_odbc configured with the same database / freetds, but I
> cannot write CDRs.
>>Are you writing to the database with func_odbc, or just reading?  My
gut says
>>that you need to check your permissions on the database to ensure that
you're
>>allowed to write to the CDR table.

	Hi Tilghman, yeah I thought so too at first but then, using the
same permissions I'm doing both read & writes as well. 

On the database end, the user is setup as database_owner and has db_read
& db_write permissions.

I got Leif to check this with me last night, we couldn't figure it out. 

The error that pops up is: 

       > cdr_odbc: Connected to asterisk-freetds-connector
       > cdr_odbc: Error in PREPARE -1
       > cdr_odbc: Query FAILED Call not logged!


________________________________________________________________________
__

On Wednesday 05 May 2010 18:29:26 Neeraj Chand wrote:
>
------------------------------------------------------------------------
> ---
> Message: 10
> Date: Wed, 5 May 2010 10:26:34 -0500
> From: Tilghman Lesher <tlesher at digium.com>
> Subject: Re: [asterisk-users] CDR to MS-SQL via ODBC issue
> To: "Asterisk Users Mailing List - Non-Commercial Discussion"
> 	<asterisk-users at lists.digium.com>
> Message-ID: <201005051026.34929.tlesher at digium.com>
> Content-Type: text/plain;  charset="iso-8859-1"
>
> On Wednesday 05 May 2010 06:51:48 Neeraj Chand wrote:
> > I can connect to the database and run via isql, and also use
>
> func_odbc,
>
> > etc with res_odbc configured with the same database / freetds, but I
> > cannot write CDRs.
> >
> >>Are you writing to the database with func_odbc, or just reading? 
My
>
> gut says
>
> >>that you need to check your permissions on the database to ensure
that
>
> you're
>
> >>allowed to write to the CDR table.
>
> 	Hi Tilghman, yeah I thought so too at first but then, using the
> same permissions I'm doing both read & writes as well.
>
> On the database end, the user is setup as database_owner and has
db_read
> & db_write permissions.
>
> I got Leif to check this with me last night, we couldn't figure it
out.
>
> The error that pops up is:
>        > cdr_odbc: Connected to asterisk-freetds-connector
>        > cdr_odbc: Error in PREPARE -1
>        > cdr_odbc: Query FAILED Call not logged!
>
>
________________________________________________________________________
> __
>Okay, second idea is that you should very carefully examine your CDR
table
>layout and ensure that the columns that you have match EXACTLY what the
>module expects you to have.  If Asterisk expects you to have a column
that >you
>don't (or the column type is wrong), that is another reason that the
>prepare
>might fail.  You might consider using the cdr_adaptive_odbc driver,
>instead,
>as it is designed to create the insert based upon the structure of the
>table.
-- 
Tilghman Lesher
------------------------------------------------------------------------
-

That did the trick. Had calldate & userfield missing & I had
loguniqueid=yes.

Thanks Leif / Tilghman. 

:)