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]

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:{)

[bancaitb_eserci]

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' 

[joao_amorim]

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'.",). "

[amanediel]

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

[laurie_gellatly]

I don't recall ever receiving a Splunk fix.

[davidpaper]

Hi,

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

[chris24747]

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.

[joao_amorim]

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

[araitz]

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]

Still no answer?

[mklabs]

Has support for stored procedures been added?

[digitalX]

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?

[graa1005]

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

[otaci]

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

[kfinke]

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.

[chris24747]

MS SQL 2012

[laurie_gellatly]

MS SQL Server 2008 R2.

[varunnair26]

Hi,

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

Thanks
Varun

[jcoates_splunk]

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

[laurie_gellatly]

Support ticket has been raised.