I use Splunk DB Connect 3.1.2. I am trying to execute a stored procedure written on SQL Server using the latest syntax as mentioned in the SplunkDocs-
| dbxquery procedure="{call }" connection="Connection_Name"
I can see in the database and confirm that the stored procedure has been executed and the table is updated with appropriate data. I'm only facing issue with the last SELECT command in the SP which is intended to fetch and display the data from the table which the SP just updated. I get a message saying "No results found."
Can someone please help fix this such that the last SELECT command is executed and the appropriate table data be displayed? Any help is much appreciated.
Use SET NOCOUNT ON in your stored procedure. It won't hurt.
My theory - If I run the stored procedure shown below, Splunk will see this result
(1 row affected)
(1 row affected)
(1 row affected)
(1 row affected)
1
(1 row affected)
(1 row affected)
If you put SET NOCOUNT ON after your Begin statement, splunk will see this result
1
CREATE PROCEDURE bob123
AS
BEGIN
SELECT 1 AS bob INTO #temp
SELECT bob INTO #temp2 FROM #Temp
SELECT * FROM #Temp2
END
GO
Can you share SP query?
USE XYZ
GO
CREATE PROCEDURE sp_Final_Values
AS
--check and drop only an existing table
IF OBJECT_ID('tempdb.dbo.#Temp_Group_Table', 'U') IS NOT NULL
DROP TABLE #Temp_Group_Table;
IF OBJECT_ID('tempdb.dbo.#TEMP_GC', 'U') IS NOT NULL
DROP TABLE #TEMP_GC;
IF OBJECT_ID('tempdb.dbo.#TEMP_EN', 'U') IS NOT NULL
DROP TABLE #TEMP_EN;
IF OBJECT_ID('tempdb.dbo.#Final_BC', 'U') IS NOT NULL
DROP TABLE #Final_BC;
IF OBJECT_ID('tempdb.dbo.#Final_EN', 'U') IS NOT NULL
DROP TABLE #Final_EN;
IF OBJECT_ID('dbo.Final_BC_EN', 'U') IS NOT NULL
DROP TABLE dbo.Final_BC_EN;
Select g.Group, s.Application, s.Server, s.BC, s.EN into #Temp_Group_Table from dbo.Grouping_Sample g INNER JOIN dbo.Test_Data s on g.Server = s.Server
SELECT DISTINCT Group, BC INTO #TEMP_GC from #Temp_Group_Table where Server in( select Server from #Temp_Group_Table where Group in ( select Group from #Temp_Group_Table))
select Group, BC into #Final_BC from (select Group, BC, ROW_NUMBER() over (partition by Group order by [level] desc) [rn] from #TEMP_GC [t] join
--here we assign numeric values to severities to make it comparable
(values ('L', 1),('M', 2),('C', 3)) as BC([name], [level]) on [t].BC = [BC].name) a where rn = 1
SELECT DISTINCT Group, EN INTO #TEMP_EN from #Temp_Group_Table where Server in( select Server from #Temp_Group_Table where Group in (select Group from #Temp_Group_Table))
select Group, EN into #Final_EN from (select Group, EN, ROW_NUMBER() over (partition by Group order by [level] desc) [rn] from #TEMP_EN [t] join
--here we assign numeric values to severities to make it comparable
(values ('D', 1),('T', 2),('Q', 3),('NP',4),('P',5)) as EN([name], [level]) on [t].EN = [EN].name ) a where rn = 1
select c.Group, c.BC as [Final_BC], e.EN as [Final_EN] into Final_BC_EN from #Final_BC c, #Final_EN e where c.Group = e.Group
select * from dbo.Final_BC_EN
GO
Can you try with one select query?
@p_gurav In the above SP, every select query is being executed, except the last select query. So having multiple select queries shouldn't be an issue. However, I tried - 1. Keeping the last query separately in an SP and calling that from dbxquery procedure="" argument, after executing the above SP - it worked. 2. Running the last select query by passing it to the dbxquery query="" argument - this worked as well.
The issue is only when that select query is inside the above mentioned SP.