查找触发查询的源 [英] Find the source that fired a query

问题描述

这是一个假设性问题 - 下面列出的问题完全是虚构的，但我相信如果有人有答案，它可以证明对未来的参考很有用.

我们遇到的情况是，多个系统都在我们的 SQL Server 上填充同一个数据表.这些系统中的一个似乎错误地填充了表格，尽管以一致的模式(让我相信这只是单个系统中的错误，而不是多个)这些主要是第三方系统，我们没有访问修改或查看他们的源代码，也不改变他们的功能.我们想向罪魁祸首系统的开发人员提交一份错误报告，但我们不知道是哪一个，因为系统没有在系统上留下可识别的痕迹表 - 在我之前的负责人，当数据库是新的并且只是偶尔被单个系统使用时，认为单个时间戳字段是足够的审计，并且从未重新考虑过这一点.

We have a situation wherein multiple systems all populate the same data table on our SQL Server. One of these systems seems to be populating the table incorrectly, albeit in a consistent pattern (leading me to believe it is only a bug in a single system, not multiple) These are majoritively third-party systems and we do not have access to modify or view their source code, nor alter their functionality. We want to file a bug report with the culprit system's developer, but we don't know which one it is as the systems leave no identifiable trace on the table - those in charge before me, when the database was new and only occasionally used by a single system, believed that a single timestamp field was an adequate audit, and this has never been reconsidered.

我们的解决方案必须完全基于 SQL.我们的想法是在桌子上写一个触发器，并以某种方式拉取查询的来源 - 即它来自哪里 - 但我们不知道如何，或者即使这是可能的.

Our solution has to be entirely SQL-based. Our thought was to write a trigger on the table, and somehow pull through the source of the query - ie, where it came from - but we don't know how, or even if that's possible.

对此有一些明确的解决方案 - 例如，联系所有开发人员更新他们的软件以填充新的 software_ID 字段，然后稍后使用新信息识别故障系统(并在以后避免我虚构的自我类似的头痛) - 但我特别想知道是否有任何事情可以在 SQL Server(或其他聪明的解决方案)上完全在内部完成并注明限制.>

There are some clear solutions to this - for eg contact all the developers to update their software to populate a new software_ID field, and then use the new information to identify the faulty system later (and save my fictional self similar headaches later) - but I'm particularly interested to know if there's anything that could be done purely in-house on SQL Server (or another clever solution) with the restrictions noted.

推荐答案

你可以使用函数:

select HOST_NAME(), APP_NAME()

因此您将了解导致更改的计算机和应用程序..

So you will know the computer and application that caused the changes..

并且您可以修改应用程序连接字符串以添加自定义应用程序名称，例如:

And you can modify application connection string to add custom Application name, for example:

„Data Source=SQLServerExpress;Initial Catalog=TestDB;
Integrated Security=True; Application Name=MyProgramm"

这篇关于查找触发查询的源的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！