December 14, 2008 at 10:10 pm
I have creatd below trigger on table
CREATE TRIGGER TR_DEXPORTXML
ON DPRODUCTS AFTER INSERT
AS
select DISTINCT rtrim(ltrim(schedule.SID)) AS SID INTO T_SCHEDULE
from SCHEDULE SCHEDULE INNER JOIN Delivery Delivery ON (Delivery.DFSID = SCHEDULE.SID)
INNER JOIN dProducts DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID )
WHERE DELIVERYITEM.DPID IN (SELECT DPID FROM INSERTED)
select DISTINCT
rtrim(ltrim(Delivery.DID)) AS DID,
rtrim(ltrim(Delivery.DFSID)) AS DFSID ,
rtrim(ltrim(Delivery.DCustomerId)) AS DeliverId
INTO T_Delivery from
SCHEDULE SCHEDULE INNER JOIN Delivery Delivery ON (Delivery.DFSID = SCHEDULE.SID)
INNER JOIN dProducts DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID )
WHERE DELIVERYITEM.DPID IN (SELECT DPID FROM INSERTED)
select DISTINCT rtrim(ltrim(DELIVERYITEM.DPID)) AS DPID,
rtrim(ltrim(DELIVERYITEM.DPFDID)) AS DPFDID,
rtrim(ltrim(DELIVERYITEM.DPQty)) AS DPQty
INTO T_DeliveryITEM from
SCHEDULE SCHEDULE INNER JOIN Delivery Delivery ON (Delivery.DFSID = SCHEDULE.SID)
INNER JOIN dProducts DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID )
WHERE DELIVERYITEM.DPID IN (SELECT DPID FROM INSERTED)
exec xp_cmdshell 'BCP "select SCHEDULE.SID ,Delivery.DeliverId,DELIVERYITEM.DPID ,DELIVERYITEM.DPQty FROM dg.dbo.T_SCHEDULE SCHEDULE INNER JOIN dg.dbo.T_Delivery Delivery ON(Delivery.DFSID = SCHEDULE.SID) INNER JOIN dg.dbo.T_DeliveryITEM DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID ) FOR XML AUTO,ELEMENTS " QUERYOUT "D:\TEST.xml" -c -t -S"DSK07" -U"sa" -P"sa"'
DROP TABLE T_SCHEDULE
DROP TABLE T_Delivery
DROP TABLE T_DeliveryITEM
when i insert data in table , trigger will fire and hang..table will lock ..
and no XML file generated..
But when i run alone code without trigger like below
select DISTINCT rtrim(ltrim(schedule.SID)) AS SID INTO T_SCHEDULE
from SCHEDULE SCHEDULE INNER JOIN Delivery Delivery ON (Delivery.DFSID = SCHEDULE.SID)
INNER JOIN dProducts DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID )
select DISTINCT
rtrim(ltrim(Delivery.DID)) AS DID,
rtrim(ltrim(Delivery.DFSID)) AS DFSID ,
rtrim(ltrim(Delivery.DCustomerId)) AS DeliverId
INTO T_Delivery from
SCHEDULE SCHEDULE INNER JOIN Delivery Delivery ON (Delivery.DFSID = SCHEDULE.SID)
INNER JOIN dProducts DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID )
select DISTINCT rtrim(ltrim(DELIVERYITEM.DPID)) AS DPID,
rtrim(ltrim(DELIVERYITEM.DPFDID)) AS DPFDID,
rtrim(ltrim(DELIVERYITEM.DPQty)) AS DPQty
INTO T_DeliveryITEM from
SCHEDULE SCHEDULE INNER JOIN Delivery Delivery ON (Delivery.DFSID = SCHEDULE.SID)
INNER JOIN dProducts DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID )
exec xp_cmdshell 'BCP "select SCHEDULE.SID ,Delivery.DeliverId,DELIVERYITEM.DPID ,DELIVERYITEM.DPQty FROM dg.dbo.T_SCHEDULE SCHEDULE INNER JOIN dg.dbo.T_Delivery Delivery ON(Delivery.DFSID = SCHEDULE.SID) INNER JOIN dg.dbo.T_DeliveryITEM DELIVERYITEM ON (DELIVERYITEM.DPFDID = Delivery.DID ) FOR XML AUTO,ELEMENTS " QUERYOUT "D:\TEST.xml" -c -t -S"DSK07" -U"sa" -P"sa"'
DROP TABLE T_SCHEDULE
DROP TABLE T_Delivery
DROP TABLE T_DeliveryITEM
This is working fine..
Can u resove this problem
..
My requirement is when the data insert in table XML file should be generate..
December 15, 2008 at 8:04 am
What process is doing the insert? If you control the code doing the insert I would recommend creating the XML document within the application code, not in SQL Server.
It would not suprise me if the issue is that the user in the insert context does not have rights to execute xp_cmdshell. from BOL:
xp_cmdshell Proxy Account
When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.
I would not do this in a trigger. Doing anything that requires resources outside the database within a trigger is usually a recipe for disaster. I would recommend looking in to Service Broker or creating your own queue tables and a schedule djob or windows service that reads the queue table(s) and creates the XML document.
Please check out this article[/url] on triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply