[Asterisk-Users] MS SQL & Free TDS

[Duane Cox]

Sure thing...

Luke Catranis, Seshu Kanuri, and any others:


Asterisk CVS-HEAD, CDR with MSSQL 7.0 database; via unixODBC and FreeTDS:

cd /usr/src &&
rm -fr unixODBC-2.2.9 &&
tar -zxvf unixODBC-2.2.9.tar.gz &&
cd unixODBC-2.2.9 &&
./configure --sysconfdir=/etc --prefix=/usr --disable-gui &&
make &&
make install

cd /usr/src &&
rm -fr freetds-0.62.4 &&
tar -zxvf freetds-0.62.4.tar.gz &&
cd freetds-0.62.4 &&
./configure --prefix=/usr --with-tdsver=7.0 --with-unixodbc=/usr/lib &&
make &&
make install

ldconfig -v

/etc/rc.d/init.d/asterisk stop

cd /usr/src/asterisk &&
make clean &&
make update &&
make &&
make install

cat > /etc/odbcinst.ini << "EOF"
[FreeTDS]
Description    = FreeTDS ODBC driver for MSSQL
Driver         = /usr/lib/libtdsodbc.so
Setup          = /usr/lib/libtdsS.so
FileUsage      = 1
EOF

cat > /etc/odbc.ini << "EOF"
[MSSQL-asterisk]
description         = Asterisk ODBC for MSSQL
driver              = FreeTDS
server              = IP.IP.IP.IP
port                = 1433
database            = your_database_name_here
tds_version         = 7.0
language            = us_english
EOF

[ -f /etc/asterisk/cdr_tds.conf ] > /etc/asterisk/cdr_tds.conf

cat > /etc/asterisk/cdr_odbc.conf << "EOF"
[global]
dsn=MSSQL-asterisk
username=your_sql_username_here
password=your_sql_password_here
loguniqueid=yes
EOF

cat > cdr_table.sql << "EOF"
CREATE TABLE cdr (
        [calldate]      [datetime]              NOT NULL ,
        [clid]          [varchar] (80)          NOT NULL ,
        [src]           [varchar] (80)          NOT NULL ,
        [dst]           [varchar] (80)          NOT NULL ,
        [dcontext]      [varchar] (80)          NOT NULL ,
        [channel]       [varchar] (80)          NOT NULL ,
        [dstchannel]    [varchar] (80)          NOT NULL ,
        [lastapp]       [varchar] (80)          NOT NULL ,
        [lastdata]      [varchar] (80)          NOT NULL ,
        [duration]      [int]                   NOT NULL ,
        [billsec]       [int]                   NOT NULL ,
        [disposition]   [varchar] (45)          NOT NULL ,
        [amaflags]      [int]                   NOT NULL ,
        [accountcode]   [varchar] (20)          NOT NULL ,
        [uniqueid]      [varchar] (32)          NOT NULL ,
        [userfield]     [varchar] (255)         NOT NULL
)
GO
EOF

echo "modify for your IP, database, username, and password"

/etc/rc.d/init.d/asterisk start




Asterisk CVS-HEAD, CDR with MSSQL 7.0 database; via FreeTDS directly (I like
better):

cd /usr/src &&
rm -fr freetds-0.62.4 &&
tar -zxvf freetds-0.62.4.tar.gz &&
cd freetds-0.62.4 &&
./configure --prefix=/usr --with-tdsver=7.0 &&
make &&
make install

ldconfig -v

/etc/rc.d/init.d/asterisk stop

cd /usr/src/asterisk &&
make clean &&
make update &&
make &&
make install

[ -f /etc/asterisk/cdr_odbc.conf ] > /etc/asterisk/cdr_odbc.conf

cat > /etc/asterisk/cdr_tds.conf << "EOF"
[global]
hostname=IP.IP.IP.IP
port=1433
dbname=your_database_name_here
user=your_sql_username_here
password=your_sql_password_here
charset=BIG5
EOF

cat > cdr_table.sql << "EOF"
CREATE TABLE cdr (
        [accountcode]   [varchar] (20)          NULL ,
        [src]           [varchar] (80)          NULL ,
        [dst]           [varchar] (80)          NULL ,
        [dcontext]      [varchar] (80)          NULL ,
        [clid]          [varchar] (80)          NULL ,
        [channel]       [varchar] (80)          NULL ,
        [dstchannel]    [varchar] (80)          NULL ,
        [lastapp]       [varchar] (80)          NULL ,
        [lastdata]      [varchar] (80)          NULL ,
        [start]         [datetime]              NULL ,
        [answer]        [datetime]              NULL ,
        [end]           [datetime]              NULL ,
        [duration]      [int]                   NULL ,
        [billsec]       [int]                   NULL ,
        [disposition]   [varchar] (20)          NULL ,
        [amaflags]      [varchar] (16)          NULL ,
        [uniqueid]      [varchar] (32)          NULL
)
GO
EOF

echo "modify for your IP, database, username, and password"

/etc/rc.d/init.d/asterisk start




Duane Cox



From: <personal at catranis.net>
To: <asterisk-users at lists.digium.com>
Sent: Wednesday, July 28, 2004 10:07 PM
Subject: Re: [Asterisk-Users] MS SQL & Free TDS


> Didn't see it, but here's my reply again... with none of
> the garbage...
>
>
> Two Specific Issues
>
>   1. With cdr_odbc unixodbc (2.2.9) I can connect with
> tsql and isql to my MS SQL server. But when I try to load
> the cdr_odbc in * I get a segmentation fault and the
> server crashes.
>
> 2. With cdr_tds, I have the .c file but that's it, I do
> not know what to do next, in order to compile the .so to
> load the module, I'm an Idiot please help.
>
>
>
> _______________________________________________
> Asterisk-Users mailing list
> Asterisk-Users at lists.digium.com
> http://lists.digium.com/mailman/listinfo/asterisk-users
> To UNSUBSCRIBE or update options visit:
>    http://lists.digium.com/mailman/listinfo/asterisk-users
>