# Jdbc\_static and mssql server

**URL:** <https://discuss.elastic.co/t/jdbc-static-and-mssql-server/184170>\
**Category:** Logstash\
**Created:** [June 4, 2019, 12:02pm UTC](https://discuss.elastic.co/t/jdbc-static-and-mssql-server/184170 "2019-06-04T12:02:09Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sam\_Kisada](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sam_kisada/32/44452_2.png) [@Sam\_Kisada](https://discuss.elastic.co/u/Sam_Kisada)\
**Post date:** [June 4, 2019, 12:02pm UTC](https://discuss.elastic.co/t/jdbc-static-and-mssql-server/184170/1 "2019-06-04T12:02:09Z")

</div>

Hi!  
I trying to get some fields through jdbc\_static:

```
if "grokked" not in [tags] {
  grok { 
    match => ["message", "{%{DATESTAMP_EVENTLOG:Date},%{INT:ID}:%{GREEDYDATA:GUID}]
    add_tag => ["grokked"]
    tag_on_failure => ["3_failure"]
  }
  mutate {
    replace => {"GUID" => "0x%{GUID}"}
    }
  if [ID] == "1142" {
   jdbc_static {
     loaders => [ 
     {
       id => "1142"
       query => "SELECT _IDRRef,_Fld25177 FROM dbo._Document1142"
       local_table => "1142"
     }
     ]        
     local_db_objects => [ 
     {
      name => "1142"
      index_columns => ["_IDRRef"]
      columns => [
        ["_IDRRef", "BINARY(16)"],
        ["_Fld25177", "NVARCHAR(211)"]
        ]
     }
     ]
     local_lookups => [ 
      {
       id => "1142"
       query => "select _Fld25177 as doc from 1142 WHERE _IDRRef = :GUID_sql"
       parameters => {GUID_sql => "[GUID]"}
       target => "sql"
      }
      ]
      staging_directory => "c:\elk\data\import_data"		  
      loader_schedule => "*/30 * * * *"
      jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
      jdbc_driver_library => "c:\elk\jdbc\sqljdbc_7.2\rus\mssql-jdbc-7.2.2.jre8.jar"
      jdbc_connection_string => "jdbc:sqlserver://db2:1433;databaseName=s2019;integratedSecurity=true;user=login;password=RRRRRRRRRR"
    }
   }    

```

and I geting error

```
[ERROR][logstash.filters.jdbc.readwritedatabase] Exception when initialising lookup db for db object
.....
Syntax error: Encountered \"(\" at line 1, column 38."......

```

also I tried to use jdbc 4.2 6.2, playaround with jdbc\_user/password, change data type.  
When I was change BINARY(16) to varchar file c:\elk\data\import\_data\1142 was appeared with data from mssql and logstash truncation error:

> CAST(X'80b5005056bda97911e7c54a20fff4b0' AS BLOB), 'target data'

so wat is wrong?

---

<div class="post-metadata">

**Author:** ![Sam\_Kisada](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/sam_kisada/32/44452_2.png) [@Sam\_Kisada](https://discuss.elastic.co/u/Sam_Kisada)\
**Post date:** [June 5, 2019, 9:00am UTC](https://discuss.elastic.co/t/jdbc-static-and-mssql-server/184170/2 "2019-06-05T09:00:21Z")

</div>

Binary type is not supported(  
I was change binary(16) to varchar(64) and it works well

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [July 3, 2019, 9:09am UTC](https://discuss.elastic.co/t/jdbc-static-and-mssql-server/184170/3 "2019-07-03T09:09:48Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.