All Apps and Add-ons

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

sh254087
Communicator

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
Explorer

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
Champion

Can you share SP query?

0 Karma

sh254087
Communicator
    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
0 Karma

p_gurav
Champion

Can you try with one select query?

0 Karma

sh254087
Communicator

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

0 Karma
Get Updates on the Splunk Community!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...