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?

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

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

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 sphelp 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

Explorer

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

0 Karma

Communicator

I don't recall ever receiving a Splunk fix.

0 Karma

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

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

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

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?

Path Finder

Still no answer?

0 Karma

New Member

Has support for stored procedures been added?

0 Karma

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

Explorer

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

0 Karma

Explorer

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

0 Karma

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

Explorer

MS SQL 2012

0 Karma

Communicator

MS SQL Server 2008 R2.

0 Karma

Explorer

Hi,

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

Thanks
Varun

0 Karma

Splunk Employee
Splunk Employee

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

0 Karma

Communicator

Support ticket has been raised.

0 Karma