Why do I get the following error "There was an error processing your request. It has been logged" in a Data Lab Input for Splunk DB Connect?

[sochsenbein]

Hello,

I am getting the error "there was an error processing your request. It has been logged" when I try to save the following query in a Data Lab Input for DB Connect:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108) AS Date,
    CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20)) AS Measured_Date,
    LL.prod_id
INTO #TempData
FROM    eng..lost_loads LL INNER JOIN met..range_radius RR ON RR.prod_id = LL.prod_id
WHERE LL.run_datetime BETWEEN DATEADD(mi, -15, GETDATE()) AND GETDATE()
order by LL.run_datetime    

SELECT 
  Date, 
  CONVERT(smalldatetime, Measured_Date) AS Measured_Date
  ,COUNT(1) AS  TempData
FROM #TempData
GROUP BY 
  Date, CONVERT(smalldatetime, Measured_Date)
ORDER BY Measured_Date DESC

DROP TABLE #TempData

I entered the query manually in the db_inputs.conf as per this thread: https://answers.splunk.com/answers/607692/splunk-dbconnect-311-build-34-there-was-an-error-p.html
However, I am still not seeing it when I refresh the Data Inputs page, I am assuming the server may need rebooted, but this is not an option at this time. Any suggestions?

Thanks

[sochsenbein · Accepted solution]

So instead of a UNION (since this was placing data into a temp table and then working off that data), I just rewrote the second SELECT statement. It's ugly, but this works perfect:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT
    CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108) AS Date
    ,CONVERT(smalldatetime, CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))) AS Measured_Date
    ,COUNT(LL.prod_id) AS TempData
FROM    eng..lost_loads LL inner join met..range_radius RR ON RR.prod_id = LL.prod_id
WHERE LL.run_datetime between dateadd(mi, -15, getdate()) and getdate()
GROUP BY 
    CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,108)
    ,CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))
ORDER BY CAST(CONVERT(CHAR(10),LL.run_datetime,101)+' '+CONVERT(CHAR(8),LL.run_datetime,114) AS VARCHAR(20))

Like I said, it's ugly. But it works lol.

[akocak]

As far as I know, DB connect doesn't support SP (Stored Procedure) style queries. Once upon a time, I remember using

UNION

SQL command to have my query work.
if you must drop table after transaction, perhaps a second scheduled search that runs after 1st one can do it with:

|dbxquery "DROP TABLE <TABLE_NAME>" 

[sochsenbein]

A defined temp table will not work when doing a CREATE TABLE, however, I took a query and instead of the CREATE TABLE, I did SELECT INTO table_name and it worked just fine in Splunk. Really odd. I did not think about using the UNION, I will look in to that. I appreciate your comment!

[akocak]

If you can merge your queries into one SELECT, I think it is definitely better than UNION.
UNION is a shortcut to me if I have the query from some DB admin and not have a time to make it better 🙂