3cx and Microsoft SQL

Discussion in '3CX Phone System - General' started by ITDOS, Jan 8, 2016.

Thread Status:
Not open for further replies.
  1. ITDOS

    Jan 8, 2016
    Likes Received:
    Hi Guys,

    Hope you're all well!

    I've been advised by our re-seller that as of 2 weeks ago there was an update that may accommodate 3cx and Microsoft SQL?

    We'd like to move away from PostgreSQL and across to Microsoft SQL

    What we would like to know is the following:

    • How do we connect 3cx to Microsoft SQL Server?
    • Once 3cx is connected to Microsoft SQL Server how do we get the database schema (DB names etc.)?
    o Does 3cx automatically create the schema or do we need to run a script that creates it for us ?

    Any advice would be greatly appreciated!

    Kind regards,
  2. Bankstone

    Dec 19, 2015
    Likes Received:

    I have been down this road so many times now
    To the point I actually wrote a routine that extracts the data from Postgres
    into a MS SQL Server, where its much more accessible.
    easier to write reports and can be posted into other applications via XML.

    As you know the reports for 3CX are dreadful and to be honest
    they always have been, even in v14 you have to extract to a csv that has page breaks !
    That's just lazy coding, there is no logical reason to do this.
    The only reason you export to CSV is to import to something else.

    As far as I know, I may be wrong, there is no intention of using MS SQL Server.

    Any way solution time;
    version 9, 10 &11 you hook into Postgres via a SQL ODBC driver and pull
    the data out and push into MS SQL, It also gives you the chance to clean it up as well.

    Options for v14 onwards

    the design of the now table(s), yes there are now 3 of them
    all begin with cl_
    and you need all of them linked together to make any sense of it.
    have changed, I have worked out how they all link, however
    there seems to be an issue with 64bit and 32bit ODBC SQL drivers
    and converting data from one to the other, still working on this.
    It centres around that Postgres puts a Time Zone stamp in a date time storage
    which will not import directly, you have to 'massage' the date data to make it work.

    The other option for v14 is to use the CDR file, this actually can be quite
    useful, but yet again there seems to be some lazy coding, after every line
    there seems to be a double line break, making importing almost impossible
    also the id of the call has had 'call ' stuck in front of it for some reason,
    which makes it difficult to import into an 'INT'
    However there are ways round both of these issues (Quite easy to be honest)
    Unfortunately the data includes every call between queues and queues
    to every phone, you only actually need about 20% of the data that's exported.
    You then run a routine that strips out this 80% and you are left
    with the call data in MS SQL,
    I have added a correct call billing system as well, working out costing's
    for 0844, 0845, 0330, 01, 02 07 mobile etc based on the route it takes out
    this gives a very accurate cost analysis, its just an inner join to the
    call table in MS SQL so its immediate and displays it on a secure web page.

    The reason they use Postgres is because its free,
    MS SQL would create licensing complications,
    Even if you use SQL Express, you would need
    to be competent writing DTS Jobs that the Agent would normally execute.
    You can, as I have, run this on a Windows schedule, which works very well.

    On another note I also converted this via Lame to MP3 to aid storage
    and back up transfer, which helped a lot, I stored by day rather than by extension per month
    This means its easy to get to a call if you know the day it was taken.

    If you need help with SQL or the conversion let me know
    Hope this helps
  3. zic


    Jan 15, 2016
    Likes Received:
    Hi guys,

    Last time i could grab reporting from this 3 tables (callhistory, callhistory2 and callhistory3) but seems like now (version 14) not possible. Any idea what has been changed? thx
Thread Status:
Not open for further replies.