# Logstash cannot call SQL Stored Procedure

**URL:** <https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271>\
**Category:** Logstash\
**Created:** [March 14, 2016, 1:35am UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271 "2016-03-14T01:35:46Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![vikram\_yerneni](https://avatars.discourse-cdn.com/v4/letter/v/3ab097/32.png) [@vikram\_yerneni](https://discuss.elastic.co/u/vikram_yerneni)\
**Post date:** [March 14, 2016, 1:35am UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/1 "2016-03-14T01:35:46Z")

</div>

Hi ELK Folks,  
I have an issue with pulling the data by calling a SQL Stored Procedure. I am using logstash agent to push the data and we are trying to pull in SQL Data in the form of a Stored Procedure.  
Its keep on throwing an error saying that the syntax is wrong...  
I checked the documentation to find what is the correct syntax to use in the .conf file to call data from a stored procedure..  
Any inputs here folks..

Thanks  
Vikram Y

---

<div class="post-metadata">

**Author:** ![magnusbaeck](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/magnusbaeck/32/44943_2.png) [@magnusbaeck](https://discuss.elastic.co/u/magnusbaeck)\
**Post date:** [March 15, 2016, 9:27pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/2 "2016-03-15T21:27:45Z")

</div>

If you show us what you've tried (i.e. your configuration) and the exact error message it might be possible to help you.

---

<div class="post-metadata">

**Author:** ![vikram\_yerneni](https://avatars.discourse-cdn.com/v4/letter/v/3ab097/32.png) [@vikram\_yerneni](https://discuss.elastic.co/u/vikram_yerneni)\
**Post date:** [March 17, 2016, 8:10pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/3 "2016-03-17T20:10:10Z")

</div>

Hi Magnus Bäck,  
We got the solution here. Instead of the statement (where we add the sql stament) we called a file with the stored proc with value 'statement\_filepath" and it worked fine.  
Thanks  
Vikram Y

---

<div class="post-metadata">

**Author:** ![vtangutoori](https://avatars.discourse-cdn.com/v4/letter/v/ee7513/32.png) [@vtangutoori](https://discuss.elastic.co/u/vtangutoori)\
**Post date:** [August 17, 2016, 7:26pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/4 "2016-08-17T19:26:45Z")

</div>

Hi Vikram,  
I am a newbie to ElasticSearch, can you please provide he steps you took in order to achieve this.

Thanks & Regards,  
vamsi

---

<div class="post-metadata">

**Author:** ![vikram\_yerneni](https://avatars.discourse-cdn.com/v4/letter/v/3ab097/32.png) [@vikram\_yerneni](https://discuss.elastic.co/u/vikram_yerneni)\
**Post date:** [August 17, 2016, 7:47pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/5 "2016-08-17T19:47:37Z")

</div>

Hi Vamsidhar,  
If u use the full sql statement then use this in ur logstash config file:  
statement =\> "SELECT Distinct([StartInterv\*\*\*\*  
If u use a Stored Proc, then save a txt file with the stored proc name and use this in ur logstash config file:  
statement\_filepath =\> "C:\Users\filename.txt"

Thanks  
Vikram Y

---

<div class="post-metadata">

**Author:** ![vtangutoori](https://avatars.discourse-cdn.com/v4/letter/v/ee7513/32.png) [@vtangutoori](https://discuss.elastic.co/u/vtangutoori)\
**Post date:** [August 18, 2016, 2:01am UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/6 "2016-08-18T02:01:54Z")

</div>

Hi Vikram,

Thanks for the quick response, i tried the below method but am still getting the error, please check the config file and the text file below and let me know what i am doing wrong here.

config:  
input {  
jdbc {  
jdbc\_driver\_library =\> "C:\Program Files\Microsoft SQL Server JDBC Driver\sqljdbc\_6.0\enu\sqljdbc4.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\> "jdbc:sqlserver://192.168.91.34\devohms;databaseName=YOLOMED"  
jdbc\_user =\> "vamsi"  
jdbc\_password =\> "vamsi"  
file{  
path =\> "C:\ElasticSearch\logstash-2.3.4\bin\File\_Name.txt"  
}  
jdbc\_paging\_enabled =\> "true"  
jdbc\_page\_size =\> "50000"  
}  
}

# IF you want to add Filter you can add one

# filter {

# .....

#}

output {  
elasticsearch {  
hosts =\> "localhost:9200"  
index =\> "indexname"  
document\_id =\> "%{column\_name}"  
document\_type =\> "searchresults"  
manage\_template =\> true  
}  
stdout { codec =\> rubydebug }  
}

File:

DECLARE @RC int

EXECUTE @RC = [dbo].[Stored\_Procedure\_Name]

Thanks & Regards,  
Vamsi

---

<div class="post-metadata">

**Author:** ![vikram\_yerneni](https://avatars.discourse-cdn.com/v4/letter/v/3ab097/32.png) [@vikram\_yerneni](https://discuss.elastic.co/u/vikram_yerneni)\
**Post date:** [August 18, 2016, 3:08pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/7 "2016-08-18T15:08:52Z")

</div>

Try "statement\_filepath " instead of file{  
path =\> "C:\ElasticSearch\logstash-2.3.4\bin\File\_Name.txt"  
}  
Thanks  
Vikram Y

---

<div class="post-metadata">

**Author:** ![vtangutoori](https://avatars.discourse-cdn.com/v4/letter/v/ee7513/32.png) [@vtangutoori](https://discuss.elastic.co/u/vtangutoori)\
**Post date:** [August 18, 2016, 3:20pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/8 "2016-08-18T15:20:15Z")

</div>

I have tried the method and it is trowing jdbc sql server exception "incorrect syntax near execute", i have attached the image below.

i have changed the file to have just the below code

execute dbo.procedurename

 ![](https://us1.discourse-cdn.com/elastic/original/2X/7/724a2ef61c3a91d458d64cfb3d5dd80dc48a793e.png)

---

<div class="post-metadata">

**Author:** ![vikram\_yerneni](https://avatars.discourse-cdn.com/v4/letter/v/3ab097/32.png) [@vikram\_yerneni](https://discuss.elastic.co/u/vikram_yerneni)\
**Post date:** [August 18, 2016, 3:34pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/9 "2016-08-18T15:34:00Z")

</div>

It seems ur syntax had some issues. U r using wrong parameters dude. Use mine instead, it will work:

input {  
jdbc {  
jdbc\_driver\_library =\> "C:\Program Files\sqljdbc\_6.0\enu\sqljdbc42.jar"  
jdbc\_driver\_class =\> "com.microsoft.sqlserver.jdbc.SQLServerDriver"  
jdbc\_connection\_string =\> "jdbc:sqlserver://Instancename\DBName:1433;"  
jdbc\_user =\> "username"  
jdbc\_password =\> "password"  
statement\_filepath =\> "C:\Users\filepath\sql.txt"  
}  
}

filter {  
mutate {  
}

}

output {  
stdout { codec =\> "rubydebug" }  
}

Thanks  
Vikram Y

---

<div class="post-metadata">

**Author:** ![vtangutoori](https://avatars.discourse-cdn.com/v4/letter/v/ee7513/32.png) [@vtangutoori](https://discuss.elastic.co/u/vtangutoori)\
**Post date:** [August 18, 2016, 3:41pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/10 "2016-08-18T15:41:30Z")

</div>

Hi Vikram,

Thank you so much it worked like a charm.

Thanks & Regards,  
Vamsi

---

<div class="post-metadata">

**Author:** ![vikram\_yerneni](https://avatars.discourse-cdn.com/v4/letter/v/3ab097/32.png) [@vikram\_yerneni](https://discuss.elastic.co/u/vikram_yerneni)\
**Post date:** [August 18, 2016, 3:54pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/11 "2016-08-18T15:54:06Z")

</div>

Cheers buddy.. 🙂

---

<div class="post-metadata">

**Author:** ![Ashish\_Viradia](https://avatars.discourse-cdn.com/v4/letter/a/c6cbf5/32.png) [@Ashish\_Viradia](https://discuss.elastic.co/u/Ashish_Viradia)\
**Post date:** [March 30, 2017, 12:51am UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/12 "2017-03-30T00:51:05Z")

</div>

I am using **Logstash 5.3** and MS SQLServer 2008 R2

Not sure how it worked for you guys... It seems that plugin runs the given SQL as subquery to figure out the column names for prepping the output JSON...

Even placing the SQL in the file and using statement\_filepath =\> "c:\mysql.sql" didn't work... ☹

All the help is much appreciated... Thanks

**config**

```
input {
  jdbc {

   jdbc_driver_library => "C:/sqljdbc/sqljdbc_6.0/enu/sqljdbc42.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://oursqlserver:12230;databaseName=WorkArea"
    jdbc_user => "dbuser"
    jdbc_password => " ****"
    jdbc_paging_enabled => "true"
    jdbc_page_size => "50000"
    statement => ["exec dbo.stp_get_delta_info 999"]
   }
}

```

**Error**

17:22:25.729 [[main]\<jdbc] INFO logstash.inputs.jdbc - (0.173000s) SELECT CAST(SERVERPROPERTY('ProductVersion') AS varchar)  
17:22:25.810 [[main]\<jdbc] ERROR logstash.inputs.jdbc - Java::ComMicrosoftSqlserverJdbc::SQLServerException:Incorrect syntax near the keyword 'exec'.: SELECT TOP (1) count(\*)  
AS [COUNT] FROM (exec dbo.stp\_get\_delta\_info 999) AS [T1]  
17:22:25.813 [[main]\<jdbc] WARN logstash.inputs.jdbc - Exception when executing JDBC query {:exception=\>#\<Sequel::DatabaseError: Java::ComMicrosoftSqlserverJdbc::SQLServerExc  
eption: Incorrect syntax near the keyword 'exec'.\>}  
17:22:28.567 [LogStash::Runner] WARN logstash.agent - stopping pipeline {:id=\>"main"}

---

<div class="post-metadata">

**Author:** ![Exocomp](https://avatars.discourse-cdn.com/v4/letter/e/ea5d25/32.png) [@Exocomp](https://discuss.elastic.co/u/Exocomp)\
**Post date:** [May 9, 2017, 1:08pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/13 "2017-05-09T13:08:32Z")

</div>

@Ashish_Viradia

I found this to be a bug, if you run logstash in debug mode the jdbc plugin will do a SELECT TOP(1) on the stored procedure which is invalid.

However, if logstash is not in debug mode then it works fine. Not the best solution but at least a work around.

E

---

<div class="post-metadata">

**Author:** ![Juanps2](https://avatars.discourse-cdn.com/v4/letter/j/bcef8e/32.png) [@Juanps2](https://discuss.elastic.co/u/Juanps2)\
**Post date:** [May 17, 2017, 4:26pm UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/14 "2017-05-17T16:26:49Z")

</div>

I can't run logstash with SQL Stored Procedure. I put the sql statemente in a separate file but I receive the same error always

How I can disable the debug mode?. --log.level=?

I used this command line "./logstash -f importsql.conf --log.level=error" in my osx installation. But always I receive the error SQLServerException due it tries to do [Select top (1) count(\*) as [Count] from (exec .......) as [T1].

Please can anyone help me?

Here a list of possible values for logging...

--log.level LEVEL  
Set the log level for Logstash. Possible values are:

fatal: log very severe error messages that will usually be followed by the application aborting  
error: log errors  
warn: log warnings  
info: log verbose info (this is the default)  
debug: log debugging info (for developers)  
trace: log finer-grained messages beyond debugging info

---

<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 6, 2017, 4:26am UTC](https://discuss.elastic.co/t/logstash-cannot-call-sql-stored-procedure/44271/15 "2017-07-06T04:26:30Z")

</div>