Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rheyman
Starting Member
2 Posts |
 Posted - 2002-04-19 : 12:52:42
|
| Hi all... Wonder if someone can help me out.I'm developing a scheme in which data from a certain table will eventually wind up in an MSMQ queue. The path I'm following is:1 > Write an insert trigger to capture the record via the 'inserted' table2 > Pass the record data to a stored procedure (from the Trigger) using FOR XML AUTO 3 > The SP will instantiate a small COM+ object that wraps some basic MSMQ functionality. The SP will use the sp_OACreate and sp_OAMethod calls. 4 > The COM+ object will push the XML record into a known queue.Here's my issue.....Being a relative ignoramus regarding triggers etc. I am having an issue passing the new record to the stored procedure as XML...My last attempt looked something like this:CREATE TRIGGER InsXML ON tablex AS DECLARE @xml_hold as char(4000) Set @xml_hold = SELECT * FROM inserted FOR XML AUTO EXEC sp_QueueWriter @xml_holdWell.... first of all the Set @xml_hold sytax is obviously incorrect.Can someone help me with this syntax? Can this in fact be accomplished? Is there a better way to do this? Please pardon me if this is so obvious an answer that I should be hanging my head in shame.Thanks in advanceRob |
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-04-19 : 13:22:42
|
| According to SQL BOL, "Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client"See the topic "Guidelines for Using the FOR XML Clause" in BOL or here:[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_0alh.asp[/url]I tried something like this yesterday and ran into similar problems. |
 |
|
|
rheyman
Starting Member
2 Posts |
Posted - 2002-04-19 : 13:38:08
|
| Hmmmmm Thanks for the quick reply.. in SQL BOL the prohibited syntax that I was trying to use was:DECLARE @doc nchar(3000)SET @doc = (SELECT * FROM Customers WHERE CustomerID = 'ALFKI' FOR XML RAW)What a shame! So... the next attempt I guess will be to send the select as a recordset to the COM object and use ADO to convert it to XML inside the object. This seems pretty ugly and I somehow remembering reading something about ADO doing something 'strange' in it's XML formating.Thanks againRob |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-19 : 13:45:05
|
| This might be another option for you:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=9336You can output the XML into a file (in the format you choose) and then read it into an ADO Stream object or an XML DOM object, using the Load method. |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2002-04-19 : 16:45:55
|
| I haven't tried it, and it's a really bad idea anyway, but you might be able to read the results of the FOR XML into an ADO Stream object created in the trigger using the sp_OACreate, and then dumping the stream text into a local SQL variable.Doing this in a trigger is bad bad bad. However, inside a SPROC it might be do-able.-- monkey |
|
|
|
|
|
|
Passing XML from insert trigger to SP