Another way to run reports on a SpiceWorks SQLite database using a linked server in SSMS?

I created a linked server in SSMS so that I could run reports on a SpiceWorks SQLite database. there are a few quirks in the whole system.

These simple queries are becoming some of the most expensive queries on my reporting SQL Server, I kind of know why, is there a way that I can do this differently?

The Code

I have two queries.

-
TimeToCloseByAssignee

SELECT 
    id AS ticketNum
    , Summary
    , Assignee
    , closed_at
    , created_at
    , ROUND((DATEDIFF(Hour, created_at, closed_at))/24. , 2) AS DaysOpen
FROM OPENQUERY(SPICEWORKS,'
    SELECT tickets.id 
        , users.email as Assignee
        , substr(tickets . summary, 1,100) AS Summary
        , tickets.closed_at
        , tickets.created_at
    FROM tickets
        INNER JOIN users ON tickets.assigned_to = users.id
WHERE status=''closed''
        AND 
        master_ticket_id IS NULL;')

-
GetCreatorByTicketID

SELECT * FROM OPENQUERY(SPICEWORKS, '
    SELECT users.email
        , tickets.id
    FROM tickets
        INNER JOIN users ON tickets.created_by = users.id')
WHERE id = @TicketID

I am using SSRS to handle the nice presentation of the data.

the problem with this, is that I can't give the underlying SQLite query boundaries from SSRS Parameters, it just doesn't like it. There are some questions on StackOverflow with answers and ways to do it, but this was the only way that I could make it work the way I wanted it to. perhaps I wasn't interpreting the answers correctly. either way I don't think it would have been clean and straightforward anyway.

Any thoughts?

Ouch.... you are not filtering the data you are selecting from the OPENQUERY source....

But, let's get some things straight first.... you say:


either way I don't think it would have been clean and straightforward anyway

Using OPENQUERY automatically excludes any pretext of 'clean and straightforward'. They are incompatible.

So, as soon as you use OPENQUERY you can assume that you have already got ugly code (with a fat ass).

The trick is to use makeup - to cover up the blemishes.

In this case, you can filter the records, but you need to be creative.... you have:

SELECT * FROM OPENQUERY(SPICEWORKS, '
    SELECT users.email
        , tickets.id
    FROM tickets
        INNER JOIN users ON tickets.created_by = users.id')
WHERE id = @TicketID

I would suggest the following:

declare @ticketsql as nvarchar(1024)
;

set @ticketsql = '
    SELECT users.email
        , tickets.id
    FROM tickets
        INNER JOIN users ON tickets.created_by = users.id
    WHERE tickets.id = ' + @TicketID
;

select * FROM OPENQUERY(SPICEWORKS, @ticketsql)

StackExchange Code Review Q#44767, answer score: 6