All Apps and Add-ons

Splunk DB Connect 2: GUI runs stored procedure, but fails as DB input. Is there special syntax to run stored procedures or documentation on this?

laurie_gellatly
Communicator

In DB Connect 2, if I run a stored proc via the GUI in step 2 of 4 from DB Inputs, the query returns the correct values.
If I save the input, then the Health dashboard shows an error against py_dbinput and the query fails.
Is there any special syntax required to run a stored procedure?
Is there any documentation on running stored procs?

Thanks ...Laurie:{)

0 Karma

bancaitb_eserci
New Member

FOR SQL Server 2008 R2 we solved the issue with this workaround:

SELECT * FROM OPENQUERY ([YOURSERVER\INSTANCE],'EXEC [YOUR_DB_NAME].[dbo].[YOUR_SP_TO_EXEC]')

To Enable local use of Linked_Server

exec sp_serveroption @server = 'YOURSERVER\INSTANCE' ,@optname = 'DATA ACCESS' ,@optvalue = 'TRUE' 
0 Karma

joao_amorim
Communicator

I'm running two SP's almost for an year in Splunk DB Connect v1.

I also have two Data Inputs running daily (which are calling those SP's from a SQL DB) to populate two different indexes.

And I never had problems. How is this possible? Since it's not supported?

For example in DB con v1 I run: EXEC sp_help and it works.
In DB con v2.2 I run: EXEC sp_help, at step 2 of 4, and it gives error, " External search command 'dbxquery' returned error code 1. Script output = RuntimeError: Failed to run query: "SELECT * FROM (EXEC sp_help) t", caused by: AvroRemoteException(u"java.sql.SQLException: Incorrect syntax near the keyword 'EXEC'.",). "

0 Karma

amanediel
Explorer

hi,
I am facing the same error. did you get the solution for this?

0 Karma

laurie_gellatly
Communicator

I don't recall ever receiving a Splunk fix.

0 Karma

davidpaper
Contributor

Hi,

There is a workaround to be able to query SPs. See https://answers.splunk.com/answering/386947/view.html for more info.

0 Karma

chris24747
Explorer

I've also got this problem. I'm trying to do this on MS SQL 2012.

The procedure works perfectly in step 2 to test, but when i schedule it with CRON it fails.

dbx2.log says:
09/10/2015 14:33:25 [ERROR] [ws.py] [DBInput Service] ERROR: null.
09/10/2015 14:33:25 [ERROR] [websocket.py] ERROR: null.

each time it happens at 24/25 seconds past the time it's scheduled to run.

0 Karma

joao_amorim
Communicator

Hey @laurie_gellatly can you put here how you run the stored procedure from the GUI please?

Cause I'm trying to run one SP but it always gives me error at EXEC.

Regards

0 Karma

araitz
Splunk Employee
Splunk Employee

Great question! We have not implemented full stored procedure support yet for all our functions (dbinput|dboutput|dblookup). I'll add this question to the stories related to stored procedure implementation. One of the challenges is that most implementations of stored procedures are vendor-specific. What specific database are you interested in support for?

agarrison
Path Finder

Still no answer?

0 Karma

mklabs
New Member

Has support for stored procedures been added?

0 Karma

digitalX
Explorer

Is there a fixe ore Workaround or a list with compatible databases?
I'm Interested in dboutput to run a stored prcedure and have to know that it works.
Is there any additional Post to this Topic?

0 Karma

graa1005
Explorer

also interested to execute Stored procedure in SQL 2008 R2 and newer versions of SQL Server.

0 Karma

otaci
Explorer

also interested in stored procedures for SQL 2012 and/or views

0 Karma

kfinke
New Member

Also interested! MS SQL Server 2008 and up. I was specifically looking for it as a DB Lookup. I can't even get it to work properly in Step 2.

0 Karma

chris24747
Explorer

MS SQL 2012

0 Karma

laurie_gellatly
Communicator

MS SQL Server 2008 R2.

0 Karma

varunnair26
Explorer

Hi,

I am also having the same query. Let me know if we have any solution for this.

Thanks
Varun

0 Karma

jcoates_splunk
Splunk Employee
Splunk Employee

Hi, can you file a support ticket so we can see what's happening?

0 Karma

laurie_gellatly
Communicator

Support ticket has been raised.

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...