Help on connecting to MS SQL Server 2012

Hi,

I installed the latest 3.0 version of Traccar on Windows Server 2012 machine.
The interface works just fine. I see maps, I add devices, users etc.
I added 2 devices.

I installed MS SQL Server 2012, and made a test db (no Traccar db)
I installed JDBC driver 4.2.
I installed Squirell and checked that it reads the test DB in SQL Server just fine.
So my JDBC 4.2 driver works fine.

I did all changes in Traccar.xml in Conf directory, according to your page
https://www.traccar.org/ms-sql/

=========================
Here is the Traccar.xml

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE properties SYSTEM 'http://java.sun.com/dtd/properties.dtd'>
<properties>

<!-- SERVER CONFIG -->
<entry key='web.enable'>true</entry>
<entry key='web.port'>8082</entry>
<entry key='web.path'>C:\Program Files (x86)\Traccar\web</entry>

<entry key='geocoder.enable'>true</entry>
<entry key='geocoder.type'>google</entry>

<entry key='logger.enable'>true</entry>
<entry key='logger.level'>all</entry>
<entry key='logger.file'>C:\Program Files (x86)\Traccar\logs\tracker-server.log</entry>

<!-- DATABASE CONFIG -->
<entry key='database.driverFile'>C:\ANX\sqljdbc42.jar</entry>
<entry key='database.driver'>com.microsoft.sqlserver.jdbc.SQLServerDriver</entry>
<entry key='database.dataSource'>com.microsoft.sqlserver.jdbc.SQLServerDataSource</entry>
<entry key='database.url'>jdbc:sqlserver://MyServer;user=sa;password=MyPassWord</entry>
<entry key='database.user'>sa</entry>
<entry key='database.password'>MyPassWord</entry>

<entry key='database.checkTable'>traccar</entry>

<entry key='database.createSchema'>

    CREATE TABLE [user] (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(1024) NOT NULL,
    email VARCHAR(256) NOT NULL UNIQUE,
    hashedPassword VARCHAR(1024) NOT NULL,
    salt VARCHAR(1024) DEFAULT '' NOT NULL,
    readonly BIT DEFAULT 0 NOT NULL,
    admin BIT DEFAULT 0 NOT NULL,
    map VARCHAR(1024) DEFAULT 'osm' NOT NULL,
    language VARCHAR(1024) DEFAULT 'en' NOT NULL,
    distanceUnit VARCHAR(1024) DEFAULT 'km' NOT NULL,
    speedUnit VARCHAR(1024) DEFAULT 'kmh' NOT NULL,
    latitude FLOAT DEFAULT 0 NOT NULL,
    longitude FLOAT DEFAULT 0 NOT NULL,
    zoom INT DEFAULT 0 NOT NULL);

CREATE TABLE [device] (
    id INT PRIMARY KEY IDENTITY(1,1),
    name VARCHAR(1024) NOT NULL,
    uniqueId VARCHAR(256) NOT NULL UNIQUE,
    status VARCHAR(1024),
    lastUpdate DATETIME,
    positionId INT,
    dataId INT);

CREATE TABLE user_device (
    userId INT NOT NULL,
    deviceId INT NOT NULL,
    [read] BIT DEFAULT 1 NOT NULL,
    write BIT DEFAULT 1 NOT NULL,
    FOREIGN KEY (userId) REFERENCES [user](id) ON DELETE CASCADE,
    FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);

CREATE INDEX user_device_user_id ON user_device(userId);

CREATE TABLE position (
    id INT PRIMARY KEY IDENTITY(1,1),
    protocol VARCHAR(1024),
    deviceId INT NOT NULL,
    serverTime DATETIME NOT NULL,
    deviceTime DATETIME NOT NULL,
    fixTime DATETIME NOT NULL,
    valid BIT NOT NULL,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL,
    altitude FLOAT NOT NULL,
    speed FLOAT NOT NULL,
    course FLOAT NOT NULL,
    address VARCHAR(1024),
    other VARCHAR(MAX) NOT NULL,
    FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);

