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