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.
... View more