CREATE TABLE data (
    id INT PRIMARY KEY IDENTITY(1,1),
    protocol VARCHAR(1024),
    deviceId INT NOT NULL,
    serverTime DATETIME NOT NULL,
    deviceTime DATETIME NOT NULL,
    other VARCHAR(MAX) NOT NULL,
    FOREIGN KEY (deviceId) REFERENCES device(id));

ALTER TABLE device ADD
    FOREIGN KEY (positionId) REFERENCES position(id);

ALTER TABLE device ADD
    FOREIGN KEY (dataId) REFERENCES data(id);

CREATE TABLE server (
    id INT PRIMARY KEY IDENTITY(1,1),
    registration BIT NOT NULL,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL,
    zoom INT NOT NULL);

CREATE TABLE traccar (
    id INT PRIMARY KEY IDENTITY(1,1));

</entry>

<entry key='database.selectServers'>
    SELECT * FROM server;
</entry>

<entry key='database.insertServer'>
    INSERT INTO server (registration, latitude, longitude, zoom)
    VALUES (:registration, :latitude, :longitude, :zoom);
</entry>

<entry key='database.updateServer'>
    UPDATE server SET registration = :registration WHERE id = :id;
</entry>

<entry key='database.loginUser'>
    SELECT * FROM [user]
    WHERE email = :email;
</entry>

<entry key='database.selectUsersAll'>
    SELECT * FROM [user];
</entry>

<entry key='database.insertUser'>
    INSERT INTO [user] (name, email, hashedPassword, salt, admin)
    VALUES (:name, :email, :hashedPassword, :salt, :admin);
</entry>

<entry key='database.updateUser'>
    UPDATE [user] SET name = :name, email = :email, admin = :admin
    WHERE id = :id;
</entry>

<entry key='database.updateUserPassword'>
    UPDATE [user] SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id;
</entry>

<entry key='database.deleteUser'>
    DELETE FROM [user] WHERE id = :id;
</entry>

<entry key='database.getPermissionsAll'>
    SELECT userId, deviceId FROM user_device;
</entry>

<entry key='database.selectDevicesAll'>
    SELECT * FROM device;
</entry>

<entry key='database.selectDevices'>
    SELECT * FROM device d INNER JOIN user_device ud ON d.id = ud.deviceId WHERE ud.userId = :userId;
</entry>

<entry key='database.insertDevice'>
    INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);
</entry>

<entry key='database.updateDevice'>
    UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;
</entry>

<entry key='database.deleteDevice'>
    DELETE FROM device WHERE id = :id;
</entry>

<entry key='database.linkDevice'>
    INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);
</entry>

<entry key='database.selectPositions'>
    SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;    
</entry>

