Trouble to Create New Input in DB Connect for stored procedure using temp table. MS SQL Server

[rick3piece]

I'm working on a project to set Sql Server DB monitoring in Splunk. I'm creating custom store procedures and then fetch data into splunk. while most of them are working fine, I'm running into "invalid object" issue if the store procedure is using temp tables. 

Store procedure script:

USE DBName
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE schemaname.[serverconfig]
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #CPUValues (
[index] SMALLINT
,[description] VARCHAR(128)
,[server_cores] SMALLINT
,[value] VARCHAR(5)
)

CREATE TABLE #MemoryValues (
[index] SMALLINT
,[description] VARCHAR(128)
,[server_memory] DECIMAL(10, 2)
,[value] VARCHAR(64)
)

INSERT INTO #CPUValues
EXEC xp_msver 'ProcessorCount'

INSERT INTO #MemoryValues
EXEC xp_msver 'PhysicalMemory'

SELECT
convert(VARCHAR(50), cast(getutcdate() AS DATETIMEOFFSET(3)), 127) ExecutionTime
,cast(SERVERPROPERTY('SERVERNAME') as varchar(100)) AS 'instance'
,cast(v.sql_version as varchar(100)) as sql_version
,cast((
SELECT SUBSTRING(CONVERT(VARCHAR(255), SERVERPROPERTY('EDITION')), 0, CHARINDEX('Edition', CONVERT(VARCHAR(255), SERVERPROPERTY('EDITION')))) + 'Edition'
) as varchar(max))AS sql_edition
,cast( SERVERPROPERTY('ProductLevel') as varchar(max)) AS 'service_pack_level'
,cast( SERVERPROPERTY('ProductVersion') as varchar(max)) AS 'build_number'
,cast((
SELECT DISTINCT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
) as varchar(max)) AS [port]
,
cast((
SELECT cast([value] AS INT)
FROM sys.configurations
WHERE name LIKE '%min server memory%'
) as varchar(max)) AS min_server_memory
,
cast((
SELECT cast([value] AS INT)
FROM sys.configurations
WHERE name LIKE '%max server memory%'
) as varchar(max)) AS max_server_memory
,cast((
SELECT ROUND(CONVERT(DECIMAL(10, 2), server_memory / 1024.0), 1)
FROM #MemoryValues
) as varchar(max)) AS server_memory
,cast(server_cores as varchar(max)) as server_cores
,cast((
SELECT COUNT(*) AS 'sql_cores'
FROM sys.dm_os_schedulers
WHERE STATUS = 'VISIBLE ONLINE'
) as varchar(max)) AS sql_cores
,cast((
SELECT cast([value] AS INT)
FROM sys.configurations
WHERE name LIKE '%degree of parallelism%'
) as varchar(max)) AS max_dop
,cast((
SELECT cast([value] AS INT)
FROM sys.configurations
WHERE name LIKE '%cost threshold for parallelism%'
) as varchar(max)) AS cost_threshold_for_parallelism

FROM #CPUValues
LEFT JOIN (
SELECT CASE
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '8%'
THEN 'SQL Server 2000'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '9%'
THEN 'SQL Server 2005'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '10.0%'
THEN 'SQL Server 2008'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '10.5%'
THEN 'SQL Server 2008 R2'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '11%'
THEN 'SQL Server 2012'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '12%'
THEN 'SQL Server 2014'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '13%'
THEN 'SQL Server 2016'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '14%'
THEN 'SQL Server 2017'
WHEN CONVERT(VARCHAR(128), SERVERPROPERTY('PRODUCTVERSION')) LIKE '15%'
THEN 'SQL Server 2019'
ELSE 'UNKNOWN'
END AS sql_version
) AS v ON 1 = 1

DROP TABLE #CPUValues

DROP TABLE #MemoryValues

SET NOCOUNT OFF
END

GO

Here's the error message I'm getting:

2020-06-22 12:15:11.101 -0400 [dw-53 - POST /api/inputs] ERROR io.dropwizard.jersey.errors.LoggingExceptionMapper - Error handling a request: ad4b202ff32bca30 java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: A processing error "Invalid object name '#CPUValues'." occurred. at com.splunk.dbx.server.util.ResultSetMetaDataUtil.isTableHavingSameNameColumns(ResultSetMetaDataUtil.java:145) at com.splunk.dbx.server.api.service.conf.impl.InputServiceImpl.create(InputServiceImpl.java:139) at com.splunk.dbx.server.api.service.conf.impl.InputServiceImpl.create(InputServiceImpl.java:38) at com.splunk.dbx.server.api.resource.InputResource.createInput(InputResource.java:96) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at

...

I can execute the sp in SQL Editor successfully but when I move on to next step it will fail due to invalid object.

Things I've tried so far:

1. I've tested to replace the temp table to acutal table,

2. define dbname, schemaname and tablename

3. use double quote around all db,schename and table names,

4. Use brakets around db, schema, and table names

5. move all tables to default dbo schema, it was in a different schema

6. tried with 3 different versions of Java drivers, default 4.2, 7.4.1 and 8.2.2

7. Tried with same but simplified sp to contain just one temp table and no duplicate column names in the store procedure.

All above have failed for the same error, any help or suggestion is highly appreciated.

Thank you.

[Esky73]

If the query is successful in SQL explorer - have you tried manually adding the query to the db_connect inputs.conf ?

OK from the docs ..

Note: Inputs only support SQL queries such as "select...from...where", not DDL such as "create table..."

No DDL cmds

[aashiqwork]

it looks like the Splunk DB connect doesn't support temp tables, please have your entire query into an SQL view and then call the view into your Splunk App.

Hope this helps.