Using trigger to export xml file

[lytung]

Hi all,

i am having trouble trying to export this in xml. Basically inside an
update trigger i need to export in xml or file the qty that has been
changed. This then gets updated to another system. Is this a good idea
? should i be using another table in between in case something happens
on the other end?

However, I tried something like this and it didn't recognize the
selected table inside the bcp:

CREATE TRIGGER [TRIGGER NAME] ON [fastpic].[FP_INVTRANS]
FOR INSERT, UPDATE
AS

DECLARE @selected varchar (100)

IF NOT UPDATE(system_qty)

BEGIN

RETURN

END

DECLARE @cmd varchar(200)
set @cmd = 'bcp "select system_qty, part_name from selected " queryout
c:\parts.xml -S. -Usa -P -c -r -t'
exec master ..xp_cmdshell @cmd '

IF someone could help or suggest something else i would appreciate it,
than you

[Erland Sommarskog]

(ly****@gmail.com) writes:

i am having trouble trying to export this in xml. Basically inside an
update trigger i need to export in xml or file the qty that has been
changed. This then gets updated to another system. Is this a good idea
? should i be using another table in between in case something happens
on the other end?
Definitely. Triggers runs in the context of a transaction, and
forking out to xp_cmdshell in a trigger is definitely not good
practice.

I don't know what sort of users that update this table, but they
need hefty privileges to xp_cmdshell. That alone is enough to
repclude this solution. Besides, I don't think you can get BCP to
export in XML.

Write a notification of the changed row to a table, and the write
a job that reads this table periodically. This should not be a
T-SQL job, but a command-line job or an Active-X task. The command
job would be a console program in VB, C# or C++ that is able to read
the XML.
However, I tried something like this and it didn't recognize the
selected table inside the bcp:

Since it's a new login, you need to specify the database name in
the query. But, as I said, you should not do this anyway.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx