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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-19 : 07:47:13
|
| John writes "I have a stumper for you. I need to get a Select * from Inserted to be written to a disk file from a trigger.I can't get Select * From Inserted For XML AUTO, ELEMENTS to work. I have written extended SPs to write a variable out to a file or to an MSMQ so I have half of the equation done. Just need to be able to get XML out of a trigger into a variable to pass to the xSPs or directly to a file. Thanks!Using sql server 2000 sp3Windows 2000 sp 2" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-19 : 07:49:26
|
| You do realize that any operations in a trigger increase the lock time for the target object by the length of time it takes to perform the trigger ....If you've got a transactional system, I'd suggest you look for another way to generate your xml....Jay White{0} |
 |
|
|
SqlStar
Posting Yak Master
121 Posts |
Posted - 2003-03-19 : 08:53:34
|
| Hi,If u r using "FOR XML" clause, u does not store that XML string output to any variable like "text,varchar" datatype, and u does not store this o/p to any other table's column (even Temporary table) also.":-) IT Knowledge is power :-)" |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-03-19 : 09:33:13
|
| SqlStar, I don't understand your suggestion.Jay White{0} |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2003-03-19 : 10:30:12
|
Me neither. But i think SQLStar is saying that FOR XML OUTPUT is only for consumption by a client, and cannot be used within a SPROC, trigger, or batch.According to the "Guidelines for Using the FOR XML Clause" topic in BOL:Generally, FOR XML cannot be used for any selections that do not produce direct output to the Microsoft® SQL Server™ 2000 client. -- monkey |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2003-03-19 : 12:32:12
|
| Actually, I don't agree with that. What we do is use a trigger that calls an sp and generate an xml file for our campaigns table whenever there is an insert/update/delete on it. This code creates the xml document and dumps the xml file over to our web server for us.(didn't want to use virtual directories).CREATE procedure p_get_campaigns_XMLasset nocount onexec master..xp_cmdshell 'bcp "select distinct p.program_name,p.program_id from procare.dbo.programs p with(nolock) inner join procare.dbo.campaigns c with(nolock) on c.program_id=p.program_id where c.active=1 for xml auto,elements" queryout d:\xmldocs\ProcessQueueCampaignsRaw.xml -T -c -r -t'exec master..xp_cmdshell 'copy/b d:\xmldocs\rootopen.txt + d:\xmldocs\ProcessQueueCampaignsRaw.xml + d:\xmldocs\rootclose.txt \\nxclmch1\d$\inetpub\devroot\includes\ProcessQueueCampaigns.xml'set nocount offGOMike"oh, that monkey is going to pay" |
|
|
|
monkeybite
Posting Yak Master
152 Posts |
Posted - 2003-03-19 : 12:39:59
|
quote:
What we do is use a trigger that calls an sp and generate an xml file for our campaigns table whenever there is an insert/update/delete on it.
Mike, I think your solution is more in line with what John is looking for.My response was kinda interpreting SqlStar's cryptic reply that FOR XML output cannot be captured in a local variable or inserted into a table within a trigger or SPROC, which is a true statement, unless the output is sent to a file or program outside of SQL Server, or doing something wild and crazy with it like using sp_OACreate.-- monkey |
|
|
|
|
|
|
|
|
xml from sql server trigger inserted table