<entry key='database.insertPosition'>
    INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other)
    VALUES (:deviceId, GETDATE(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :other);
</entry>

<entry key='database.selectLatestPositions'>
    SELECT * FROM position WHERE id IN (SELECT positionId FROM device);
</entry>

<entry key='database.updateLatestPosition'>
    UPDATE device SET positionId = :id WHERE id = :deviceId;
</entry>

<!-- PROTOCOL CONFIG -->
<entry key='h02.port'>5013</entry>

</properties>

Here is the tracker-server.log

.........
2015-07-29 03:57:49 INFO: Starting server...
2015-07-29 03:57:49 INFO: Operating System name: Windows Server 2012 version: 6.2 architecture: amd64
2015-07-29 03:57:49 INFO: Java Runtime name: Java HotSpot(TM) 64-Bit Server VM vendor: Oracle Corporation version: 25.51-b03
2015-07-29 03:57:49 INFO: Memory Limit heap: 3605mb non-heap: 0mb
2015-07-29 03:57:49 INFO: Version: 3.0-SNAPSHOT
2015-07-29 12:10:26 DEBUG: [A849813B: 5013 <- 62.74.23.9] - HEX: 2a48512c343230383033323536312c56312c3039303934312c562c333933372e393831322c4e2c30323232342e303735382c452c3030302e30302c3030302c3239303731352c46464646464246462c3230322c30352c302c302c3623
2015-07-29 12:10:26 INFO: device: 3, time: Wed Jul 29 12:09:41 EEST 2015, lat: 39.63302, lon: 22.401263333333333
2015-07-29 12:10:26 DEBUG: [A849813B: 5013 <- 62.74.23.9] - HEX: 2a48512c343230383033323536312c56392c3030303034392c562c333933372e393831322c4e2c30323232342e303735382c452c3030302e30302c3030302c3031303131332c2c2c464646464642464623
......... and goes on with data like these above .........

Here is my wrapper.log

STATUS | wrapper | 2015/07/29 03:54:20 | <-- Wrapper Stopped
STATUS | wrapper | 2015/07/29 03:57:30 | --> Wrapper Started as Service
STATUS | wrapper | 2015/07/29 03:57:31 | Java Service Wrapper Community Edition 64-bit 3.5.26
STATUS | wrapper | 2015/07/29 03:57:31 | Copyright (C) 1999-2014 Tanuki Software, Ltd. All Rights Reserved.
STATUS | wrapper | 2015/07/29 03:57:31 | http://wrapper.tanukisoftware.com
STATUS | wrapper | 2015/07/29 03:57:31 |
STATUS | wrapper | 2015/07/29 03:57:31 | Launching a JVM...
INFO | jvm 1 | 2015/07/29 03:57:38 | WrapperManager: Initializing...
INFO | jvm 1 | 2015/07/29 03:57:49 | 2015-07-29 03:57:49.347:INFO:oejs.Server:jetty-8.1.17.v20150415
INFO | jvm 1 | 2015/07/29 03:57:49 | 2015-07-29 03:57:49.426:INFO:oejs.AbstractConnector:Started SelectChannelConnector@0.0.0.0:8082

With all these above I see the Interface working fine, showing points, of devices etc.
I checked in MS SQL Server and there is no database there.

So, the questions are:

All these coordinates that I see on maps in the interface where are they stored??? From where Traccar reads them and display them as long as I have set it up for MS SQL and in SQL there is no database???
Why I don't see anything in SQL Server, no new database etc. In an other issue, Anton Tananaef said that for Traccar 3.0 the documentation about MS SQL Server is not correct. So if Anton reads my issue, could he please update the documentation about MS SQL Server??? If someone else knows the changes for MS SQL Server please let me know.
In Traccar.xml there is no tag giving the database name or something. So what is the name of the database that traccar creates??? Could we fix the name, or it's always the same???
Do I have to keep in Traccar.xml the tag below???
<entry key='database.checkTable'>traccar</entry>
What is its meaning ??? What does it do???

Thanks in advance for your help.
XCrystall

user19799 years ago

Maybe you can define your database name like that:
<entry key=’database.url’>jdbc:sqlserver://MyServer;databaseName=yourDBname;user=sa;password=MyPassWord</entry>
Please take a look at https://msdn.microsoft.com/en-us/library/ms378428%28v=sql.110%29.aspx

Anton Tananaev9 years ago

I think @user1979 is right. You didn't specify the database name in the Traccar config file, so I guess it's using the default one whichever it is.

user19799 years ago

About your question 4. According to documentation:
database.checkTable:Name of a database table used to check if database schema has been generated. Default value is “traccar”.

user19799 years ago

About your question 1. Maybe there is a misconfiguration in your Traccar.xml for mssql and automatically switches to H2 support. In your conf I see the following:
<entry key=’database.driverFile’>C:\ANX\sqljdbc42.jar</entry>
The documentation says:
Extract ‘sqljdbc4.jar’ from the archive and put it into the Traccar ‘lib’ directory
Is C:\ANX\ your traccar lib?

user19799 years ago

About question 2. It's related to question 1. If traccar cannot connect to mssql server then it won't generate any tables...

user19799 years ago

Also, after any change in traccar.xml please restart the traccar service (maybe for that reason your traccar still looks at H2 db). Did you restart the service?

user19799 years ago

I connect to mssql with the following settings in traccar.xml:

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE properties SYSTEM 'http://java.sun.com/dtd/properties.dtd'>

<properties>

    <!-- SERVER CONFIG -->

    <entry key='web.enable'>true</entry>
    <entry key='web.port'>8082</entry>
    <entry key='web.path'>C:\Program Files (x86)\Traccar\web</entry>

    <entry key='geocoder.enable'>true</entry>
    <entry key='geocoder.type'>google</entry>

    <entry key='logger.enable'>true</entry>
    <entry key='logger.level'>all</entry>
    <entry key='logger.file'>C:\Program Files (x86)\Traccar\logs\tracker-server.log</entry>

    <!-- DATABASE CONFIG -->

    <entry key='database.driverFile'>C:\Program Files (x86)\Traccar\lib\sqljdbc4.jar</entry>
    <entry key='database.driver'>com.microsoft.sqlserver.jdbc.SQLServerDriver</entry>
    <entry key='database.url'>jdbc:sqlserver://192.168.2.100;instanceName=SQLEXPRESS;databaseName=traccar;</entry>
    <entry key='database.user'>traccaruser</entry>
   <entry key='database.password'>mypassword</entry>

    <entry key='database.createSchema'>
        CREATE TABLE [user] (
        id INT PRIMARY KEY IDENTITY(1,1),
        name VARCHAR(1024) NOT NULL,
        email VARCHAR(256) NOT NULL UNIQUE,
        hashedPassword VARCHAR(1024) NOT NULL,
        salt VARCHAR(1024) DEFAULT '' NOT NULL,
        readonly BIT DEFAULT 0 NOT NULL,
        admin BIT DEFAULT 0 NOT NULL,
        map VARCHAR(1024) DEFAULT 'osm' NOT NULL,
        language VARCHAR(1024) DEFAULT 'en' NOT NULL,
        distanceUnit VARCHAR(1024) DEFAULT 'km' NOT NULL,
        speedUnit VARCHAR(1024) DEFAULT 'kmh' NOT NULL,
        latitude FLOAT DEFAULT 0 NOT NULL,
        longitude FLOAT DEFAULT 0 NOT NULL,
        zoom INT DEFAULT 0 NOT NULL);

        CREATE TABLE [device] (
        id INT PRIMARY KEY IDENTITY(1,1),
        name VARCHAR(1024) NOT NULL,
        uniqueId VARCHAR(256) NOT NULL UNIQUE,
        status VARCHAR(1024),
        lastUpdate DATETIME,
        positionId INT,
        dataId INT);

        CREATE TABLE user_device (
        userId INT NOT NULL,
        deviceId INT NOT NULL,
        [read] BIT DEFAULT 1 NOT NULL,
        write BIT DEFAULT 1 NOT NULL,
        FOREIGN KEY (userId) REFERENCES [user](id) ON DELETE CASCADE,
        FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);

        CREATE INDEX user_device_user_id ON user_device(userId);

        CREATE TABLE position (
        id INT PRIMARY KEY IDENTITY(1,1),
        protocol VARCHAR(1024),
        deviceId INT NOT NULL,
        serverTime DATETIME NOT NULL,
        deviceTime DATETIME NOT NULL,
        fixTime DATETIME NOT NULL,
        valid BIT NOT NULL,
        latitude FLOAT NOT NULL,
        longitude FLOAT NOT NULL,
        altitude FLOAT NOT NULL,
        speed FLOAT NOT NULL,
        course FLOAT NOT NULL,
        address VARCHAR(1024),
        other VARCHAR(MAX) NOT NULL,
        FOREIGN KEY (deviceId) REFERENCES device(id) ON DELETE CASCADE);

        CREATE TABLE data (
        id INT PRIMARY KEY IDENTITY(1,1),
        protocol VARCHAR(1024),
        deviceId INT NOT NULL,
        serverTime DATETIME NOT NULL,
        deviceTime DATETIME NOT NULL,
        other VARCHAR(MAX) NOT NULL,
        FOREIGN KEY (deviceId) REFERENCES device(id));

        ALTER TABLE device ADD
        FOREIGN KEY (positionId) REFERENCES position(id);

        ALTER TABLE device ADD
        FOREIGN KEY (dataId) REFERENCES data(id);

        CREATE TABLE server (
        id INT PRIMARY KEY IDENTITY(1,1),
        registration BIT NOT NULL,
        latitude FLOAT NOT NULL,
        longitude FLOAT NOT NULL,
        zoom INT NOT NULL);

        CREATE TABLE traccar (
        id INT PRIMARY KEY IDENTITY(1,1));

    </entry>

    <entry key='database.selectServers'>
        SELECT * FROM server;
    </entry>

    <entry key='database.insertServer'>
        INSERT INTO server (registration, latitude, longitude, zoom)
        VALUES (:registration, :latitude, :longitude, :zoom);
    </entry>

    <entry key='database.updateServer'>
        UPDATE server SET registration = :registration WHERE id = :id;
    </entry>

    <entry key='database.loginUser'>
SELECT * FROM [user]
        WHERE email = :email;
    </entry>

    <entry key='database.selectUsersAll'>
          SELECT * FROM "user";
    </entry>

  <entry key='database.insertUser'>
        INSERT INTO [user] (name, email, hashedPassword, salt, admin)
        VALUES (:name, :email, :hashedPassword, :salt, :admin);
    </entry>

    <entry key='database.updateUser'>
        UPDATE [user] SET name = :name, email = :email, admin = :admin
        WHERE id = :id;
    </entry>

    <entry key='database.updateUserPassword'>
        UPDATE [user] SET hashedPassword = :hashedPassword, salt = :salt WHERE id = :id;
    </entry>
    
    <entry key='database.deleteUser'>
        DELETE FROM [user] WHERE id = :id;
    </entry>

    <entry key='database.getPermissionsAll'>
        SELECT userId, deviceId FROM user_device;
    </entry>
    
    <entry key='database.selectDevicesAll'>
        SELECT * FROM device;
    </entry>
    
    <entry key='database.selectDevices'>
        SELECT * FROM device d INNER JOIN user_device ud ON d.id = ud.deviceId WHERE ud.userId = :userId;
    </entry>
    
    <entry key='database.insertDevice'>
        INSERT INTO device (name, uniqueId) VALUES (:name, :uniqueId);
    </entry>
    
    <entry key='database.updateDevice'>
        UPDATE device SET name = :name, uniqueId = :uniqueId WHERE id = :id;
    </entry>
    
    <entry key='database.deleteDevice'>
        DELETE FROM device WHERE id = :id;
    </entry>
    
    <entry key='database.linkDevice'>
        INSERT INTO user_device (userId, deviceId) VALUES (:userId, :deviceId);
    </entry>

    <entry key='database.selectPositions'>
        SELECT * FROM position WHERE deviceId = :deviceId AND fixTime BETWEEN :from AND :to;    
    </entry>

    <entry key='database.insertPosition'>
        INSERT INTO position (deviceId, serverTime, deviceTime, fixTime, valid, latitude, longitude, altitude, speed, course, address, other)
        VALUES (:deviceId, GETDATE(), :time, :time, :valid, :latitude, :longitude, :altitude, :speed, :course, :address, :other);
    </entry>

    <entry key='database.selectLatestPositions'>
        SELECT * FROM position WHERE id IN (SELECT positionId FROM device);
    </entry>

    <entry key='database.updateLatestPosition'>
        UPDATE device SET positionId = :id WHERE id = :deviceId;
    </entry>

    <!-- PROTOCOL CONFIG -->

    <entry key='detector.port'>5000</entry>
    <entry key='gps103.port'>5001</entry>
    <entry key='tk103.port'>5002</entry>
    ...
    <entry key='castel.port'>5086</entry>

</properties>

mbald9 years ago

Hi Anton and user1979, is this post dead? Did the user resolved the issue?

Which is the correct configuration procedure? In the connection string, should I use the USER_NAME of the database or the LOGIN_NAME of the server?

Anton Tananaev9 years ago

Unfortunately I don't have Windows or MS SQL to test the config, but I know many people successfully use MS SQL with Traccar.

For more information about JDBC URL, you can read Microsoft documentation:

https://msdn.microsoft.com/en-us/library/ms378428(v=sql.110).aspx

Help on connecting to MS SQL Server 2012

=========================Here is the Traccar.xml

</properties>

Here is the tracker-server.log

Here is my wrapper.log

So, the questions are:

=========================
Here is the Traccar.xml