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?
I have two queries.
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;')
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.