|
|
Posted by Erland Sommarskog on 05/05/06 00:32
(lytung@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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|