Splunk DB Connect 3.1.2 | Stored Procedure | OUTPUT | SP executes but does not return/display the resultant table data

[sh254087]

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.

[ehughes100]

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)

bob

1
(1 row affected)
(1 row affected)

If you put SET NOCOUNT ON after your Begin statement, splunk will see this result

bob

1

CREATE PROCEDURE bob123

AS
BEGIN
SELECT 1 AS bob INTO #temp
SELECT bob INTO #temp2 FROM #Temp
SELECT * FROM #Temp2
END
GO

[p_gurav]

Can you share SP query?

[sh254087]

    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

[p_gurav]

Can you try with one select query?

[sh254087]

@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.