June 19, 2018 at 9:37 am
We are working on developing a process that will prepopulate a document with data from one of our databases when certain conditions are met. This document will act as an "update sheet" where the end-users will have the ability to change data (on paper for now).
We are trying to export the data into an XML file where it will then be read by a service we created. The service will handle the prepopulating/printing of the document. Right now, the process we came up with is creating locks on the initial query our trigger is firing from. We need a way for the queries to run asynchronously. I have outlined the process we have below.
Outline of process as of now:
1. AFTER UPDATE trigger in place on table A that will run a stored proc if the value of a specific column is changed.
2. Stored proc references an identifier passed to it from the trigger and compares data in table B to see if the end-user data can be updated. If an update is available, we are pulling data from table C, writing a record to a logging table, and using bcp to output the result set to an XML file. We are experiencing locks on the SELECT statement that is creating the XML. Using a global variable for the XML creation.
Has anyone had luck performing XML output using SSB?
We are thinking SSB would be the best route for us to pursue since having a SQL Agent Job running against a logging table every few seconds would create unnecessary overhead. The SQL Agent job would have to check for new records, perform the SELECT from table C, and create the XML file in our situation. We have read that SSB can be tricky to configure properly.
Any feedback or suggestions would be greatly appreciated.
June 19, 2018 at 11:05 am
When you say you are getting locks, which object is being locked, and which SPID is holding that lock? We'd need to see the query and perhaps the output of sp_who2 to make progress on fixing that issue, plus we'll need the query text to see if the query itself could be the problem. If you can get your hands on an actual execution plan, that would also help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply