使用SSMS中的链接服务器在SpiceWorks SQLite数据库上运行报表的另一种方法？

Question

我在SSMS中创建了一个链接服务器，以便在SpiceWorks SQLite数据库上运行报表。整个系统中有几个怪癖。

这些简单的查询正在成为我的reporting上最昂贵的查询之一，我知道为什么，有什么方法可以以不同的方式进行吗？

代码

我有两个问题。

1. TimeToCloseByAssignee选择id作为ticketNum，汇总，受让人，closed_at，created_at，圆形((DATEDIFF(Hour，created_at，closed_at))/24。，2)作为DaysOpen从OPENQUERY(SPICEWORKS，‘选择tickets.id，users.email作为受让人，substr(门票)。摘要，1,100)作为摘要，tickets.closed_at，tickets.created_at从票证内部加入用户在tickets.assigned_to = users.id，其中的状态=‘关闭’和master_ticket_id为空；)
2. GetCreatorByTicketID SELECT * FROM (SPICEWORKS，‘Selectusers.email，从票证内部加入用户tickets.created_by = users.id')，其中id = @TicketID

我正在使用SSRS来处理良好的数据表示。

问题在于，我不能从SSRS参数中给出底层的SQLite查询边界，它只是不喜欢它。在StackOverflow上有一些问题有答案和方法，但这是我能让它按照我想要的方式工作的唯一方法。也许我没有正确解释答案。不管是哪种方式，我都不认为它是干净而直接的。

有什么想法吗？

Answer 1

我能够删除第二个查询，并将其与第一个查询合并，我不知道为什么一开始不尝试这样做，可能是因为我想轻轻踩在这座摇摇欲坠的桥上。我仍然没有将变量传递给OPENQUERY的可靠方法，但当我有更多时间处理这个项目时，这可能是StackOverflow的一个问题。

代码：

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

我在这里所做的是将另一个查询转换为一个单独的连接到users表，但在tickets表中的另一个字段上加入了别名表。这样，我就可以从这个单一的查询中获得创建者和指定的用户(Assignee)。

摆脱第二个查询将对SQLite数据库的查询量减少到一个，而另一个方法是执行第二个查询的次数惊人，因为第二个查询将对第一个查询中的每一行运行一次，这是非常低效率的，除非绝对必须这样做。

我应该解释得更多，第二个查询必须是第一个查询的子报告，这样它才能按照我希望的方式工作。我不认为我可以在查询中以这种方式运行表，在输入别名后，我没有对所有列的名称进行完全限定，这就是为什么我在第一次循环时没有这样做的原因，但现在我知道，只要我完全限定我在SQLite数据库上运行的查询中选择的所有列，就可以在SQLite上执行此操作。

Answer 2

哎哟..。您没有过滤从OPENQUERY源中选择的数据.

但是，让我们先把事情搞清楚.你说：

不管怎样，我不认为它是干净的和直接的。

使用OPENQUERY自动排除任何“干净和直截了当”的借口。它们不相容。

因此，一旦您使用OPENQUERY，您就可以假设您已经有了丑陋的代码(有一个胖屁股)。

诀窍是化妆--掩盖瑕疵。

在这种情况下，你可以过滤记录，但你需要有创造性.你有：

从OPENQUERY中选择*(SPICEWORKS，‘选择users.email，tickets.id从tickets.created_by =users.id上的内部连接用户’)，其中id = @TicketID

我建议如下：

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)