1

I've been dealing with a vendor's unconfigurable application which is, unfortunately, the most feature-complete in its niche. I'm looking to augment its capabilities with on-top screen display. Its data access is extremely predictable allows me to infer reliably from query patterns what I need to know to provide a user with additional relevant information pertaining to their current view within the application. Specifically, it always executes TSQL statement (which is the easily identifiable part) with a named parameter (which is the part that defines what the user is viewing) that I'd very much like to get at.

So, in short, I'm looking to create a mechanism that: - Hook the query pattern (trivially expressible via wildcards) - Extract the named parameter value - Sends a message to the (remote) overlay application with the parameter value

Does SQL server have any facilities for any portion of this undertaking? Is there a better way I should be going about this? (Possibly lower down in the network stack.)

Edit: We're using SQL Server 2005, but upgrading to SQL Server 2008/2008R2 isn't out of the question.

Improve this question
10
  • Won't you need to get the user too? I'm assuming multiple people can be connected to the database at once. If person A runs Query 123, how do you keep it from updating the dashboard or whatever on everyone else's display?
    – JNK
    Commented Jan 18, 2012 at 20:47
  • I have other ways of dealing with this. While capturing an IP or something sufficiently "unique" for my particular circumstance would be ideal, it's also sufficient for me to only allow one client to view a subset of data at a time.
    – Kaganar
    Commented Jan 18, 2012 at 21:22
  • 2
    If you still have this issue, take a look at Useful Management Objects In particular Listing D, which shows the SQL executing for all users on the server.
    – user4742
    Commented Mar 23, 2012 at 15:45
  • @Joshua: That looks pretty handy, however if there a way to trigger that when a request is issued? The queries I'm trying to catch aren't long ongoing queries.
    – Kaganar
    Commented Mar 23, 2012 at 15:58
  • I just found Query Notifications, which have a 2005 version as well. Though they may require you to do something odd such as run the query once for each possible user so you can monitor for updates.
    – user4742
    Commented Mar 23, 2012 at 16:01

1 Answer 1

Reset to default
1

There's a few gotchas in here: you've said that the queries will be short-lived, that you're going to have (relatively) high volume, that you're going to capture parts of the queries, and that you want to communicate back to the app tier. This means you want a non-blocking, high-performance solution, and there's nothing built-in that will make this easy. I'm going to just talk theoretically about something you could build, but it ain't gonna be easy.

Part 1: capturing the query data as it happens. For this, SQL Server's Extended Events are probably the way to go. Think of it as debug points in the SQL Server engine code where you can insert your own code. As you learn about it, you'll find that you can gather all kinds of cool information about queries, but keep in mind that this is blocking code, and the more information you want to gather, the slower your queries go. You won't be able to push data outside of SQL Server in this part - you're going to have to either write the data to an XML file, or put it in the ring buffer. The ring buffer makes the most sense here since you'll need to query it in near-real-time.

Part 2: getting the data from the SQL Server's ring buffer to the client. You could build something to push the data from SQL Server to the overlay tier, but this screams danger to me. It couldn't be single-threaded, because you could hang on trying to send a notification to a client that's running slow, and miss everybody else's notifications. Instead, what I think you want to do is have the overlay app detect on the client side when it needs to check the ring buffer, and then do that via a query. You don't want all the clients continuously polling the ring buffer - performance nightmare there.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.